Sum Past three months Revenue

[north19701]

I have a query where I need a calculated field to total 3 months worth of revenue by vendor. For example:

Month Vendor Revenue
12/31/2004 - Jackson - $100.00
1/31/2004 - Jackson - $100.00
2/29/2004 - Jackson - $100.00
3/31/2004 - Jackson - $100.00
4/30/2004 - Jackson - $100.00
12/31/2004 - Murray - $100.00
1/31/2004 - Murray - $100.00
2/29/2004 - Murray - $100.00
3/31/2004 - Murray - $100.00
4/30/2004 - Murray- $100.00

I need to set up a calculated field in the query that will sum vendor totals, in this case from 2/2004 to 4/2004. Of course next month this will change to 3/2004 to 5/2004. Can anyone help with this?

 

[WJReid]

Hi North19701,


If you can add a column with the formula as in column D of the attachment, then do a unique filter to get all of your vendors, the formulae in column G will give you the results based on the current month.

Regards,

Bill

Book2
	A	B	C	D	E	F	G
1	Date	Vendor	Amount	Month		Vendor	Amount
2	30/04/2004	Jackson	$25.00	4		Jackson	$61.00
3	21/03/2004	Jackson	$36.00	3		Smith	$12.00
4	21/01/2004	Jackson	$24.00	1		Roberts	$69.00
5	02/05/2004	Smith	$12.00	5
6	30/03/2004	Roberts	$69.00	3
7
8
9
10
Sheet1

 

[WJReid]

Hi North19701,

Please disregard the last post. I forgot that I had changed forums. I am still in Excel Mode


Regards,

Bill

 

[north19701]

thanks anyway! anyone else have an Access solution? Would DSUM work?

 

[blueshark]

Use a totals select query and try using the following in the date criteria of the grid:

Between Date() And DateAdd("m",-3,Date())

Here's the SQL :

SELECT Table1.Vendor, Sum(Table1.Sales) AS SumOfSales
FROM Table1
WHERE (((Table1.Month) Between Date() And DateAdd("m",-3,Date())))
GROUP BY Table1.Vendor;

 

[north19701]

Thanks for the info. the criteria has changed slightly, though. The three month rolling revenue needs to be in the same query one column to the right of Revenue, so the first example:


Month Vendor Revenue
12/31/2004 - Jackson - $100.00
1/31/2004 - Jackson - $100.00
2/29/2004 - Jackson - $100.00
3/31/2004 - Jackson - $100.00
4/30/2004 - Jackson - $100.00
12/31/2004 - Murray - $100.00
1/31/2004 - Murray - $100.00
2/29/2004 - Murray - $100.00
3/31/2004 - Murray - $100.00
4/30/2004 - Murray- $100.00

would become:

Month Vendor Revenue 3-Month Rolling Revenue
12/31/2004 - Jackson - $100.00 $300.00
1/31/2004 - Jackson - $100.00 $300.00
2/29/2004 - Jackson - $100.00 $300.00
3/31/2004 - Jackson - $100.00 $300.00
4/30/2004 - Jackson - $100.00 $300.00
12/31/2004 - Murray - $100.00 $300.00
1/31/2004 - Murray - $100.00 $300.00
2/29/2004 - Murray - $100.00 $300.00
3/31/2004 - Murray - $100.00 $300.00
4/30/2004 - Murray- $100.00 $300.00

 

[blueshark]

A report (or form) would be the fastest way to get this done. Do you need to manipulate the data or just look at it?

p.s. I miss delaware.

 

[blueshark]

Okay I think I got it. I've posted the code below but you can contact me directly if you want the database.

You'll need three seperate queries.

Query 1: Has nothing to do with this. Just wanted the query numbers here to match the code below.
Query 2. Establishes the 3 month period.
Query 3. Retrieves the 3 month sum
Query 4. Combines 3 month sum with current data


Query 1:

SELECT DateAdd("m",-2,[month]) AS Start_3_Month, Table1.Month AS End_3_Month, Table1.Vendor, Table1.Sales
FROM Table1;

Query 2:

SELECT Table1.Vendor, Query2.End_3_Month, Sum(Table1.Sales) AS SumOfSales
FROM Query2 INNER JOIN Table1 ON Query2.Vendor = Table1.Vendor
WHERE (((Table1.Month) Between [start_3_month] And [end_3_month]))
GROUP BY Table1.Vendor, Query2.End_3_Month
ORDER BY Table1.Vendor;

Query 3:

SELECT Table1.Month, Table1.Vendor, Table1.Sales, Query3.SumOfSales
FROM Query3 INNER JOIN Table1 ON Query3.End_3_Month = Table1.Month
ORDER BY Table1.Month, Table1.Vendor;

[/b]

 

[blueshark]

oops. code for query 4 is missing a join. This may work better.

SELECT Table1.Month, Table1.Vendor, Table1.Sales, Query3.SumOfSales
FROM Query3 INNER JOIN Table1 ON (Query3.End_3_Month = Table1.Month) AND (Query3.Vendor = Table1.Vendor)
ORDER BY Table1.Month, Table1.Vendor;

 

[north19701]

hey blueshark, thanks for the info on this. What part of DE were you from? I worked on this a lot and came up with this. Let me know if this will work as well if you don't mind. It's a calculated field in the same query:

Rolling3MonthRev: CCur(DSum("[Net Revenue]","Center Aging Totals","[Ctr #] = " & [Ctr #] & " AND [As of] >= #" & (DateSerial(Year([As of]),Month([As of])-1,0)) & "# AND [As of] <= #" & (DateSerial(Year([As of]),Month([As of]),Day([As of]))) & "#"))

All the As of dates are the ending date of each month (i.e. 2/29/2004, 3/31/2004).