Running totals in a query

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

Guest

I am trying to build a query to only show the inventory of materials between a Minimum quantity and Maximum quantity but the inventory quantity can be a total of 2 or more records of the inventory in different locations. I tried to do it on the Report to no avail. But i am needing a way to sum the quantities of the same product (that can be in different locations) in the query so that i can compare the summed quantity to the Minimum and Maximum number and only show those that fall within the Minimum and Maximum number. I am new to ACCESS queries so please be gentle... THANKS IN ADVANCE!!!!
 
try...

Select [ProductId], Sum([Quantity]) as Stock, min
([MinQuantity]) as MinQty, max([MaxQuantity]) as MaxQty
from <yourtable>
Group by [ProductID]
Having Sum([Quantity]) >= min([MinQuantity]) and Sum
([Quantity]) <= max([MaxQuantity])

Everything inside the square brackets are field names.
Change them to suit
-----Original Message-----
I am trying to build a query to only show the inventory
Click to expand...
of materials between a Minimum quantity and Maximum
quantity but the inventory quantity can be a total of 2 or
more records of the inventory in different locations. I
tried to do it on the Report to no avail. But i am
needing a way to sum the quantities of the same product
(that can be in different locations) in the query so that
i can compare the summed quantity to the Minimum and
Maximum number and only show those that fall within the
Minimum and Maximum number. I am new to ACCESS queries so
please be gentle... THANKS IN ADVANCE!!!!
 
Back
Top