Sum Question

T

Tom

I have a list of customers and next to their name is numerous columns with
amounts in them. Each column represents a new month. Given a starting month,
which is different for each customer & is entered in another field, I want a
sum starting in that month plus the following 8 months. I have no idea where
to begin. Please help.
 
A

Anne

Somehow what you want to total does not make sense. Lets assume a customer
started in 01/2007 and lets say now it is any day in Jan 2008, you still only
want January 2007 plus Jun, Jul, Aug, Sep, Oct, Nov, Dec 2007? Please let us
know what exactly you are trying to total.
 
K

KARL DEWEY

You need to change from a spreadsheet layout to a database structure like
this --
Name TransDate Amount
Joe 1/1/07 1.32
Bill 1/23/07 14.65
Jim 1/31/07 .47
Bill 2/14/07 5.41
Jim 2/14/07 6.47
Joe 3/01/07 11.21

To migrate your data to the new table use an append query.

Then to sum use query like this ---
Tom_1 ---
SELECT [YourTable].Name, Min([YourTable].[TransDate]) AS [MinOfTransDate]
FROM [YourTable]
GROUP BY [YourTable].Name;

SELECT [YourTable].Name, Sum([YourTable].Amount) AS SumOfAmount
FROM [YourTable], Tom_1
WHERE ((([YourTable].[TransDate]) Between [MinOfTransDate] And
DateAdd("m",8,[MinOfTransDate])))
GROUP BY [YourTable].Name;
 
Top