Need ideas for creating a running sum table (Access97/2000)

Topic

New Reply

I’m going to be getting a text file every month to import. The file contains serial number, curent meter ,current meter date, previous meter, and previous meter date.
The meters are copy counts on office machines.
I need some ideas on prossessing the data into a table that will the total counts for 6 months for each serial number.
Ie in month 7 month 1 falls off, in month 8 month 2 falls off etc….
The 6 month meter reads will then be used for some processing later.
If anyone has any ideas let me know.
I can clarify if needed.
Thanks,
Scott

Reply | Quote

Replies

I’d have a MachineUsage table made up of these fields (and how they are calculated from import data:

MachineID
ReadingDate
Usage (current Meter – PreviousMeter)
DaysUsage (Current Date – PreviousDate)

Reply | Quote

Mark,
How would that show a 6 month total(sum) for each machine?
It seems to me that I would somehow have to keep 6 months of readings for each machine in the table and then delete the earliest reading when the next month rolls around(once I get at least 7 months worth of data)
But not sure how to do this.
Thanks,
Scott

Reply | Quote

Use a dsum in a query
Running Total: DSum(“Usage”;”table1″;”[ReadingDate] between #” & [ReadingDate] & “# and #” & DateAdd(“m”;-5;[ReadingDate]) & “# and [MachineID] = ‘” & [MachineID] & “‘”

If your Machineid is numeric change to
Running Total: DSum(“Usage”;”table1″;”[ReadingDate] between #” & [ReadingDate] & “# and #” & DateAdd(“m”;-5;[ReadingDate]) & “# and [MachineID] = ” & [MachineID]

Reply | Quote

Why not just keep the data (instead of deleting the earliest) and calculate the 6-month sum when you need it. That gives you two advantages: you don’t have to figure out how to *store* the running sum, and you can always go back and recalculate a previous value when they decide they have to see it.

Reply | Quote

Charlotte,
The data will be for 12,000 units plus.
I didn’t really want to have a table that big, but I will give it some thought.
Any other ideas welcome
Thanks, and thanks Francois

Reply | Quote

You can keep the monthly records indefinitely! You can then use a Totals query to sum the monthly usages, specifying a date range within the past 6 months; or for that matter, ANY date range you want! Suppose your boss asks for a yearly total. Do you really want to tell him/her that you can only go back 6 months, and the prior data is lost?

Reply | Quote

OK, OK…
You guys talked me into it, I’ll keep it all
I imported the first run of data and then added some dummy data to get me about 8 months worth to test with.
The fields again are:
serial_number, curent_meter ,current_meter_date, previous_meter, and previous_meter_date.
I didn’t do any processing on the import, just have the raw data.
I don’t think I need the previous data fields but kept it for now.
The dates range from 01/18/2002 to 08/28/2002 with a meter read in each of the months.
I don’t need a running totals query, I just need to subtract the current_meter for the latest date from the current_meter 6 months back.
I’ve been playing with some queries to pick out those 2 values to subtract but can’t seem to figure it out.
Can you some ideas?
Thanks,
Scott

Reply | Quote

If you want, send me your table and I write you a function to return meterstand 6 months ago.
May e-mail is in my profile.

Reply | Quote

Since you are have current and previous meter reading, I’d just do a calculation to get Usage for each month, the sum the Monthly Usages. Trying to take the current reading, then subtract the reading from 6 months ago is a pain. Furthermore, you will probably have a situation where the meter gets reset (like when it is replaced), which will completely screw you up!

Reply | Quote