Dynamic SQL query creation

Reshaw

Board Regular
Joined
Mar 2, 2011
Messages
53
Morning everyone

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 Blue 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.

What I need to do is dynamically create the above SQL statement to include all the Account names retrieved from Act! and insert them into the SQL statement above (blue part)

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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top