Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    running balances (Access 2000 SP3)

    I'm trying to get a running outstanding balance by item in a report where the item is repeated several times due to the fact that it's in more than one product order. This is how we'd
    like it:

    Order item qty on hand short
    12345 101 6 100 0
    12345 102 9 27 0

    12346 101 6 98 0
    12346 102 25 18 -7

    I can't figure out how to do this and have searched in vain for an answer. A running total isn't doing it. <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Carpy Diem, it&#39;s .

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: running balances (Access 2000 SP3)

    Do you want a running balance by item? Is there a date field to keep the transactions in order or is it a ID (autonumber field)?
    The third line on your example should probably 94 as the on hand figure.
    To get the on hand figure I would use a DSum based upon the date or ID field to get this figure for each line. This will take a while to run this report if there are a lot of items for the report.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running balances (Access 2000 SP3)

    yes, I know that the third line should have been 94, but right in the middle of my typing I got called away and then when I got back I just went typing away like I knew what I was doing....

    What we're trying to do is show, on the report, that the inventory is going down for the item as the orders are displayed. I'm trying to use DSUM, and I would consider the item to be the key field, but try as I might, I can create a running total, but not a running calculation. I'll keep trying but if anyone has anything else to suggest, I'd be eternally grateful. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Carpy Diem, it&#39;s .

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: running balances (Access 2000 SP3)

    What I had alluded to in my earlier post was to use an ID field (or for that matter a date/time field) as well as the item field. You see, it's not sufficient to just use the item field, as you will always get the same answer.
    Can you let us know the structure of the table where these transactions are.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running balances (Access 2000 SP3)

    I looked at the underlying query and table for this report and there is no key field. We have multiple dates, multiple order numbers, multiple customer numbers, multiple items, etc. So I guess DSUM() won't work here. I'm thinking of tying in a subreport, but that's about all I can do here, I guess. Unless I add a field to the underlying table that calculates this via an update query <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> ....

    Thanks, Patt! I'll let everyone know how it goes.
    Carpy Diem, it&#39;s .

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: running balances (Access 2000 SP3)

    Why don't you introduce an autonumber field in this table, that field can then be used for the DSUM.

Posting Permissions

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