YTD Subquery problems

[equalizer]

I am trying to create a report that allows the user to select a
specific month and year for which to see MTD and YTD sales figures for
specific products. It must be a summary report so I only want it to
show product sales information for the selected month. Here's an
example:

Product Date MTD YTD
Product1 Feb, 2006 100 200
Prdouct2 Feb, 2006 200 250
Product3 Feb, 2006 300 350

After researching, I thought a subquery would be the best way to
accomplish this but I've never used subqueries and after several
attempts I have failed to get it to work the way I need it to. Can
anyone help me?

 

[Ken Snell \(MVP\)]

A subquery may be the answer, but you'll need to show us examples of the
actual data that are in the tables so that we can see how to construct the
correct query.

 

[equalizer]

Well, the transaction table contains product names, order dates (date
format), and sales amounts for each transaction taking place. There are
many transactions for each product that take place each day. Here's an
example of a snapshot from a typical day:

Product Date Qty Amount SO#
90-0600 1/1/2006 10 $45 982
90-1000 1/1/2006 5 $50
983
90-1200 1/1/2006 10 $55 984
90-1200 1/1/2006 20 $110 985
90-0600 1/1/2006 20 $90 986
90-1000 1/1/2006 10 $100 987
etc.

I need to summarize the Qty and Amount totals by month and then show
YTD amounts for the selected month.

 

[Ken Snell \(MVP\)]

Not too difficult, I believe. Something like this (assuming that you need to
see the Product info as well) -- replace TableName with the actual name of
your table:

SELECT Format(T.[Date], "mmm yyyy") AS MonthYear,
T.Product, SUM(T.Qty) AS QtySum, SUM(T.Amount) AS AmountSum,
(SELECT SUM(TT.Qty) AS TTQ FROM TableName AS TT
WHERE Year(TT.[Date]) = Year(T.[Date]) AND
Month(TT.[Date]) <= Month(T.[Date])
AND TT.Product = T.Product) AS QtyYTD,
(SELECT SUM(TX.Amount) AS TTA FROM TableName AS TX
WHERE Year(TX.[Date]) = Year(T.[Date]) AND
Month(TX.[Date]) <= Month(T.[Date])
AND TX.Product = T.Product) AS AmountYTD
FROM TableName AS T
GROUP BY Format(T.[Date], "mmm yyyy"), T.Product
ORDER BY Year(T.[Date]) DESC, Month(T.[Date]) DESC;


Also, I note that you're using Date as the name of a field in a table. They
and many other words are reserved words in ACCESS and should not be used for
field names, etc. See these Knowledge Base articles for more information
about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

Well, the transaction table contains product names, order dates (date
format), and sales amounts for each transaction taking place. There are
many transactions for each product that take place each day. Here's an
example of a snapshot from a typical day:

Product Date Qty Amount SO#
90-0600 1/1/2006 10 $45 982
90-1000 1/1/2006 5 $50
983
90-1200 1/1/2006 10 $55 984
90-1200 1/1/2006 20 $110 985
90-0600 1/1/2006 20 $90 986
90-1000 1/1/2006 10 $100 987
etc.

I need to summarize the Qty and Amount totals by month and then show
YTD amounts for the selected month.