Query by month and year

  • Thread starter Thread starter KrispyData
  • Start date Start date
K

KrispyData

Let's say I have 3 columns:

Customer
Year
Month
Sales

I want to calculate the sales for each year and each month. In other words
I want columns for 2007 sales, 2008 sales, 2009 sales, January 2008 sales,
February 2008, and so on for each customer.

I'm thinking that I need to create an query with criteria for each time
period that I want. That will be over 48 queries! There's gotta be a better
way. Is there way a way to do something to similar to Subtotals in Excel
here in Access?
 
48 queries would be 47 more than you need.

If your sales table has a field for SalesDate, that's all you need to
view the data by any date related value you want.

You would use *one* Totals query, with calculated fields like;

SaleYear: Year([SalesDate])
SaleMonth: Format(Month([SalesDate]), "mmmm")
etc.

You could then have a report based on this query where you could
add subtotals, overall totals, etc. You could also have a form based
this query as well, but be aware that it would be read only, so it
would only be useful for display purposes.
 
Hi Beetle
I tried the calculated field you suggested and got an invalid syntax error.
Maybe you didnt mean for me to put that in as an expression? (i'm still very
new to Access!).
I think what i'm looking for is a pivot table format. I used the pivot
table view of my table and i am getting the results that I want. Is there a
way I can save this view and make it a new table?
 
You can create a Crosstab Query.

Go to queries New/Crosstab Query Wizard. It should guide you through
it.
 
You don't need to create a new table, and more importantly should not do as
the results can be computed from the existing data at any time. Storing data
derived from other data should only be done in circumstances where the
original data can change, but you want the computed values to remain static;
the price of a product in an invoice is an example as you want the invoice to
retain the price from the time it was created, not the current price of the
product in a Products table. Otherwise it introduces redundancy and leaves
the database open to inconsistent data being entered.

You can return the total sales per month per year with a crosstab query:

TRANSFORM SUM(Sales) AS TotalSales
SELECT [Month]
FROM YourTable
GROUP BY [Month]
PIVOT [Year];

Or if you want the sales per customer per month per year:

TRANSFORM SUM(Sales) AS TotalSales
SELECT Customer, [Month]
FROM YourTable
GROUP BY Customer, [Month]
PIVOT [Year];

BTW avoid naming columns Year, month etc. These are the names of built in
functions in Access, so naming objects with the same name can cause confusion
in some circumstances. If you do use them then be sure to wrap them in
square brackets when referencing them in queries or in code, but its best to
use more specific terms such as SalesYear, SalesMonth.

Ken Sheridan
Stafford, England
 
Back
Top