Simple way to count by month?

[Steve B]

Access2000

Hi I have a table that is just a list of serial numbers and dates that they
were sold.

Is there a simple way to build a query that will just list the total serials
numbers by month, i.e.

Dec-07 - 566
Nov-07- 453
Oct-07- 343

Each serial is on its own row. Getting the number sold by day is simple, but
grouping and suming these to months has me stumped.

Thanks - Steve

 

[Steve B]

Solved it.

I used this if anybody else is looking

SELECT Format([SQL_DELIVERY_DATE],"yyyy mm") AS MonthSold, Count(*) AS Total
FROM tbl_sold
GROUP BY Format([SQL_DELIVERY_DATE],"yyyy mm")
ORDER BY Format([SQL_DELIVERY_DATE],"yyyy mm");


SQL_DELIVERY_DATE

 

[John W. Vinson]

Access2000

Hi I have a table that is just a list of serial numbers and dates that they
were sold.

Is there a simple way to build a query that will just list the total serials
numbers by month, i.e.

Dec-07 - 566
Nov-07- 453
Oct-07- 343

Each serial is on its own row. Getting the number sold by day is simple, but
grouping and suming these to months has me stumped.

Thanks - Steve

You can use a calculated field in a Totals query. Assuming the fields are
named SerialNo and SaleDate, try

SELECT Format([saledate], "mmm-yy"), Count(*)
FROM yourtable
GROUP BY Year([saledate]), Month([saledate]), Format([saledate], "mmm-yy")
WHERE saledate BETWEEN [Enter start date:] AND [Enter end date:];



I'm using Format to convert the date to a string, but grouping by the Year and
Month rather than by the string, since the string will sort alphabetically
(April first) rather than chronologically.

John W. Vinson [MVP]

 

[Steve B]

Thanks John.

Access2000

Hi I have a table that is just a list of serial numbers and dates that
they
were sold.

Is there a simple way to build a query that will just list the total
serials
numbers by month, i.e.

Dec-07 - 566
Nov-07- 453
Oct-07- 343

Each serial is on its own row. Getting the number sold by day is simple,
but
grouping and suming these to months has me stumped.

Thanks - Steve

You can use a calculated field in a Totals query. Assuming the fields are
named SerialNo and SaleDate, try

SELECT Format([saledate], "mmm-yy"), Count(*)
FROM yourtable
GROUP BY Year([saledate]), Month([saledate]), Format([saledate], "mmm-yy")
WHERE saledate BETWEEN [Enter start date:] AND [Enter end date:];



I'm using Format to convert the date to a string, but grouping by the Year
and
Month rather than by the string, since the string will sort alphabetically
(April first) rather than chronologically.

John W. Vinson [MVP]