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:
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
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
