Creating a monthly summary report with SQL

[davidcantor]

I am having trouble creating a monthly summary report see below the output and SQL code as you can see the values are repeating and only th elast column is correct.

Code: Select all

SELECT MonthName(Month(Date)) AS [Month], Count(Sheet1.customerID) AS NumberOfCustomers, Count(Sheet1.invoiceID) AS NumberOfInvoices, Count(Sheet1.productID) AS NumberOfProducts, Sum(Sheet1.Income) AS [TotalIncome]
FROM Sheet1
WHERE (((Year([Date]))="2016"))
GROUP BY MonthName(Month(Date))
ORDER BY Sum(Sheet1.Income) desc ;

[table=]Month NumberOfCustomers NumberOfInvoices NumberOfProducts TotalIncome
January 18 18 18 10300
February 4 4 4 2200
March 3 3 3 2100
April 1 1 1 400[/table]

[HansV]

The Count function simply counts the number of non-null values in the specified field; assuming that there are no blanks, this will yield the same result for all fields.
Did you want to count the number of distinct (unique) customers per month, etc.?

[davidcantor]

yes
where do I put the distinct command?

[HansV]

This is more complicated than you'd think. You'll have to create a separate query for each of the components, and then combine them.

1) A query for total income, named qryTotalIncome

SELECT Year([Date]) AS Y, Month([Date]) As M, Sum(Income) AS TotalIncome FROM Sheet1 GROUP BY Year([Date]), Month([Date])

2) A query for unique customers, named qryDistinctCustomers:

SELECT Y, M, Count(*) AS NumberOfCustomers FROM (SELECT DISTINCT Year([Date]) AS Y, Month([Date]) AS M, CustomerID FROM Sheet1) AS T GROUP BY Y, M

3) A query for unique invoices, named qryDistinctInvoices:

SELECT Y, M, Count(*) AS NumberOfInvoices FROM (SELECT DISTINCT Year([Date]) AS Y, Month([Date]) AS M, InvoiceID FROM Sheet1) AS T GROUP BY Y, M

4) A query for unique products, named qryDistinctProducts:

SELECT Y, M, Count(*) AS NumberOfProducts FROM (SELECT DISTINCT Year([Date]) AS Y, Month([Date]) AS M, ProductID FROM Sheet1) AS T GROUP BY Y, M

The combined query: create a query in design view.
Add qryTotalIncome, qryDistinctCustomers, qryDistinctInvoices, qryDistinctProducts.
Join the first table to each of the others on Y and M.
Add Y to the query grid, clear its Show check box and enter 2016 in the criteria row.
In the second column, enter Month: MonthName(M)
Add the NumberOfCustomers, NumberOfInvoices, NumberOfProducts and TotalIncome fields from their tables to the query grid.

[davidcantor]

Thnak you for your clear directions!