Probably basic SQL problem

Morning everyone

First of all, apologies if this is a very simple question. I am not very familiar with SQL, I have cannibalised a few queries generated by MS SQL (created via Excel) and reached an impass for something I am trying to achieve, hence the cry for help here.

I am attempting to create a self updating performance spreadsheet for my sales team.

We use a transport planning system called Manpack and I can use MS Query to interrogate this system and pull back any data I want. My reps sign up new accounts and they are given an account name internally and I can construct an SQL query that looks for all the transactions for those named accounts in the current year.

Currently this SQL statement looks like this:
SELECT ANON01_HAJobFile.JobNumber, ANON01_HAJobFile.Account, ANON01_HAJobFile.Volume, ANON01_HAJobFile.Quantity, ANON01_HAJobFile.Value1, ANON01_HAJobFile.DelDate, ANON01_HAJobFile.JobCancelled
FROM DBA.ANON01_HAJobFile ANON01_HAJobFile
WHERE (ANON01_HAJobFile.JobCancelled='N') AND (ANON01_HAJobFile.Account In ('C01','CAR','COC','DEB','HAR','HOL','INT','KAC','RDM','UKC','RDM','DOB','VER')) AND (ANON01_HAJobFile.DelDate Between {d '2012-01-01'} And {d '2012-12-31'})
ORDER BY ANON01_HAJobFile.DelDate

Referring particularily to the bit:- WHERE Job is not cancelled and WHERE job is one of (list of accounts)

Hold that thought a moment please...




Now, we have another system (Sage Act!) which is a prospecting tool, I can also interrogate that and retrieve any info I need from there (and do). One of the things that I can get from Act! easily is a list of Account names for a given Rep in the form of a table. When a rep signs up a new customer, Act! is updated and the table will show the new accounts at the bottom of it.

The code for this looks like:-
SELECT TBL_CONTACT.CUST_AccountCode_015058247, TBL_CONTACT.CUST_Salesperson_053027311
FROM SalesCombined.dbo.TBL_CONTACT TBL_CONTACT
WHERE (TBL_CONTACT.CUST_AccountCode_015058247 Is Not Null) AND (TBL_CONTACT.CUST_Salesperson_053027311='Bryan Surname')
ORDER BY TBL_CONTACT.CUST_AccountCode_015058247



What I need to do is dynamically create the above SQL statement which retrieves all the customer account codes for that salesman from Act! and insert them into the SQL statement above The In list.

I have attempted to use the [] and ? operators so that excel then asks me where to look for the parameter but it doesn't appear to like me adding a range (as opposed to a single cell) and also I need the range to be the length of the table returned which increases over time.

Currently I manually update the Query to include any new accounts but this is time consuming and if I am not here, the reps are not able to do this themselves.

Is it possible to nest the Act! Query within the Manpack one?

I would appreciate any thoughts on this problem you may have
thanks in advance
Rob

don't know, but you can test it...

Many thanks for your prompt reply

I have entered this in as you have it and get the error message
Table 'TBL_CONTACT' not found.

Any ideas?

well, i got that from you, didn't i

maybe change this --to this --

Yeah you did, sorry. I originally copied the code from the connection properties dialogue within Excel, so both sections of code were working as a standalone queries.

I tried swapping the dbo to the front but get error message:
User ID 'Salescombined' does not exist.

well, i'm sorry, i don't really know what your tables are called

but i'm pretty sure 'Salescombined' isn't a User ID

perhaps ms query doesn't allow subqueries?

That's what I'm starting to think, there's references all over the web that seem to suggest it is very possible with SQL in general, but it seems that the Select function only works when its is the first line of the code and any subsequent select's are seemingly ignored and that it presumes you are referencing tables in the same database, which I'm not.

Thanks for your help all the same
Rob