How do I exclude records that will sum to zero in a report?

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

Guest

I have a report based on a query. I would like to exclude the items in the
report that sum to zero in order to shorten my report and remove those items
that have been "used." How do I exclude the detail records that will sum to
zero in my report?
 
Karl:

My query includes a column where quantity * weight is placed. This always
has a number in it. What I'm trying to do is if the summation of this column
for a product is zero, exclude that product from the report. I've tried <>0,
but I still get zero result products sent to the report.

samiam
 
You must create/calculate your totals in the report's record source. Make a
query similar to your report's record source:
SELECT Product
FROM tblNoNameGiven
GROUP BY Product
HAVING Sum(Quantity * Weight)<>0;

Add this query to your report's record source query and join the Product
fields.
 
Here is my SQL query:

SELECT tblInventory.CustomerID, tblCustomer.ShortName, tblCustomer.FullName,
IIf([TransTypeID]=1,[RecDate],IIf([TransTypeID]=2,[BOLDate],IIf([TransTypeID]=3,[MoveDate],[ADJDate])))
AS TransDate, tblInventory.ID,
IIf([TransTypeID]=1,"Receiving",IIf([TransTypeID]=2,"BOL",IIf([TransTypeID]=3,"Move","Adjustment")))
AS TransTypeName, tblAisle.Name AS Aisle, tblInventory.CustPO,
tblInventory.Quantity, tblProduct.uID, tblProduct.ProductCode,
tblProduct.Description, tblProduct.NWeight, tblInventory.DayCode,
([QUANTITY]*[NWEIGHT]) AS NETWEIGHT
FROM tblCustomer INNER JOIN (tblAisle RIGHT JOIN ((tblMoveInv RIGHT JOIN
(tblInvAdj RIGHT JOIN (tblReceiving RIGHT JOIN (tblBOL RIGHT JOIN
tblInventory ON tblBOL.BOLNo = tblInventory.ID) ON tblReceiving.uID =
tblInventory.ID) ON tblInvAdj.uID = tblInventory.ID) ON tblMoveInv.uID =
tblInventory.ID) INNER JOIN tblProduct ON tblInventory.ProductID =
tblProduct.uID) ON tblAisle.uID = tblInventory.AisleID) ON tblCustomer.uID =
tblInventory.CustomerID
WHERE (((tblCustomer.ShortName) Like "NESTLE"))
ORDER BY tblInventory.CustomerID, tblInventory.CustPO,
tblProduct.ProductCode, tblInventory.MoveOrder;

I appreciate your assistance.
samiam
 
I would create a totals query like:

SELECT tblInventory.ID
FROM tblCustomer INNER JOIN tblInventory ON tblCustomer.uID =
tblInventory.CustomerID
WHERE tblCustomer.ShortName Like "NESTLE"
GROUP BY tblInventory.ID
HAVING Sum(Quantity) <> 0;

Then add this query to your report's query and join the ID fields. This
should limit the report to IDs where the quantity is not 0.

Apparently you work for one of our suppliers (I work for Nestle ;-)

--
Duane Hookom
Microsoft Access MVP


samiam said:
Here is my SQL query:

SELECT tblInventory.CustomerID, tblCustomer.ShortName, tblCustomer.FullName,
IIf([TransTypeID]=1,[RecDate],IIf([TransTypeID]=2,[BOLDate],IIf([TransTypeID]=3,[MoveDate],[ADJDate])))
AS TransDate, tblInventory.ID,
IIf([TransTypeID]=1,"Receiving",IIf([TransTypeID]=2,"BOL",IIf([TransTypeID]=3,"Move","Adjustment")))
AS TransTypeName, tblAisle.Name AS Aisle, tblInventory.CustPO,
tblInventory.Quantity, tblProduct.uID, tblProduct.ProductCode,
tblProduct.Description, tblProduct.NWeight, tblInventory.DayCode,
([QUANTITY]*[NWEIGHT]) AS NETWEIGHT
FROM tblCustomer INNER JOIN (tblAisle RIGHT JOIN ((tblMoveInv RIGHT JOIN
(tblInvAdj RIGHT JOIN (tblReceiving RIGHT JOIN (tblBOL RIGHT JOIN
tblInventory ON tblBOL.BOLNo = tblInventory.ID) ON tblReceiving.uID =
tblInventory.ID) ON tblInvAdj.uID = tblInventory.ID) ON tblMoveInv.uID =
tblInventory.ID) INNER JOIN tblProduct ON tblInventory.ProductID =
tblProduct.uID) ON tblAisle.uID = tblInventory.AisleID) ON tblCustomer.uID =
tblInventory.CustomerID
WHERE (((tblCustomer.ShortName) Like "NESTLE"))
ORDER BY tblInventory.CustomerID, tblInventory.CustPO,
tblProduct.ProductCode, tblInventory.MoveOrder;

I appreciate your assistance.
samiam

KARL DEWEY said:
Post the SQL of your query.
Click to expand...
Click to expand...
 
I work at Payson Fruit Growers in Payson, UT. We store products for you.

Thanks a bunch.

samiam

Duane Hookom said:
I would create a totals query like:

SELECT tblInventory.ID
FROM tblCustomer INNER JOIN tblInventory ON tblCustomer.uID =
tblInventory.CustomerID
WHERE tblCustomer.ShortName Like "NESTLE"
GROUP BY tblInventory.ID
HAVING Sum(Quantity) <> 0;

Then add this query to your report's query and join the ID fields. This
should limit the report to IDs where the quantity is not 0.

Apparently you work for one of our suppliers (I work for Nestle ;-)

--
Duane Hookom
Microsoft Access MVP


samiam said:
Here is my SQL query:

SELECT tblInventory.CustomerID, tblCustomer.ShortName, tblCustomer.FullName,
IIf([TransTypeID]=1,[RecDate],IIf([TransTypeID]=2,[BOLDate],IIf([TransTypeID]=3,[MoveDate],[ADJDate])))
AS TransDate, tblInventory.ID,
IIf([TransTypeID]=1,"Receiving",IIf([TransTypeID]=2,"BOL",IIf([TransTypeID]=3,"Move","Adjustment")))
AS TransTypeName, tblAisle.Name AS Aisle, tblInventory.CustPO,
tblInventory.Quantity, tblProduct.uID, tblProduct.ProductCode,
tblProduct.Description, tblProduct.NWeight, tblInventory.DayCode,
([QUANTITY]*[NWEIGHT]) AS NETWEIGHT
FROM tblCustomer INNER JOIN (tblAisle RIGHT JOIN ((tblMoveInv RIGHT JOIN
(tblInvAdj RIGHT JOIN (tblReceiving RIGHT JOIN (tblBOL RIGHT JOIN
tblInventory ON tblBOL.BOLNo = tblInventory.ID) ON tblReceiving.uID =
tblInventory.ID) ON tblInvAdj.uID = tblInventory.ID) ON tblMoveInv.uID =
tblInventory.ID) INNER JOIN tblProduct ON tblInventory.ProductID =
tblProduct.uID) ON tblAisle.uID = tblInventory.AisleID) ON tblCustomer.uID =
tblInventory.CustomerID
WHERE (((tblCustomer.ShortName) Like "NESTLE"))
ORDER BY tblInventory.CustomerID, tblInventory.CustPO,
tblProduct.ProductCode, tblInventory.MoveOrder;

I appreciate your assistance.
samiam

KARL DEWEY said:
Post the SQL of your query.
--
KARL DEWEY
Build a little - Test a little


:

Karl:

My query includes a column where quantity * weight is placed. This always
has a number in it. What I'm trying to do is if the summation of this column
for a product is zero, exclude that product from the report. I've tried <>0,
but I still get zero result products sent to the report.

samiam

:

Use criteria in your totals query <> 0 and it not pull them.
--
KARL DEWEY
Build a little - Test a little


:

I have a report based on a query. I would like to exclude the items in the
report that sum to zero in order to shorten my report and remove those items
that have been "used." How do I exclude the detail records that will sum to
zero in my report?
Click to expand...
Click to expand...
Click to expand...
 
Duane:

Should the HAVING clause include quantity times new weight, or just quantity?

samiam

Duane Hookom said:
I would create a totals query like:

SELECT tblInventory.ID
FROM tblCustomer INNER JOIN tblInventory ON tblCustomer.uID =
tblInventory.CustomerID
WHERE tblCustomer.ShortName Like "NESTLE"
GROUP BY tblInventory.ID
HAVING Sum(Quantity) <> 0;

Then add this query to your report's query and join the ID fields. This
should limit the report to IDs where the quantity is not 0.

Apparently you work for one of our suppliers (I work for Nestle ;-)

--
Duane Hookom
Microsoft Access MVP


samiam said:
Here is my SQL query:

SELECT tblInventory.CustomerID, tblCustomer.ShortName, tblCustomer.FullName,
IIf([TransTypeID]=1,[RecDate],IIf([TransTypeID]=2,[BOLDate],IIf([TransTypeID]=3,[MoveDate],[ADJDate])))
AS TransDate, tblInventory.ID,
IIf([TransTypeID]=1,"Receiving",IIf([TransTypeID]=2,"BOL",IIf([TransTypeID]=3,"Move","Adjustment")))
AS TransTypeName, tblAisle.Name AS Aisle, tblInventory.CustPO,
tblInventory.Quantity, tblProduct.uID, tblProduct.ProductCode,
tblProduct.Description, tblProduct.NWeight, tblInventory.DayCode,
([QUANTITY]*[NWEIGHT]) AS NETWEIGHT
FROM tblCustomer INNER JOIN (tblAisle RIGHT JOIN ((tblMoveInv RIGHT JOIN
(tblInvAdj RIGHT JOIN (tblReceiving RIGHT JOIN (tblBOL RIGHT JOIN
tblInventory ON tblBOL.BOLNo = tblInventory.ID) ON tblReceiving.uID =
tblInventory.ID) ON tblInvAdj.uID = tblInventory.ID) ON tblMoveInv.uID =
tblInventory.ID) INNER JOIN tblProduct ON tblInventory.ProductID =
tblProduct.uID) ON tblAisle.uID = tblInventory.AisleID) ON tblCustomer.uID =
tblInventory.CustomerID
WHERE (((tblCustomer.ShortName) Like "NESTLE"))
ORDER BY tblInventory.CustomerID, tblInventory.CustPO,
tblProduct.ProductCode, tblInventory.MoveOrder;

I appreciate your assistance.
samiam

KARL DEWEY said:
Post the SQL of your query.
--
KARL DEWEY
Build a little - Test a little


:

Karl:

My query includes a column where quantity * weight is placed. This always
has a number in it. What I'm trying to do is if the summation of this column
for a product is zero, exclude that product from the report. I've tried <>0,
but I still get zero result products sent to the report.

samiam

:

Use criteria in your totals query <> 0 and it not pull them.
--
KARL DEWEY
Build a little - Test a little


:

I have a report based on a query. I would like to exclude the items in the
report that sum to zero in order to shorten my report and remove those items
that have been "used." How do I exclude the detail records that will sum to
zero in my report?
Click to expand...
Click to expand...
Click to expand...
 
I don't know your data so you need to decide if the weight is significant. I
assumed the quantity was really the only required value.

--
Duane Hookom
Microsoft Access MVP


samiam said:
Duane:

Should the HAVING clause include quantity times new weight, or just quantity?

samiam

Duane Hookom said:
I would create a totals query like:

SELECT tblInventory.ID
FROM tblCustomer INNER JOIN tblInventory ON tblCustomer.uID =
tblInventory.CustomerID
WHERE tblCustomer.ShortName Like "NESTLE"
GROUP BY tblInventory.ID
HAVING Sum(Quantity) <> 0;

Then add this query to your report's query and join the ID fields. This
should limit the report to IDs where the quantity is not 0.

Apparently you work for one of our suppliers (I work for Nestle ;-)

--
Duane Hookom
Microsoft Access MVP


samiam said:
Here is my SQL query:

SELECT tblInventory.CustomerID, tblCustomer.ShortName, tblCustomer.FullName,
IIf([TransTypeID]=1,[RecDate],IIf([TransTypeID]=2,[BOLDate],IIf([TransTypeID]=3,[MoveDate],[ADJDate])))
AS TransDate, tblInventory.ID,
IIf([TransTypeID]=1,"Receiving",IIf([TransTypeID]=2,"BOL",IIf([TransTypeID]=3,"Move","Adjustment")))
AS TransTypeName, tblAisle.Name AS Aisle, tblInventory.CustPO,
tblInventory.Quantity, tblProduct.uID, tblProduct.ProductCode,
tblProduct.Description, tblProduct.NWeight, tblInventory.DayCode,
([QUANTITY]*[NWEIGHT]) AS NETWEIGHT
FROM tblCustomer INNER JOIN (tblAisle RIGHT JOIN ((tblMoveInv RIGHT JOIN
(tblInvAdj RIGHT JOIN (tblReceiving RIGHT JOIN (tblBOL RIGHT JOIN
tblInventory ON tblBOL.BOLNo = tblInventory.ID) ON tblReceiving.uID =
tblInventory.ID) ON tblInvAdj.uID = tblInventory.ID) ON tblMoveInv.uID =
tblInventory.ID) INNER JOIN tblProduct ON tblInventory.ProductID =
tblProduct.uID) ON tblAisle.uID = tblInventory.AisleID) ON tblCustomer.uID =
tblInventory.CustomerID
WHERE (((tblCustomer.ShortName) Like "NESTLE"))
ORDER BY tblInventory.CustomerID, tblInventory.CustPO,
tblProduct.ProductCode, tblInventory.MoveOrder;

I appreciate your assistance.
samiam

:

Post the SQL of your query.
--
KARL DEWEY
Build a little - Test a little


:

Karl:

My query includes a column where quantity * weight is placed. This always
has a number in it. What I'm trying to do is if the summation of this column
for a product is zero, exclude that product from the report. I've tried <>0,
but I still get zero result products sent to the report.

samiam

:

Use criteria in your totals query <> 0 and it not pull them.
--
KARL DEWEY
Build a little - Test a little


:

I have a report based on a query. I would like to exclude the items in the
report that sum to zero in order to shorten my report and remove those items
that have been "used." How do I exclude the detail records that will sum to
zero in my report?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top