Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Unanswered: trying to obtain totals

    I am not sure of the best way to do this and I'm hoping someone can help me. I have a database listing parts that we have ordered. In my parts table I have a column called QTY and a column called RECD_DATE. What I want to do is create a way to say the following:
    1. how many of part x have we ordered (I have gotten this total by doing a
    SUM[(QTY)] on the report from my original query.

    2. how many of part x have come in (in my query I put "is not null" in the RECD_DATE column)

    here's where it stumps me:

    3. total ordered minus qty received = number of outstanding parts. For the life of me I cannot figure out how to get it to do a sum of the parts when the RECD_DATE column is null. i.e. SUM([QTY]) when RECD_DATE is not null

    I'm not sure if the best way to do it is multiple queires or expressions on the report? Does that make any sense?

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    what does your original query look like... this sounds a little strange..
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    New York State
    I think I understand. You have a field QTY for QtyOrdered in a separate table, while the QTY field in the current table is really QtyReceived. Is that right?

    If that's so, what you want is something like this:
    PartsOutstanding = Nz(QtyOrdered, 0) - Nz(QtyReceived,0)
    This would be for a record-by-record basis. In a report summary, you might have
    PartsOutstanding = Sum(Nz(QtyOrdered, 0)) - Sum(Nz(QtyReceived,0))
    You can leave RECD_DATE out of the equation altogether.



Posting Permissions

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