Try this SQL and see what you get:
SELECT [Container Details].ItemID, [Container Details].LotID, Sum([Container
Details].OriginalQty) AS SumOfOriginalQty, Sum([Order Details].UnitsOrdered)
AS SumOfUnitsOrdered, Sum(Returns.QuantityReturned) AS
SumOfQuantityReturned,
Sum(Nz([OriginalQty],0)-Nz([UnitsOrdered],0)+Nz([QuantityReturned],0)) AS
Available
FROM ([Container Details] LEFT JOIN [Order Details] ON [Container
Details].ItemID = [Order Details].ItemID) LEFT JOIN Returns ON [Container
Details].ItemID = Returns.ItemID
WHERE [Container Details].ItemID)=[Enter Item#]
GROUP BY [Container Details].ItemID, [Container Details].LotID
HAVING
(Sum(Nz([OriginalQty],0)-Nz([UnitsOrdered],0)+Nz([QuantityReturned],0)))>0
ORDER BY [Container Details].ItemID, [Container Details].LotID;
--
Ken Snell
<MS ACCESS MVP>
"RaisinLady" <(E-Mail Removed)> wrote in message
news:F487B025-DAC2-4305-9AC2-(E-Mail Removed)...
> SELECT [Container Details].ItemID, [Container Details].LotID,
> Sum([Container
> Details].OriginalQty) AS SumOfOriginalQty, Sum([Order
> Details].UnitsOrdered)
> AS SumOfUnitsOrdered, Sum(Returns.QuantityReturned) AS
> SumOfQuantityReturned,
> Sum([OriginalQty]-[UnitsOrdered]+nz([QuantityReturned])) AS Available
> FROM ([Container Details] LEFT JOIN [Order Details] ON [Container
> Details].ItemID = [Order Details].ItemID) LEFT JOIN Returns ON [Container
> Details].ItemID = Returns.ItemID
> GROUP BY [Container Details].ItemID, [Container Details].LotID
> HAVING ((([Container Details].ItemID)=[Enter Item#]) AND
> ((Sum([OriginalQty]-[UnitsOrdered]+nz([QuantityReturned])))>0))
> ORDER BY [Container Details].ItemID, [Container Details].LotID;
>
> When I run the report every lot code that we have ever received shows on
> the
> report. What I want to do is filter it so that only the lot codes that
> have
> remaining product left will show in the report.
>
> "Ken Snell [MVP]" wrote:
>
>> But what is not working? Are you seeing all inventory items? Are you
>> seeing
>> some with an "empty" Sum value?
>>
>> Post the SQL statement of the query where you put the ">0" criterion
>> under
>> the Sum field and let's see what you have.
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>> "RaisinLady" <(E-Mail Removed)> wrote in message
>> news:E1C9572D-43C8-4B52-BA07-(E-Mail Removed)...
>> >
>> >
>> > "Ken Snell [MVP]" wrote:
>> >
>> >> What's not working with your setup? What are you seeing in the report
>> >> that
>> >> you don't want to see?
>> >>
>> >> Hi Ken, My report is working fine but it has gotten so big over the
>> >> years
>> >> with all the inventory that we have had that I want to try and trim it
>> >> down to just show me the products that have an available balance
>> >> instead
>> >> of all products.
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >>
>> >> "RaisinLady" <(E-Mail Removed)> wrote in message
>> >> news:E3AAF21D-8AD9-4E98-B782-(E-Mail Removed)...
>> >> >I want the report to only show the balance of an item that has more
>> >> >than
>> >> >0.
>> >> >I
>> >> > have tried putting >0 in the column where it sums everything up
>> >> > using
>> >> > this
>> >> > expression: Available:
>> >> > Sum([OriginalQty]-[UnitsOrdered]+nz([QuantityReturned])) under that
>> >> > in
>> >> > the
>> >> > criteria column is where I have tried entering >0 so that only the
>> >> > records
>> >> > of
>> >> > lots that have a balance over zero will appear.
>> >> > Thank you in advance for your help!!
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
|
Similar Threads
