Texas Longhorn
Active Member
- Joined
- Sep 30, 2003
- Messages
- 493
I have a large log in Excel (WB = 2003 YTD Audit Log.xls; Sheet = All). It is about 35 columns by 5,100 rows. Of the 35 columns, I only want to see data from 6 (Concatenate, Dir, Mgr, Rep, License, and Other). Concatenate is Dir&Mgr&Rep; Dir, Mgr, and Rep are text; and License and Other are in dollars. I want to see this data summarized for each unique string in Concatenate. I've got this working using Advanced Filter, but I'd prefer to use MS Query (so I can autofill formulas in cells adjacent to the query result each time I run it).
My desired result effectively rolls up License and Other revenue by unique value in Concatenate.
Here is the SQL statement that I've butchered:
SELECT DISTINCT `All$`.Concatenate, `All$`.Dir, `All$`.Mgr, `All$`.Rep, Sum(`All$`.`License Amount`) AS 'Sum of License Amount', Sum(`All$`.Other) AS 'Sum of Other'
FROM `G:\Sales\Analyst\Audit\2003 YTD Audit Log`.`All$` `All$`
GROUP BY `All$`.Concatenate, `All$`.Dir, `All$`.Mgr, `All$`.Rep
I'm not sure what it's doing, but it's definitely not yielding the desired results. (Please keep in mind that I am brand new to SQL).
Any help with this statement would help me immensely.
Thanks,
Bill
My desired result effectively rolls up License and Other revenue by unique value in Concatenate.
Here is the SQL statement that I've butchered:
SELECT DISTINCT `All$`.Concatenate, `All$`.Dir, `All$`.Mgr, `All$`.Rep, Sum(`All$`.`License Amount`) AS 'Sum of License Amount', Sum(`All$`.Other) AS 'Sum of Other'
FROM `G:\Sales\Analyst\Audit\2003 YTD Audit Log`.`All$` `All$`
GROUP BY `All$`.Concatenate, `All$`.Dir, `All$`.Mgr, `All$`.Rep
I'm not sure what it's doing, but it's definitely not yielding the desired results. (Please keep in mind that I am brand new to SQL).
Any help with this statement would help me immensely.
Thanks,
Bill
