Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <img src=/S/question.gif border=0 alt=question width=15 height=15> 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

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    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)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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]
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Francois

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    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!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •