Grouping in a comparative query

[bg17067]

I am having problems with a comparative query. I have the following table:
[Tbl_Commission]
[Sales Rep]
[Manufacturer]
[Shipped Amount] (this is totaled)
[Shipped Date] (between start/end dates)

I have created two identical queries that run on different start/end dates
creating the comparative period. Qry1’s total is called [Current] and Qry2’s
total is [Prior]. I can show the figures I want either per manufacturer or
per Sales Rep but not by both.

Here is the SQL for one of the single queries:

SELECT Tbl_Commission.SalesRep, Tbl_Commission.Manufacturer, Sum
(Tbl_Commission.[Shipped Amount]) AS [SumOfShipped Amount], [SumOfShipped
Amount] AS [Current]
FROM Tbl_Commission
WHERE (((Tbl_Commission.ShippedDate) Between [Enter Current Start Date:] And
[Enter Current End Date:]))
GROUP BY Tbl_Commission.SalesRep, Tbl_Commission.Manufacturer;

I want the final query to group by Sales Rep & Manufacturer while showing the
Current and Prior totals from Qry’s 1 & 2. I can add the totals and compute
the math but it’s the grouping that I seem to have a problem with.

I appreciate any and all assistance,

Thanks,
Brian

 

[Tom Ellison]

Dear BG:

I'm going to make a guess. Your table Tbl_Commission has a unique key for
SalesRep / Manufacturer / ShippedDate.

If there are 10 rows in the table for a single SalesRep / Manufacturer, each
for a different ShippedDate, which fall between the dates entered, then you
are going to add together the values in the [Shipped Amount] column and call
that [SumOfShipped Amount]. But the next thing is surprising. You
reference [SumOfShipped Amount] as though it were a column in the table. It
is not by your definition of the problem. It is however an alias you have
selected for the column you just calculated when you summed [Shipped
Amount].

Why would you want another column with the same value in it anyway. Is that
what you intended?

Now, you said this is intended to be a comparative query. I'm thinking you
want to bring together the two queries to make a comparison. I assume one
is "current" and the other "historic". You must bring the two query results
together to do this. Here's what I would suggest:

SELECT SalesRep, Manufacturer, SUM([Shipped Amount] AS CurrAmt, 0 AS PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
UNION ALL
SELECT SalesRep, Manufacturer, 0, SUM([Shipped Amount]
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]

This is just a step along the path. Notice that you have 2 rows for each
SalesRep / Manufacturer. One has the CurrAmt and the other has the PrevAmt.

Next, add them together:

SELECT SalesRep, Manufacturer, SUM(CurrAmt) AS Current, SUM(PrevAmt) AS
Previous
FROM (
SELECT SalesRep, Manufacturer, SUM([Shipped Amount] AS CurrAmt, 0 AS PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
UNION ALL
SELECT SalesRep, Manufacturer, 0, SUM([Shipped Amount]
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
) X
GROUP BY SalesRep, Manufacturer

This is a common technique to do the thing I believe you intend. Any luck?

UNION ALL combines to recordsets with identical number and types of columns
into one. Aggregating this into a single result is done by a simple query
of the type you are accustomed to making.

Note that a UNION query CANNOT be created in the design grid. This query
requires manual coding. As query programmers mature, they typically are
forded to learn to code queries by typing the SQL. With a bit of practice,
this is very rewarding in the results produced.

Tom Ellison
Microsoft Access MVP

I am having problems with a comparative query. I have the following table:
[Tbl_Commission]
[Sales Rep]
[Manufacturer]
[Shipped Amount] (this is totaled)
[Shipped Date] (between start/end dates)

I have created two identical queries that run on different start/end dates
creating the comparative period. Qry1's total is called [Current] and
Qry2's
total is [Prior]. I can show the figures I want either per manufacturer
or
per Sales Rep but not by both.

Here is the SQL for one of the single queries:

SELECT Tbl_Commission.SalesRep, Tbl_Commission.Manufacturer, Sum
(Tbl_Commission.[Shipped Amount]) AS [SumOfShipped Amount], [SumOfShipped
Amount] AS [Current]
FROM Tbl_Commission
WHERE (((Tbl_Commission.ShippedDate) Between [Enter Current Start Date:]
And
[Enter Current End Date:]))
GROUP BY Tbl_Commission.SalesRep, Tbl_Commission.Manufacturer;

I want the final query to group by Sales Rep & Manufacturer while showing
the
Current and Prior totals from Qry's 1 & 2. I can add the totals and
compute
the math but it's the grouping that I seem to have a problem with.

I appreciate any and all assistance,

Thanks,
Brian

 

[Michael Gramelspacher]

I am having problems with a comparative query. I have the following table:
[Tbl_Commission]
[Sales Rep]
[Manufacturer]
[Shipped Amount] (this is totaled)
[Shipped Date] (between start/end dates)

I have created two identical queries that run on different start/end dates
creating the comparative period. Qry1�s total is called [Current] and Qry2�s
total is [Prior]. I can show the figures I want either per manufacturer or
per Sales Rep but not by both.

Here is the SQL for one of the single queries:

SELECT Tbl_Commission.SalesRep, Tbl_Commission.Manufacturer, Sum
(Tbl_Commission.[Shipped Amount]) AS [SumOfShipped Amount], [SumOfShipped
Amount] AS [Current]
FROM Tbl_Commission
WHERE (((Tbl_Commission.ShippedDate) Between [Enter Current Start Date:] And
[Enter Current End Date:]))
GROUP BY Tbl_Commission.SalesRep, Tbl_Commission.Manufacturer;

I want the final query to group by Sales Rep & Manufacturer while showingthe
Current and Prior totals from Qry�s 1 & 2. I can add the totals and compute
the math but it�s the grouping that I seem to have a problem with.

I appreciate any and all assistance,

Thanks,
Brian

Perhaps like this:

SELECT Commission.[Sales Rep],
Commission.Manufacturer,
SUM(IIF(DATEDIFF("q",[Enter Date:],Commission.[Shipped Date]) = -1,
Commission.[Shipped Amt],0)) AS Last_Qtr_Sales,
SUM(IIF(DATEDIFF("q",[Enter Date:],Commission.[Shipped Date]) = 0,
Commission.[Shipped Amt],0)) AS Current_Qtr_Sales
FROM Commission
WHERE Commission.[Shipped Amt] IS NOT NULL
AND Commission.[Shipped Date] >=# 1 / 1 / 2007 #
GROUP BY Commission.[Sales Rep],Commission.Manufacturer;

The query gets totals for the current quarter, which ever quarter [Enter
Date:]is, and the quarter prior to that. #1/1/2007# should be changed to
the start of the range you want to work with.

 

[bg17067 via AccessMonster.com]

Tom,

I attempted to use the second example you gave but received an error saying:
Syntax Error in FROM clause.

By looking at your examples it appears that the first ends up being a
subquery in the second one.

Thanks again for your help.

Dear BG:

I'm going to make a guess. Your table Tbl_Commission has a unique key for
SalesRep / Manufacturer / ShippedDate.

If there are 10 rows in the table for a single SalesRep / Manufacturer, each
for a different ShippedDate, which fall between the dates entered, then you
are going to add together the values in the [Shipped Amount] column and call
that [SumOfShipped Amount]. But the next thing is surprising. You
reference [SumOfShipped Amount] as though it were a column in the table. It
is not by your definition of the problem. It is however an alias you have
selected for the column you just calculated when you summed [Shipped
Amount].

Why would you want another column with the same value in it anyway. Is that
what you intended?

Now, you said this is intended to be a comparative query. I'm thinking you
want to bring together the two queries to make a comparison. I assume one
is "current" and the other "historic". You must bring the two query results
together to do this. Here's what I would suggest:

SELECT SalesRep, Manufacturer, SUM([Shipped Amount] AS CurrAmt, 0 AS PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
UNION ALL
SELECT SalesRep, Manufacturer, 0, SUM([Shipped Amount]
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]

This is just a step along the path. Notice that you have 2 rows for each
SalesRep / Manufacturer. One has the CurrAmt and the other has the PrevAmt.

Next, add them together:

SELECT SalesRep, Manufacturer, SUM(CurrAmt) AS Current, SUM(PrevAmt) AS
Previous
FROM (
SELECT SalesRep, Manufacturer, SUM([Shipped Amount] AS CurrAmt, 0 AS PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
UNION ALL
SELECT SalesRep, Manufacturer, 0, SUM([Shipped Amount]
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
) X
GROUP BY SalesRep, Manufacturer

This is a common technique to do the thing I believe you intend. Any luck?

UNION ALL combines to recordsets with identical number and types of columns
into one. Aggregating this into a single result is done by a simple query
of the type you are accustomed to making.

Note that a UNION query CANNOT be created in the design grid. This query
requires manual coding. As query programmers mature, they typically are
forded to learn to code queries by typing the SQL. With a bit of practice,
this is very rewarding in the results produced.

Tom Ellison
Microsoft Access MVP

I am having problems with a comparative query. I have the following table:
[Tbl_Commission]

[quoted text clipped - 31 lines]

Thanks,
Brian

 

[Tom Ellison]

Dear BG:

Indeed, it is a subquery. The first query I gave is the basis. Does this
query work?

Which query errors, the first or the second, or both?

The letter X I put in the second query may be required, or it may be a
problem. Remove it and see.

I'll try to study this a bit later and I may come up with something. I have
work to do right now, just taking a short break.

Tom Ellison
Microsoft Access MVP

Tom,

I attempted to use the second example you gave but received an error
saying:
Syntax Error in FROM clause.

By looking at your examples it appears that the first ends up being a
subquery in the second one.

Thanks again for your help.

Dear BG:

I'm going to make a guess. Your table Tbl_Commission has a unique key for
SalesRep / Manufacturer / ShippedDate.

If there are 10 rows in the table for a single SalesRep / Manufacturer,
each
for a different ShippedDate, which fall between the dates entered, then
you
are going to add together the values in the [Shipped Amount] column and
call
that [SumOfShipped Amount]. But the next thing is surprising. You
reference [SumOfShipped Amount] as though it were a column in the table.
It
is not by your definition of the problem. It is however an alias you have
selected for the column you just calculated when you summed [Shipped
Amount].

Why would you want another column with the same value in it anyway. Is
that
what you intended?

Now, you said this is intended to be a comparative query. I'm thinking
you
want to bring together the two queries to make a comparison. I assume one
is "current" and the other "historic". You must bring the two query
results
together to do this. Here's what I would suggest:

SELECT SalesRep, Manufacturer, SUM([Shipped Amount] AS CurrAmt, 0 AS
PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
UNION ALL
SELECT SalesRep, Manufacturer, 0, SUM([Shipped Amount]
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]

This is just a step along the path. Notice that you have 2 rows for each
SalesRep / Manufacturer. One has the CurrAmt and the other has the
PrevAmt.

Next, add them together:

SELECT SalesRep, Manufacturer, SUM(CurrAmt) AS Current, SUM(PrevAmt) AS
Previous
FROM (
SELECT SalesRep, Manufacturer, SUM([Shipped Amount] AS CurrAmt, 0 AS
PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
UNION ALL
SELECT SalesRep, Manufacturer, 0, SUM([Shipped Amount]
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
) X
GROUP BY SalesRep, Manufacturer

This is a common technique to do the thing I believe you intend. Any
luck?

UNION ALL combines to recordsets with identical number and types of
columns
into one. Aggregating this into a single result is done by a simple query
of the type you are accustomed to making.

Note that a UNION query CANNOT be created in the design grid. This query
requires manual coding. As query programmers mature, they typically are
forded to learn to code queries by typing the SQL. With a bit of
practice,
this is very rewarding in the results produced.

Tom Ellison
Microsoft Access MVP

I am having problems with a comparative query. I have the following
table:
[Tbl_Commission]

[quoted text clipped - 31 lines]

Thanks,
Brian

--
B. Goldhammer
(e-mail address removed)

Message posted via AccessMonster.com

 

[Michael Gramelspacher]

Dear BG:

Indeed, it is a subquery. The first query I gave is the basis. Does this
query work?

Which query errors, the first or the second, or both?

The letter X I put in the second query may be required, or it may be a
problem. Remove it and see.

I'll try to study this a bit later and I may come up with something. I have
work to do right now, just taking a short break.

Tom Ellison
Microsoft Access MVP

Tom,

I attempted to use the second example you gave but received an error
saying:
Syntax Error in FROM clause.

By looking at your examples it appears that the first ends up being a
subquery in the second one.

Thanks again for your help.

Dear BG:

I'm going to make a guess. Your table Tbl_Commission has a unique key for
SalesRep / Manufacturer / ShippedDate.

If there are 10 rows in the table for a single SalesRep / Manufacturer,
each
for a different ShippedDate, which fall between the dates entered, then
you
are going to add together the values in the [Shipped Amount] column and
call
that [SumOfShipped Amount]. But the next thing is surprising. You
reference [SumOfShipped Amount] as though it were a column in the table.
It
is not by your definition of the problem. It is however an alias you have
selected for the column you just calculated when you summed [Shipped
Amount].

Why would you want another column with the same value in it anyway. Is

Tom,

This seems to work for me. My column names may be different, but it is
really just restating your query. My impression though was that the OP
wanted the current and prior quarter totals, and I think that can be gotten
a bit easier.

This works for me, but I had always thought that a bracketed subquery in
place of a table could not have brackets within it. Probably just a
misconception on my part.

SELECT [Sales Rep], Manufacturer, SUM(CurrAmt) AS [Current], SUM(PrevAmt)
AS Previous
FROM [SELECT [Sales Rep], Manufacturer, SUM([Shipped Amt]) AS CurrAmt, 0 AS
PrevAmt
FROM Commission
WHERE [Shipped Date] BETWEEN [Enter Prior Start Date] AND [Enter Prior End
Date]
GROUP BY [Sales Rep], Manufacturer
UNION ALL SELECT [Sales Rep], Manufacturer, 0 AS CurrAmt, SUM([Shipped
Amt]) AS PrevAmt
FROM Commission
WHERE [Shipped Date] BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
GROUP BY [Sales Rep], Manufacturer]. AS some_Alias
GROUP BY [Sales Rep], Manufacturer;

 

[bg17067 via AccessMonster.com]

Tom,

Sorry for taking so long to get back to you. My primary occupation (law
enforcement) has kept me busy these past few days. I'm going to sit down
tomorrow and test this again

I also want to thank everyone else for their input. I'm sure its a minor
mistake on my end that's causing the problem.

Brian

Dear BG:

Indeed, it is a subquery. The first query I gave is the basis. Does this
query work?

Which query errors, the first or the second, or both?

The letter X I put in the second query may be required, or it may be a
problem. Remove it and see.

I'll try to study this a bit later and I may come up with something. I have
work to do right now, just taking a short break.

Tom Ellison
Microsoft Access MVP

Tom,

[quoted text clipped - 92 lines]

 

[Michael Gramelspacher]

Tom,

Sorry for taking so long to get back to you. My primary occupation (law
enforcement) has kept me busy these past few days. I'm going to sit down
tomorrow and test this again

I also want to thank everyone else for their input. I'm sure its a minor
mistake on my end that's causing the problem.

Brian

Dear BG:

Indeed, it is a subquery. The first query I gave is the basis. Does this
query work?

Which query errors, the first or the second, or both?

The letter X I put in the second query may be required, or it may be a
problem. Remove it and see.

I'll try to study this a bit later and I may come up with something. I have
work to do right now, just taking a short break.

Tom Ellison
Microsoft Access MVP

Tom,

[quoted text clipped - 92 lines]

Thanks,
Brian

A clarification on the subquery bracketing issue. Where I had
FROM [SELECT .......Manufacturer]. AS some_alias

change to:

FROM (SELECT ........Manufacturer) AS some_alias

Access will automatically change the parenthesis to brackets when it runs
the query and will save the query with brackets.

If you later edit the saved query with the brackets in SQL View, the
brackets may needed to be changed back to parenthesis to avoid the Invalid
Bracketing error.

I am writing from my experience with Access 2003.

 

[bg17067 via AccessMonster.com]

Michael & Tom,

Here's what my SQL statement (I changed values to match table&field names):

SELECT SalesRep, Manufacturer, SUM(CurrAmt) AS [Current], SUM(PrevAmt)
AS Previous
FROM (SELECT SalesRep, Manufacturer, SUM([Shipped Amount]) AS CurrAmt, 0 AS
PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Prior Start Date] AND [Enter Prior End Date]
GROUP BY SalesRep, Manufacturer)
UNION ALL SELECT SalesRep, Manufacturer, 0 AS CurrAmt, SUM([Shipped Amount])
AS PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current End
Date]
GROUP BY SalesRep, Manufacturer. AS alias
GROUP BY SalesRep, Manufacturer

The error msg I get now says:
Syntax error (missing operator) in query expression 'Manufactuerer. AS alias
GROUP BY SalesRep'

I assumed this was about the last grouping issue but I haven't been able to
find a fix for it.


P.S.
Michael,
I am not looking for quarters but the comparison between two separate set of
dates. i.e. (Current: 01/01/07-04/15/07) and (Prior: 01/01/06-04/15/06) the
user wants the ability to compare any date ranges they like. I tested your
example and it worked great for quarterly purposes.

Dear BG:

[quoted text clipped - 42 lines]
Tom,

This seems to work for me. My column names may be different, but it is
really just restating your query. My impression though was that the OP
wanted the current and prior quarter totals, and I think that can be gotten
a bit easier.

This works for me, but I had always thought that a bracketed subquery in
place of a table could not have brackets within it. Probably just a
misconception on my part.

SELECT [Sales Rep], Manufacturer, SUM(CurrAmt) AS [Current], SUM(PrevAmt)
AS Previous
FROM [SELECT [Sales Rep], Manufacturer, SUM([Shipped Amt]) AS CurrAmt, 0 AS
PrevAmt
FROM Commission
WHERE [Shipped Date] BETWEEN [Enter Prior Start Date] AND [Enter Prior End
Date]
GROUP BY [Sales Rep], Manufacturer
UNION ALL SELECT [Sales Rep], Manufacturer, 0 AS CurrAmt, SUM([Shipped
Amt]) AS PrevAmt
FROM Commission
WHERE [Shipped Date] BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
GROUP BY [Sales Rep], Manufacturer]. AS some_Alias
GROUP BY [Sales Rep], Manufacturer;

 

[Michael Gramelspacher]

Manufactuerer. AS alias

Just substitute a ) for the period so it reads
Manufacturer) AS alias

It the query runs, save it and you will see that Access
changes the subquery parenthesis to brackets with a
period.

 

[bg17067 via AccessMonster.com]

This thing is causing me to pull my hair out.

I inserted the ) and now get the following: syntax error in UNION query.

 

[Tom Ellison]

Dear BG:

I'll try writing the whole thing the way I think you may need it:

SELECT SalesRep, Manufacturer, SUM(CurrAmt) AS [Current], SUM(PrevAmt) AS
Previous
FROM
(SELECT SalesRep, Manufacturer, SUM([Shipped Amount]) AS CurrAmt, 0 AS
PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Prior Start Date] AND [Enter Prior End
Date]
GROUP BY SalesRep, Manufacturer)
UNION ALL
SELECT SalesRep, Manufacturer, 0 AS CurrAmt, SUM([Shipped Amount]) AS
PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
GROUP BY SalesRep, Manufacturer) AS X
GROUP BY SalesRep, Manufacturer

Access may mangle how this reads, but I believe it will accept it. Please
let me know.

Tom Ellison
Microsoft Access MVP

Michael & Tom,

Here's what my SQL statement (I changed values to match table&field
names):

SELECT SalesRep, Manufacturer, SUM(CurrAmt) AS [Current], SUM(PrevAmt)
AS Previous
FROM (SELECT SalesRep, Manufacturer, SUM([Shipped Amount]) AS CurrAmt, 0
AS
PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Prior Start Date] AND [Enter Prior End
Date]
GROUP BY SalesRep, Manufacturer)
UNION ALL SELECT SalesRep, Manufacturer, 0 AS CurrAmt, SUM([Shipped
Amount])
AS PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End
Date]
GROUP BY SalesRep, Manufacturer. AS alias
GROUP BY SalesRep, Manufacturer

The error msg I get now says:
Syntax error (missing operator) in query expression 'Manufactuerer. AS
alias
GROUP BY SalesRep'

I assumed this was about the last grouping issue but I haven't been able
to
find a fix for it.


P.S.
Michael,
I am not looking for quarters but the comparison between two separate set
of
dates. i.e. (Current: 01/01/07-04/15/07) and (Prior: 01/01/06-04/15/06)
the
user wants the ability to compare any date ranges they like. I tested
your
example and it worked great for quarterly purposes.

Dear BG:

[quoted text clipped - 42 lines]

Why would you want another column with the same value in it anyway.
Is

Tom,

This seems to work for me. My column names may be different, but it is
really just restating your query. My impression though was that the OP
wanted the current and prior quarter totals, and I think that can be
gotten
a bit easier.

This works for me, but I had always thought that a bracketed subquery in
place of a table could not have brackets within it. Probably just a
misconception on my part.

SELECT [Sales Rep], Manufacturer, SUM(CurrAmt) AS [Current], SUM(PrevAmt)
AS Previous
FROM [SELECT [Sales Rep], Manufacturer, SUM([Shipped Amt]) AS CurrAmt, 0
AS
PrevAmt
FROM Commission
WHERE [Shipped Date] BETWEEN [Enter Prior Start Date] AND [Enter Prior End
Date]
GROUP BY [Sales Rep], Manufacturer
UNION ALL SELECT [Sales Rep], Manufacturer, 0 AS CurrAmt, SUM([Shipped
Amt]) AS PrevAmt
FROM Commission
WHERE [Shipped Date] BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
GROUP BY [Sales Rep], Manufacturer]. AS some_Alias
GROUP BY [Sales Rep], Manufacturer;

--
B. Goldhammer
(e-mail address removed)

Message posted via AccessMonster.com

 

[Michael Gramelspacher]

This thing is causing me to pull my hair out.

I inserted the ) and now get the following: syntax error in UNION query.

There should be no parenthesis before UNION ALL.

 

[bg17067 via AccessMonster.com]

Michael,

IT WORKED!!!

Thank you so much! This forum has once again pulled me out of the deep dark
language of SQL programming.

 

[bg17067 via AccessMonster.com]

Tom,

This worked great! Thank you so much for your help. I was able to use this
as a format to make some other comparative queries and everything is working
fine.

Dear BG:

I'll try writing the whole thing the way I think you may need it:

SELECT SalesRep, Manufacturer, SUM(CurrAmt) AS [Current], SUM(PrevAmt) AS
Previous
FROM
(SELECT SalesRep, Manufacturer, SUM([Shipped Amount]) AS CurrAmt, 0 AS
PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Prior Start Date] AND [Enter Prior End
Date]
GROUP BY SalesRep, Manufacturer)
UNION ALL
SELECT SalesRep, Manufacturer, 0 AS CurrAmt, SUM([Shipped Amount]) AS
PrevAmt
FROM Tbl_Commission
WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current
End Date]
GROUP BY SalesRep, Manufacturer) AS X
GROUP BY SalesRep, Manufacturer

Access may mangle how this reads, but I believe it will accept it. Please
let me know.

Tom Ellison
Microsoft Access MVP

Michael & Tom,

[quoted text clipped - 74 lines]

GROUP BY [Sales Rep], Manufacturer]. AS some_Alias
GROUP BY [Sales Rep], Manufacturer;