I am trying to create a report that does the following:
Access Data in Query:
NAME | DATE | SALE TYPE |
-------------------------
John DOE | 1282003 | TYPE A
Jane DOE | 1282003 | TYPE C
Jane DOE | 1282003 | TYPE D
Jane DOE | 1282003 | TYPE C
John DOE | 1282003 | TYPE E
John DOE | 1282003 | TYPE C
John DOE | 1282003 | TYPE A
What I want the report to look like:
NAME | DATE | TOTAL SALES | % of TYPE C SALES | % of TYPE D SALES
-----------------------------------------------------------------
John DOE | 1282003 | 4 | 25% | 0%
Jane DOE | 1282003 | 3 | 66% | 33%
So basically, I want to show a break down per day of the sales each
salesperson recieved in this format.
My trouble is pulling the counts for each needed calcualtion in one
select statement - as I am trying to have all salespeople on one page
and listed togethor.
The code I am using looks like this:
rst.Open "SELECT agent_Name, count(agent_nam e) AS SALES FROM [PNG PAMP
Report By Agent] WHERE sale_date BETWEEN '"& session("date1" ) &"' AND
'"& session("date2" ) &"' GROUP BY Agent_Name" , "DSN=PNG", 1, 3
It basically counts the total number of sales per agent for the date I
specifiy. This gives me totals to divide by, however, in order for me
to divide by these totals I need to count the sale types to divide
by... how would I do this? Nested Select Statements, count(case
when...)??
Any help appreciated.
Thank you
