# Thread: trying to obtain totals

1. Registered User
Join Date
Jun 2004
Posts
35

## 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. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
what does your original query look like... this sounds a little strange..

3. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
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:
Code:
`PartsOutstanding = Nz(QtyOrdered, 0) - Nz(QtyReceived,0)`
This would be for a record-by-record basis. In a report summary, you might have
Code:
`PartsOutstanding = Sum(Nz(QtyOrdered, 0)) - Sum(Nz(QtyReceived,0))`
You can leave RECD_DATE out of the equation altogether.

HTH

Sam

#### Posting Permissions

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