How do I filter a query to eliminate balances less than 0 (zero)?

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

Guest

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!!
 
What's not working with your setup? What are you seeing in the report that
you don't want to see?
 
Ken Snell said:
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 said:
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!!
Click to expand...
Click to expand...
 
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 said:
Ken Snell said:
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 said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Ken Snell said:
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>

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!!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
:

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>

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!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top