Count and Sort by Month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to count the rows in a query and display it by Month/Year.
My counting works fine but I am having difficulty displaying per Month/Year.

This is the SQL view as designed by the Design View:
SELECT Count(GRV.[Date Received]) AS [CountOfDate Received], [Stock
File].Product, GRV.[Date Received]
FROM GRV INNER JOIN [Stock File] ON (GRV.Serial = [Stock File].[Serial
Number]) AND (GRV.[Date Received] = [Stock File].[Date Received]) AND
(GRV.[Warranty Period] = [Stock File].[Warranty Period])
GROUP BY [Stock File].Product, GRV.[Date Received]
HAVING ((([Stock File].Product) Like "*Digipos*" Or ([Stock File].Product)
Like "*Epson*"));

Please help :)
 
Try:
SELECT Count(GRV.[Date Received]) AS [CountOfDate Received],
[Stock File].Product, Format("yymm",GRV.[Date Received])
FROM GRV
INNER JOIN [Stock File]
ON (GRV.Serial = [Stock File].[Serial Number])
AND (GRV.[Date Received] = [Stock File].[Date Received])
AND (GRV.[Warranty Period] = [Stock File].[Warranty Period])
GROUP BY [Stock File].Product, Format("yymm",GRV.[Date Received])
HAVING ((([Stock File].Product) LIKE "*Digipos*"
OR ([Stock File].Product) LIKE "*Epson*"));

In case the syntax isn't perfect, the gist is to format the date received
using the last two digits of the year, and the number for the month from the
Date Received in the GRV table.
 
Dunmarie said:
I am trying to count the rows in a query and display it by Month/Year.
My counting works fine but I am having difficulty displaying per Month/Year.

This is the SQL view as designed by the Design View:
SELECT Count(GRV.[Date Received]) AS [CountOfDate Received], [Stock
File].Product, GRV.[Date Received]
FROM GRV INNER JOIN [Stock File] ON (GRV.Serial = [Stock File].[Serial
Number]) AND (GRV.[Date Received] = [Stock File].[Date Received]) AND
(GRV.[Warranty Period] = [Stock File].[Warranty Period])
GROUP BY [Stock File].Product, GRV.[Date Received]
HAVING ((([Stock File].Product) Like "*Digipos*" Or ([Stock File].Product)
Like "*Epson*"));
Click to expand...


What do you mean by "display by month year"?

Do you want the count to be over each month?
If so, use somthing like
Format(GRV.[Date Received], "yyyymm")
instead of the [Date Received] field everywhere except the
Count function.

Or do you want to see the count for each date plus have a
total count for each month?
If this is what you want then you shoul use a report that
groups my month and uses a group footer to display the month
totals.
 
Back
Top