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

[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?

 

[Guest]

Use criteria in your totals query <> 0 and it not pull them.

 

[Guest]

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

 

[Guest]

Post the SQL of your query.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

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.

 

[Guest]

I work at Payson Fruit Growers in Payson, UT. We store products for you.

Thanks a bunch.

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

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?

 

[Guest]

Duane:

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

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

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?

 

[Guest]

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

Duane:

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

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

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?