Query Criteria

[Scott Viney]

Morning All,

Iam trying to write a query for determining if certain stock needs ordering.
Im pretty sure I have all the data I need in the query. But I dont know how
to create the criteria?

I have two calculated columns for StockOnHand and StockOnOrder. I also have
a StockReorderLevel column from the products table.

How do I create the criteria in the query so that it only lists items that
less than or equal to the StockReorderLevel?

Thanks for the help,
Scott V

 

[John Spencer]

I would guess that you need to add StockOnHand and StockOnOrder together and
then compare that to StockReorderLevel.

SOMETHING like
Field: CalculationForStockOnHand + CalculationStockOnOrder
Criteria: <StockReorderLevel

You will need to do the calculations again in this new column AND make sure
you don't return a null value for either calculation. Probably by using the
NZ function to return zero if the calculation returns a null value.

 

[Guest]

I am assuming what you want is to return rows where StockOnHand +
StockOnOrder is less than or equal to StockReorderLevel. If this is correct,
put the following in the Critera row of the StockReorderLevel in the query
builder:

= [StockOnHand + [StockOnOrder]

 

[Scott Viney]

Afternoon John,

If I create another field in my
SumOfOnOrderOnHand: [UnitsOnOrder]+[UnitsOnHand]
and the criteria on this field to
< [ProductReorderLevel]

When I run the query Iam asked for the parameter values for [UnitsOnOrder] &
[UnitsOnHand] which are both calculated fields as well.

What am I missing here?

Scott V

 

[Scott Viney]

Hiya Klatuu,

I get the same happening as I have stated above if I try it your way as
well...

What am I doing wrong here?

Scott V

I am assuming what you want is to return rows where StockOnHand +
StockOnOrder is less than or equal to StockReorderLevel. If this is
correct,
put the following in the Critera row of the StockReorderLevel in the query
builder:

= [StockOnHand + [StockOnOrder]

Morning All,

Iam trying to write a query for determining if certain stock needs
ordering.
Im pretty sure I have all the data I need in the query. But I dont know
how
to create the criteria?

I have two calculated columns for StockOnHand and StockOnOrder. I also
have
a StockReorderLevel column from the products table.

How do I create the criteria in the query so that it only lists items
that
less than or equal to the StockReorderLevel?

Thanks for the help,
Scott V

 

[Scott Viney]

Below is the sql for the query without adding the criteria. This works.

SELECT tblProducts.ProductID, tblBrand.BrandName,
tblProducts.ProductDescription, tblPresentation.PresentationType,
tblProducts.ProductReorderLevel, tblProducts.ProductLeadTime,
qryInventoryTransactionsSummary.SumOfInventoryUnitsOrdered,
qryInventoryTransactionsSummary.SumOfInventoryUnitsReceived,
qryInventoryTransactionsSummary.SumOfInventoryUnitsSold,
qryInventoryTransactionsSummary.SumOfInventoryUnitsShrinkage,
[SumOfInventoryUnitsOrdered]-[SumOfInventoryUnitsReceived] AS UnitsOnOrder,
[SumOfInventoryUnitsReceived]-[SumOfInventoryUnitsSold]-[SumOfInventoryUnitsShrinkage]
AS UnitsOnHand, [UnitsOnOrder]+[UnitsOnHand] AS SumOfOnOrderOnHand
FROM tblPresentation INNER JOIN (tblBrand INNER JOIN (tblProducts INNER JOIN
qryInventoryTransactionsSummary ON tblProducts.ProductID =
qryInventoryTransactionsSummary.ProductID) ON tblBrand.BrandID =
tblProducts.BrandID) ON tblPresentation.PresentationID =
tblProducts.PresentationID
ORDER BY tblBrand.BrandName, tblProducts.ProductDescription;

 

[John Spencer]

As I mentioned in the first post, you have to RE-Enter the calculations in
the new columns. That would be


I think the following MAY work.

SELECT tblProducts.ProductID, tblBrand.BrandName,
tblProducts.ProductDescription, tblPresentation.PresentationType,
tblProducts.ProductReorderLevel, tblProducts.ProductLeadTime,
qryInventoryTransactionsSummary.SumOfInventoryUnitsOrdered,
qryInventoryTransactionsSummary.SumOfInventoryUnitsReceived,
qryInventoryTransactionsSummary.SumOfInventoryUnitsSold,
qryInventoryTransactionsSummary.SumOfInventoryUnitsShrinkage,
[SumOfInventoryUnitsOrdered]-[SumOfInventoryUnitsReceived] AS UnitsOnOrder,
[SumOfInventoryUnitsReceived]-[SumOfInventoryUnitsSold]-[SumOfInventoryUnitsShrinkage]
AS UnitsOnHand,
[SumOfInventoryUnitsOrdered]-[SumOfInventoryUnitsReceived] +
[SumOfInventoryUnitsReceived]-[SumOfInventoryUnitsSold]-[SumOfInventoryUnitsShrinkage]
AS SumOfOnOrderOnHand
FROM tblPresentation INNER JOIN (tblBrand INNER JOIN (tblProducts INNER JOIN
qryInventoryTransactionsSummary ON tblProducts.ProductID =
qryInventoryTransactionsSummary.ProductID) ON tblBrand.BrandID =
tblProducts.BrandID) ON tblPresentation.PresentationID =
tblProducts.PresentationID
WHERE
[SumOfInventoryUnitsOrdered]-[SumOfInventoryUnitsSold]-[SumOfInventoryUnitsShrinkage]

tblProducts.StockReorderLevel

ORDER BY tblBrand.BrandName, tblProducts.ProductDescription;

Below is the sql for the query without adding the criteria. This works.

SELECT tblProducts.ProductID, tblBrand.BrandName,
tblProducts.ProductDescription, tblPresentation.PresentationType,
tblProducts.ProductReorderLevel, tblProducts.ProductLeadTime,
qryInventoryTransactionsSummary.SumOfInventoryUnitsOrdered,
qryInventoryTransactionsSummary.SumOfInventoryUnitsReceived,
qryInventoryTransactionsSummary.SumOfInventoryUnitsSold,
qryInventoryTransactionsSummary.SumOfInventoryUnitsShrinkage,
[SumOfInventoryUnitsOrdered]-[SumOfInventoryUnitsReceived] AS
UnitsOnOrder,
[SumOfInventoryUnitsReceived]-[SumOfInventoryUnitsSold]-[SumOfInventoryUnitsShrinkage]
AS UnitsOnHand, [UnitsOnOrder]+[UnitsOnHand] AS SumOfOnOrderOnHand
FROM tblPresentation INNER JOIN (tblBrand INNER JOIN (tblProducts INNER
JOIN qryInventoryTransactionsSummary ON tblProducts.ProductID =
qryInventoryTransactionsSummary.ProductID) ON tblBrand.BrandID =
tblProducts.BrandID) ON tblPresentation.PresentationID =
tblProducts.PresentationID
ORDER BY tblBrand.BrandName, tblProducts.ProductDescription;

Morning All,

Iam trying to write a query for determining if certain stock needs
ordering. Im pretty sure I have all the data I need in the query. But I
dont know how to create the criteria?

I have two calculated columns for StockOnHand and StockOnOrder. I also
have a StockReorderLevel column from the products table.

How do I create the criteria in the query so that it only lists items
that less than or equal to the StockReorderLevel?

Thanks for the help,
Scott V