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

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Need ideas for creating a running sum table (Access97/2000)

    Author
    Topic
    #368337

    question 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

    Viewing 0 reply threads
    Author
    Replies
    • #576867

      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)

      • #576870

        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

        • #576876

          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]

        • #576877

          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.

          • #576890

            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

        • #576979

          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?

          • #577155

            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

            • #577164

              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.

            • #577221

              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!

    Viewing 0 reply threads
    Reply To: Reply #576979 in Need ideas for creating a running sum table (Access97/2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




    Cancel