Sum Every 2 Months

[darreno]

I need help on a query. There are 3 columns in the table: Month, Company, Amount. I need to add the amount of every 2 months to find the highest 2 month average.

Example:

Date Company Amount 2-Month
Jan 07 AAA $100 $100
Feb 07 AAA $200 $300
Mar 07 AAA $300 $500
Apr 07 AAA $100 $400

Thanks.

 

[Guus2005]

You need something to group two months together. e.g. a table:
Two fields, both numeric(byte or bigger) we call this table GroupMonths
This is what's in the table GroupMonths:
Fields: Month GroupMonth
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4
9 5
10 5
11 6
12 6

Now you can link your example to the month field, and group by GroupMonth. Do a sum on the amounts and there you have it.

Each two months are now grouped. Jan with Feb, etc.

HTH

 

[namliam]

If your date is a real date field, you can use the function dateadd to add 1 month to your date.

Then make 2 queries,
1)
Select [original table].*, {add 1 month to date field}
from [original table]

2)
Link to query one and [original table] and add your information that you need to add.

 

[Guus2005]

@namliam

Can you post a sample database namliam?
I am very curious how that works. Because i dont quite understand.

 

[raskew]

Hi -

Different solution using the dLookup() function. Made the following changes to the original sample:
1) Added autonumber field [ID]
2) Renamed field [Month] to [theMonth] since Month is a reserved word in access.
3) Restructured [theMonth] data from 'Jan 07' to 'Jan 2007' since the original format doesn't represent a date whereas the replacement can be turned into a valid date using the DateValue() function. 'Jan 07' could be turned into a date using DateSerial(), but would also require Mid() and Left() functions to extract the month and year components.

SELECT
    tblPrevRec.ID
  , tblPrevRec.TheMonth
  , tblPrevRec.Company
  , tblPrevRec.Amount
  , [amount]+nz(DLookUp("[Amount]","Query100","ID =  " & [ID]-1),0) AS 2_Month
FROM
   tblPrevRec
ORDER BY
   tblPrevRec.ID;

This query works. However, it relies on theMonth to be in corresponding sequence with [ID]. Insert a date out of order and the problems start.

Adding a [FullDate] calculated field, i.e. FullDate: DateValue(TheMonth), I attempted to base the criteria of DLookUP to "FullDate = " & DateAdd("m", -1, FullDate), but was unable to get the syntax correctly. Had I got it to work, could then sort on [FullDate] and do away with the problems with [ID].

HTH - Bob

 

[namliam]

DLookup is INCREADABLY slow tho, a sub query would be much faster.

See attached DB for the general idea, Query2 is what you are looking for.

 

[raskew]

Yeah, that works quite well!

Here's a working example based on Northwind's Orders table which will return the current and previous months' totals as requested by the OP. Just copy the SQL to new queries named Query103 and Query104 respectively:
Query103

SELECT
    DateSerial(Year([OrderDate]),Month([OrderDate]),1) AS CurMonth
  , Sum(a.Freight) AS SumOfFreight
  , DateAdd("m",-1,[CurMonth]) AS PrevMonth
FROM
   Orders AS a
GROUP BY
   DateSerial(Year([OrderDate]),Month([OrderDate]),1)
ORDER BY
   DateSerial(Year([OrderDate]),Month([OrderDate]),1);

Query104

SELECT
    Query103.CurMonth
  , Query103.SumOfFreight
  , Query103_1.SumOfFreight
  , [query103].[SumOfFreight]+nz([query103_1].[sumoffreight],0) AS Two_Month
FROM
   Query103 
LEFT JOIN
   Query103 AS Query103_1 
ON
   Query103.PrevMonth = Query103_1.CurMonth
ORDER BY
   Query103.CurMonth;

Bob

 

[darreno]

I think I got it. I made 3 queries: one with the original settings, the second with an added month, and then the third to link the two together. Attached is the DB. Thanks to all!

 

[namliam]

You could do one and two in one, or even all in one... but... it works ... Good for you

One thing: Do not use spaces in any of your names....

Another thing:
Instead of doing this: 2MonthSum: [Data 1.Amount]+[Data 2.Amount]
Which is revering to fields with your query it is better to point back to the original fields like so:
2MonthSum: [Data 1].[Amount]+[Data 2].[Amount]


Other than that, well done !