Displaying records with a zero value

[Anne]

I have an inventory report with displays what items we have in stock and the
quantity. The report contains a subreport (Inventory Transactions) where the
[UnitsOnHand], are report are calculated. This is the calculation.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The [QuantityOnHand] in the main report uses this as the control source
=[Inventory Transactions].Report!UnitsOnHand

If the quantity is zero I do not want that item to show on my report. How
do I eliminate the items that have zero on hand?

 

[Jeff Boyce]

Anne

Use a query to do that calculation. Use "<>0" in the Selection Criterion
under that (new) field.

Use the query as the source for your report.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Anne]

This is the query for the main report
SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.AccountID, Products.CategoryID,
Products.DepartmentID, Products.CatalogNumber, Products.UnitPrice,
Products.ReorderLevel, Products.LeadTime, Products.GST, Products.PST,
Products.AvgCost, Products.Notes, Products.Location, Products.DepartmentID,
Accounts.AccountNumber, Accounts.AccountName
FROM Accounts RIGHT JOIN Products ON Accounts.AccountID = Products.AccountID
WHERE (((Products.DepartmentID)=2))
ORDER BY Accounts.AccountNumber;

The Inventory Transactions subreport uses this calculation in the footer of
the report.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The information in this report is from the Inventory Transactions Table, no
query. Is this where I should write my query? I tried writing one and adding
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
as an expression in the query but it wouldn't run. I'm obviously missing
something.


--
Anne

Anne

Use a query to do that calculation. Use "<>0" in the Selection Criterion
under that (new) field.

Use the query as the source for your report.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an inventory report with displays what items we have in stock and
the
quantity. The report contains a subreport (Inventory Transactions) where
the
[UnitsOnHand], are report are calculated. This is the calculation.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The [QuantityOnHand] in the main report uses this as the control source
=[Inventory Transactions].Report!UnitsOnHand

If the quantity is zero I do not want that item to show on my report. How
do I eliminate the items that have zero on hand?

 

[Jeff Boyce]

It sounds like you're describing doing the calculation inside the report.
I'm not visualizing the raw data underneath.

Are you saying that one product can have many inventory transactions?

What query are you using for the subreport?

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is the query for the main report
SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.AccountID, Products.CategoryID,
Products.DepartmentID, Products.CatalogNumber, Products.UnitPrice,
Products.ReorderLevel, Products.LeadTime, Products.GST, Products.PST,
Products.AvgCost, Products.Notes, Products.Location,
Products.DepartmentID,
Accounts.AccountNumber, Accounts.AccountName
FROM Accounts RIGHT JOIN Products ON Accounts.AccountID =
Products.AccountID
WHERE (((Products.DepartmentID)=2))
ORDER BY Accounts.AccountNumber;

The Inventory Transactions subreport uses this calculation in the footer
of
the report.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The information in this report is from the Inventory Transactions Table,
no
query. Is this where I should write my query? I tried writing one and
adding
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
as an expression in the query but it wouldn't run. I'm obviously missing
something.


--
Anne

Anne

Use a query to do that calculation. Use "<>0" in the Selection Criterion
under that (new) field.

Use the query as the source for your report.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an inventory report with displays what items we have in stock and
the
quantity. The report contains a subreport (Inventory Transactions)
where
the
[UnitsOnHand], are report are calculated. This is the calculation.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The [QuantityOnHand] in the main report uses this as the control source
=[Inventory Transactions].Report!UnitsOnHand

If the quantity is zero I do not want that item to show on my report.
How
do I eliminate the items that have zero on hand?

 

[Anne]

Yes, currently the calculation occurs on the footer of the subreport
Inventory Transactions. There are many, perhaps hundreds of "transactions"
that are factored into the
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold])) This
Inventory Transactions subreport does not run on a query - just takes
information from the Inventory Transactions Table and then performs the
calculation on the report. The subreport links by Product ID to the main
report which references the [UnitsOnHand] for each Product ID to give
[QuantityOnHand] in the main report.


--
Anne

It sounds like you're describing doing the calculation inside the report.
I'm not visualizing the raw data underneath.

Are you saying that one product can have many inventory transactions?

What query are you using for the subreport?

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is the query for the main report
SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.AccountID, Products.CategoryID,
Products.DepartmentID, Products.CatalogNumber, Products.UnitPrice,
Products.ReorderLevel, Products.LeadTime, Products.GST, Products.PST,
Products.AvgCost, Products.Notes, Products.Location,
Products.DepartmentID,
Accounts.AccountNumber, Accounts.AccountName
FROM Accounts RIGHT JOIN Products ON Accounts.AccountID =
Products.AccountID
WHERE (((Products.DepartmentID)=2))
ORDER BY Accounts.AccountNumber;

The Inventory Transactions subreport uses this calculation in the footer
of
the report.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The information in this report is from the Inventory Transactions Table,
no
query. Is this where I should write my query? I tried writing one and
adding
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
as an expression in the query but it wouldn't run. I'm obviously missing
something.


--
Anne

Anne

Use a query to do that calculation. Use "<>0" in the Selection Criterion
under that (new) field.

Use the query as the source for your report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have an inventory report with displays what items we have in stock and
the
quantity. The report contains a subreport (Inventory Transactions)
where
the
[UnitsOnHand], are report are calculated. This is the calculation.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The [QuantityOnHand] in the main report uses this as the control source
=[Inventory Transactions].Report!UnitsOnHand

If the quantity is zero I do not want that item to show on my report.
How
do I eliminate the items that have zero on hand?

 

[Jeff Boyce]

I'm confused. Are you using both a footer and a subreport?

If you have a main form based on the "parent" of the [Inventory
Transaction], then why base the subreport on ALL of the Inventory
Tranasactions in the table?

Maybe you and I are using the same terminology but with different meanings.

My understanding is that a report in Access displays data from either a
query or a table (query is preferred). When I embed a subreport in that
main report, I first create a new report, base it on data (query or table),
then embed it into the main report. Access asks for the field(s) that the
two share, to ensure that the records (the "many" side) displayed in the
subreport "belong" to the record displayed in the main report.

Is this how you understand these terms?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes, currently the calculation occurs on the footer of the subreport
Inventory Transactions. There are many, perhaps hundreds of
"transactions"
that are factored into the
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold])) This
Inventory Transactions subreport does not run on a query - just takes
information from the Inventory Transactions Table and then performs the
calculation on the report. The subreport links by Product ID to the main
report which references the [UnitsOnHand] for each Product ID to give
[QuantityOnHand] in the main report.


--
Anne

It sounds like you're describing doing the calculation inside the report.
I'm not visualizing the raw data underneath.

Are you saying that one product can have many inventory transactions?

What query are you using for the subreport?

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is the query for the main report
SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.AccountID, Products.CategoryID,
Products.DepartmentID, Products.CatalogNumber, Products.UnitPrice,
Products.ReorderLevel, Products.LeadTime, Products.GST, Products.PST,
Products.AvgCost, Products.Notes, Products.Location,
Products.DepartmentID,
Accounts.AccountNumber, Accounts.AccountName
FROM Accounts RIGHT JOIN Products ON Accounts.AccountID =
Products.AccountID
WHERE (((Products.DepartmentID)=2))
ORDER BY Accounts.AccountNumber;

The Inventory Transactions subreport uses this calculation in the
footer
of
the report.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The information in this report is from the Inventory Transactions
Table,
no
query. Is this where I should write my query? I tried writing one and
adding
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
as an expression in the query but it wouldn't run. I'm obviously
missing
something.


--
Anne


:

Anne

Use a query to do that calculation. Use "<>0" in the Selection
Criterion
under that (new) field.

Use the query as the source for your report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have an inventory report with displays what items we have in stock
and
the
quantity. The report contains a subreport (Inventory Transactions)
where
the
[UnitsOnHand], are report are calculated. This is the calculation.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The [QuantityOnHand] in the main report uses this as the control
source
=[Inventory Transactions].Report!UnitsOnHand

If the quantity is zero I do not want that item to show on my
report.
How
do I eliminate the items that have zero on hand?

 

[Anne]

That is how I created my subreport. The subreport has the calculation in the
footer of that report.
I have been plugging away all day and I finally got a query that produced
the report without the "0" quantity items.
--
Anne

I'm confused. Are you using both a footer and a subreport?

If you have a main form based on the "parent" of the [Inventory
Transaction], then why base the subreport on ALL of the Inventory
Tranasactions in the table?

Maybe you and I are using the same terminology but with different meanings.

My understanding is that a report in Access displays data from either a
query or a table (query is preferred). When I embed a subreport in that
main report, I first create a new report, base it on data (query or table),
then embed it into the main report. Access asks for the field(s) that the
two share, to ensure that the records (the "many" side) displayed in the
subreport "belong" to the record displayed in the main report.

Is this how you understand these terms?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes, currently the calculation occurs on the footer of the subreport
Inventory Transactions. There are many, perhaps hundreds of
"transactions"
that are factored into the
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold])) This
Inventory Transactions subreport does not run on a query - just takes
information from the Inventory Transactions Table and then performs the
calculation on the report. The subreport links by Product ID to the main
report which references the [UnitsOnHand] for each Product ID to give
[QuantityOnHand] in the main report.


--
Anne

It sounds like you're describing doing the calculation inside the report.
I'm not visualizing the raw data underneath.

Are you saying that one product can have many inventory transactions?

What query are you using for the subreport?

Regards

Jeff Boyce
Microsoft Office/Access MVP


This is the query for the main report
SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.AccountID, Products.CategoryID,
Products.DepartmentID, Products.CatalogNumber, Products.UnitPrice,
Products.ReorderLevel, Products.LeadTime, Products.GST, Products.PST,
Products.AvgCost, Products.Notes, Products.Location,
Products.DepartmentID,
Accounts.AccountNumber, Accounts.AccountName
FROM Accounts RIGHT JOIN Products ON Accounts.AccountID =
Products.AccountID
WHERE (((Products.DepartmentID)=2))
ORDER BY Accounts.AccountNumber;

The Inventory Transactions subreport uses this calculation in the
footer
of
the report.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The information in this report is from the Inventory Transactions
Table,
no
query. Is this where I should write my query? I tried writing one and
adding
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
as an expression in the query but it wouldn't run. I'm obviously
missing
something.


--
Anne


:

Anne

Use a query to do that calculation. Use "<>0" in the Selection
Criterion
under that (new) field.

Use the query as the source for your report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have an inventory report with displays what items we have in stock
and
the
quantity. The report contains a subreport (Inventory Transactions)
where
the
[UnitsOnHand], are report are calculated. This is the calculation.
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
The [QuantityOnHand] in the main report uses this as the control
source
=[Inventory Transactions].Report!UnitsOnHand

If the quantity is zero I do not want that item to show on my
report.
How
do I eliminate the items that have zero on hand?