Month over Month (previous year) query

[nebula13]

Hello everyone,
I am having a problem creating a query that uses the monthly volume number and finds the corresponding volume for the same month of the previous year.

I am trying to use the DLookup function, but it only shows the first record it finds. To clarify:

Table: Monthly_Volume
Fields: Date(mm/yyyy), Volume

The point of the query is to retrieve the volume that was listed for the same month of the previous year. e.g. $1000 volume on 12/2009 would retrieve $XXX volume on 12/2008

I expect the code to look something like:
Volume_PreviousYear: DLookup([volume],[Monthly_Volume],[Criteria])

Does anyone know what I need to put for Criteria to get this to work?
Should I be using the DateAdd function???

Thanks in advanced for your time and help!

All the best,
Ben

 

[Will_B]

I think the simplest approach is to create two queries, and then link them in a third query. The concept is: the first query would be this year's data, the second query would be of last year's data. You would then link the two queries by a new column you'd add to each query called "MyMonth". This will put each month's data, this year's and last year's months, side by side. Here are the details...

Query #1 would be all volumes for the CURRENT year --- and you add two columns to that query, each of which would be a formula rather than a field from your table. Those columns are: 1) MyMonth: Month([MyDate]) 2) MyYear: Year([MyDate]) --- replacing "MyDate" with the name of your date field. MyMonth would have no criteria (it will just give us the number of the month, such as "1" for January, "2" for February, and so on. You do need a criterion for the MyYear column, so make that criterion be the formula Year(Date) --- type it exactly as shown. This will give you the current year. When you view the results of this query, you should see only data for 2009 (at least for a couple more weeks).

Query #2 is similiar to Query #1, so you can start by simply making a copy of Query #1. In edit mode, change the MyYear criterion to be Year(Date)-1. This will give you only LAST year's data (which will be 2008 for the next couple of weeks.

In Query #3, add your two queries to the grid and link them by the MyMonth field. As a result, April 2009 will be side-by-side with the data for April 2008, and so on for every month in 2009. You can play around with the column names to get them to something you like.

Because we used a formula based on current date, your queries will not need updating come the New Year. If you need to report the previous 12 months'-worth of data (so that in Janauary you are looking at the previous 12 months and not just January 2010 vs. January 2009), you can play around with the date criteria in each query to get the results you need --- but this should get you started.

 

[SydneyGeek]

A variation on Will_B's approach; you can make the first query a crosstab.

An example using tables from the Northwind database --
Add the Order Details and Orders tables to the grid in query design
The extended price is an expression; [Quantity]*[Unit Price]*(1-[Discount])
Now, use the Order Date three times in the grid.
The expressions are --
OrderMonth: Format([Order Date],"mmm")
Month([OrderDate])
Year([OrderDate])

Now, turn it into a crosstab. Query > Crosstab (or Design > Crosstab for 2007)
OrderMonth is a Row Heading. Group By
Month is sorted Ascending. Group By but leave the Crosstab item blank so it doesn't display
Year is a Column Heading. Group By
Extended is a Value. Sum

The whole SQL is:

TRANSFORM Sum([Quantity]*[Unit Price]*(1-[Discount])) AS Extended
SELECT Format([Order Date],"mmm") AS OrderMonth
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Format([Order Date],"mmm"), Month([Order Date])
ORDER BY Month([Order Date])
PIVOT Year([Order Date]);

Denis