ARGH! Basic query is confounding me !!

[TomH]

I have an inventory table with fields of item number, description, etc. One field is "BeginningInventory". I also have a table populated with line items of invoice sales, against which I wrote a query that gives me a sum of the number of sales, "SalesQuantity", by item number.

I want to write a query that will allow subtraction of an item's SalesQuantity from its BeginningInventory so that I can keep a current count. However, when not all items are sold in a particular period, those items with no sales don't appear in the original query. I have written the second query so that all items appear, but the SalesQuantity field is blank, and I cannot do the subtraction to get my quantity remaining. That subtraction results in another blank field on that item. So, I guess what I need is a way to write the original query such that all items appear (which I can do) AND if there are no sales, that value defaults to zero... or something like that.

I don't know why I can't figure this out... it should be easy. Thanks for any help!!!

 

[John Big Booty]

Perhaps this article will give you some pointers.

 

[TomH]

Thanks, but that article doesn't address my basic question of how I can subtract the sales volume for a particular item if that item had zero sales, since the query for that item reports back a blank field.

 

[Simon_MT]

Do a Left Join on the Sales transactions so all the Products appear on your query and then All the Sales Transactions. You may need to calculate Zero if there is no Sales Transaction.

Simon

 

[TomH]

Simon: That's my question... how do I calculate zero??

 

[TomH]

Figured it out...

RemainingQuantity: [QuantityonHand]-IIf([sumofquantity] Is Null,0,[sumofquantity])

So, if my [sumofquantity] field (the field that sums the sales) is NULL, this equation enters zero in this new field... otherwise it grabs my original amount. I then subtract the calculated value (either the original amount or the zero that replaced the null).

 

[John Big Booty]

The Nz() function may be of some use to you in this situation.

 

[TomH]

Thanks! I'll try that.