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.
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]
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.?
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.