Although I'm not sure about this, my interpretation of what you asked
for is a little different from Tom Ellison's. The Query that he posted
will do a good job of displaying totals, and that's what I originally
thought you wanted. But looking at your question again caused me to
think that you really wanted to know how many invoices in each price
range a salesman issued (during the time period you specified). So, if
the Query that Tom gave you is what you need, you may ignore the rest of
this message. (It presents a solution based on an alternative
interpretation of your request.)
Suppose we have sales data that look like this:
[Sales]
date Invoice# salesrep# sales margin
dollars
---------- -------- --------- ------------
11/21/2005 021 Jim $105.00
11/21/2005 022 Sue $98.00
10/21/2005 002 Sue $85.00
11/21/2005 023 Jim $205.00
5/18/2005 033 Sue $315.00
I added a new Table to identify the categories into which you wanted to
classify the sales representatives:
[Levels]
Levels_ID Range Floor
--------- -------- --------
1 <50 $0.00
2 50-75 $50.00
3 75-100 $75.00
4 100-150 $100.00
5 150-300 $150.00
6 300+ $300.00
(The second field, [Range], I was originally going to use in the
Crosstab Query, but it didn't do exactly what I wanted, so I don't
actually use it there.)
For these data, Tom Ellison's Query (slightly revised) would produce the
following results in Query Datasheet View:
salesrep# Invoices 50 50-75 75-100 100-150 150-300 300+
--------- -------- -- ----- ------ ------- ------- -------
Jim 2 $105.00 $205.00
Sue 3 $183.00 $315.00
To display these data according to my take on your request, I defined 3
Queries. The first one calculates the category to which an invoice
belongs. (You might want to combine these, for example, using monthly
totals, but I think that's not what you asked for.)
[Q_1_Levels] SQL:
SELECT S.[salesrep#], S.[Invoice#],
S.[sales margin dollars], Max(L.Floor) AS LevelBoundary
FROM Levels AS L, Sales AS S
WHERE (((L.Floor)<
![sales margin dollars]))
GROUP BY S.[salesrep#], S.[Invoice#],
S.[sales margin dollars], S.date
HAVING (((S.date) Between #1/1/2004# And #12/31/2005#));
[Q_1_Levels] Query Datasheet view:
salesrep# Invoice# sales margin LevelBoundary
dollars
--------- -------- ------------ -------------
Jim 021 $105.00 $100.00
Jim 023 $205.00 $150.00
Sue 002 $85.00 $75.00
Sue 022 $98.00 $75.00
Sue 033 $315.00 $300.00
The [LevelBoundary] field identifies the category that we will use.
Only one date range is used; for other ranges, you'd need to change the
Query design.
The next Query counts the number of invoices that each salesman has in
each category (during the specified time period).
[Q_2_SalesrepLevels] SQL:
SELECT Q1.[salesrep#], L.Range,
Count(Q1.[Invoice#]) AS Invoices
FROM Q_1_Levels AS Q1 INNER JOIN Levels AS L
ON Q1.LevelBoundary = L.Floor
GROUP BY Q1.[salesrep#], L.Range, Q1.LevelBoundary
ORDER BY Q1.[salesrep#], Q1.LevelBoundary;
[Q_2_SalesrepLevels] Query Datasheet view:
salesrep# Range Invoices
--------- ------- --------
Jim 100-150 1
Jim 150-300 1
Sue 75-100 2
Sue 300+ 1
The 3rd Query runs the Crosstab, displaying numbers of invoices (no
dollar amounts) in each category.
[Q_3_Salesrep_Crosstab] SQL:
TRANSFORM Max(Q2.Invoices) AS MaxOfInvoices
SELECT Q2.[salesrep#]
FROM Q_2_SalesrepLevels AS Q2
GROUP BY Q2.[salesrep#]
ORDER BY Q2.[salesrep#]
PIVOT Q2.Range In ("<50","50-75","75-100",
"100-150","150-300","300+");
Sadly, I couldn't find a way to specify the column headings ("<50",
etc.) without re-entering them. I dislike maintaining duplicate lists
in a database, as any changes must be made multiple times, but I
couldn't find any other way to specify them. So you'll find the same
list of values in the [Levels].[Range] Table and in the
[Q_3_Salesrep_Crosstab] definition. (In Query Design View, right-click
on the upper panel, choose properties, specify values in Column Headings.)
[Q_3_Salesrep_Crosstab] Query Datasheet view:
salesrep# <50 50-75 75-100 100-150 150-300 300+
--------- --- ----- ------ ------- ------- ----
Jim 1 1
Sue 2 1
This is close to what I (now) think you were asking for. Of course,
these Queries can be modified to add date ranges, totals, etc.
-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
Tom said:
Dear Krish:
Here's what I propose:
SELECT Salesrep, COUNT(*) AS Invoices,
(SELECT SUM([sales margin])
FROM YourTable T1
WHERE T1.Salesrep = T.SalesREP
AND T1.[sales margin] <= 50)
AS [50],
(SELECT SUM([sales margin])
FROM YourTable T1
WHERE T1.Salesrep = T.SalesREP
AND T1.[sales margin] > 50
AND T1.[sales margin] <= 75)
AS [50 - 75],
(SELECT SUM([sales margin])
FROM YourTable T1
WHERE T1.Salesrep = T.SalesREP
AND T1.[sales margin] > 75
AND T1.[sales margin] <= 100)
AS [75 - 100],
(SELECT SUM([sales margin])
FROM YourTable T1
WHERE T1.Salesrep = T.SalesREP
AND T1.[sales margin] > 100
AND T1.[sales margin] <= 150)
AS [100 - 150],
(SELECT SUM([sales margin])
FROM YourTable T1
WHERE T1.Salesrep = T.SalesREP
AND T1.[sales margin] > 150
AND T1.[sales margin] <= 300)
AS [150 - 300],
(SELECT SUM([sales margin])
FROM YourTable T1
WHERE T1.Salesrep = T.SalesREP
AND T1.[sales margin] > 300)
AS [300+],
FROM YourTable T
WHERE [date] BETWEEN #1/1/2004# AND #12/31/2004#
GROUP BY Salesrep
This uses subqueries to categorize the 6 ranges of Sales Margin.
You must replace "YourTable" in the above with the actual name of your table
(or query) at 7 places in the above. Also, please check my spelling of your
column names.
Tom Ellison
I have a table that has fields such as date, Invoice#, salesrep# and sales
margin dollars for every product sold listed in a Invoice. I would like to
populate a crosstab query preferably as follows.
Group by Salesrep as row
Count number of Invoices as value.
Group Sales Margin dollars like >50,50-75,75-100,100-150, 150-300,300 and
above as columns.
Where date range is between ## and ##.
Which query is the best for getting the results?
My goal is to determine the poor performing salesrep.
Thanks for all the help.
artition([Sales Margin],0,999999,50)