having a sum value as part of criteria?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a simple inventory where I have ItemMaster and ItemDetails.
When An item is received it is written to the ItemDetails table updating a QTY field.
When an item is used it is also written to the ItemDetails table updating the QTY field with a negative number
so at the end of the day (I am hoping) Sum(QTY) will give me the qty I still have available. (I'm not sure diff between sum and dsum - that's another ?)

I would like to do a stock short report so
Select records from my saledetail table where qty is > then the Sum(QTY) from the itemdetails table (which gives me my available), but when I do this I get an error saying that the where clause cannot have aggregate function in it. I am hoping that there is someway around this - I have struggled to figure out how to handle this inventory and summing the qty field seemed best for this app. but I wasn't aware of the above issue. PLEASE someone out there, any help is appreciated.

HELP!!!
thx in advance.... Joan
 
I figured it out...I have Sum(sales parts) - Sum (invdetails) as the expression itsself and only show where the result is > 0, This gives me my results. tnx...
 
Back
Top