How to limit the number of records based on the quantity in a field? 1

[supportsvc]

Hello,
I'm trying to figure out how to limit the number of records based on the quantity field

My attempt:

INSERT INTO dbo_SO_InvoiceTierDistribution ( InvoiceNo, LineKey, LotSerialNo, TierType, ItemCode, WarehouseCode, QuantityShipped )
SELECT dbo_SO_InvoiceDetail.InvoiceNo, dbo_SO_InvoiceDetail.LineKey, dbo_IM_ItemCost.LotSerialNo, dbo_IM_ItemCost.TierType, dbo_IM_ItemCost.ItemCode, dbo_IM_ItemCost.WarehouseCode, dbo_IM_ItemCost.QuantityOnHand
FROM dbo_SO_InvoiceDetail INNER JOIN dbo_IM_ItemCost ON (dbo_SO_InvoiceDetail.WarehouseCode = dbo_IM_ItemCost.WarehouseCode) AND (dbo_SO_InvoiceDetail.ItemCode = dbo_IM_ItemCost.ItemCode)
WHERE (((dbo_IM_ItemCost.QuantityOnHand)=1))
GROUP BY dbo_SO_InvoiceDetail.InvoiceNo, dbo_SO_InvoiceDetail.LineKey, dbo_IM_ItemCost.LotSerialNo, dbo_IM_ItemCost.TierType, dbo_IM_ItemCost.ItemCode, dbo_IM_ItemCost.WarehouseCode, dbo_IM_ItemCost.QuantityOnHand
HAVING (((dbo_SO_InvoiceDetail.InvoiceNo)="S009904") AND ((dbo_IM_ItemCost.LotSerialNo)>=[From SerialNo]) AND ((Count(dbo_IM_ItemCost.QuantityOnHand))=[QtyShipped]));

Goal trying to import the number of records based on the quantity shown in the QuantityShipped field of the serial numbers starting with the number user inputs

Example:
QuantityShipped for the Invoice is 500 on the Item from the dbo_SO_InvoiceDetail
Need to import 500 serial numbers from the dbo_IM_ItemCost table starting with the serial number the user wants to start the range from but limit to the reocrds based on the quantity in the QuantityShipped field

Hope this makes sense

 

[dhookom]

I almost always keep a table of numbers in my databases for cases like this. A simple table [tblNumbers] with a numeric field [Num] and values 0 - 9 can generate a query [qselNumbers] with records from 0 to 9,999.

SELECT Ones.Num+Tens.Num*10+Hundreds.Num*100+Thousands.Num*1000 AS theNumber
FROM tblNumbers AS Ones, tblNumbers AS Tens, tblNumbers AS Hundreds, tblNumbers AS Thousands
ORDER BY Ones.Num+Tens.Num*10+Hundreds.Num*100+Thousands.Num*1000;

Using this numbers query in your query can generate records that are numbered based on the QuantityShipped field. This is a simple query using the Northwind Order Details table using the Quantity field:

SELECT [Order Details].OrderID, [Order Details].ProductID, [Order Details].Quantity, qselNumbers.theNumber
FROM [Order Details], qselNumbers
WHERE [Order Details].OrderID < 10261 AND qselNumbers.theNumber Between 1 And [Quantity]
ORDER BY [Order Details].OrderID, [Order Details].ProductID, qselNumbers.theNumber;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[supportsvc]

Hello,
Thank you

NOt sure what I am doing wrong but it did not produce the number of records from the QuantityShipped.
It's still producing all serial numbers

INSERT INTO dbo_SO_InvoiceTierDistribution ( InvoiceNo, LineKey, LotSerialNo, TierType, ItemCode, WarehouseCode, QuantityShipped )
SELECT dbo_SO_InvoiceDetail.InvoiceNo, dbo_SO_InvoiceDetail.LineKey, dbo_IM_ItemCost.LotSerialNo, dbo_IM_ItemCost.TierType, dbo_IM_ItemCost.ItemCode, dbo_IM_ItemCost.WarehouseCode, dbo_IM_ItemCost.QuantityOnHand
FROM qselNumbers, dbo_SO_InvoiceDetail INNER JOIN dbo_IM_ItemCost ON (dbo_SO_InvoiceDetail.WarehouseCode = dbo_IM_ItemCost.WarehouseCode) AND (dbo_SO_InvoiceDetail.ItemCode = dbo_IM_ItemCost.ItemCode)
WHERE (((dbo_IM_ItemCost.QuantityOnHand)=1) AND ((dbo_SO_InvoiceDetail.QuantityShipped) Between 1 And [QuantityShipped]))
GROUP BY dbo_SO_InvoiceDetail.InvoiceNo, dbo_SO_InvoiceDetail.LineKey, dbo_IM_ItemCost.LotSerialNo, dbo_IM_ItemCost.TierType, dbo_IM_ItemCost.ItemCode, dbo_IM_ItemCost.WarehouseCode, dbo_IM_ItemCost.QuantityOnHand
HAVING (((dbo_SO_InvoiceDetail.InvoiceNo)="S009904") AND ((dbo_IM_ItemCost.LotSerialNo)>=[From SerialNo]));

 

[dhookom]

You SQL has

AND ((dbo_SO_InvoiceDetail.QuantityShipped) Between 1 And [QuantityShipped]))

I would expect to see

AND (([highlight #FCE94F]qselNumbers.theNumber[/highlight] Between 1 And [highlight #FCE94F]dbo_SO_InvoiceDetail.QuantityShipped[/highlight]))

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[supportsvc]

Hello,
I had tried both ways and both ways produce ALL serial numbers rather than the quantity in the QuantityShipped

INSERT INTO dbo_SO_InvoiceTierDistribution ( InvoiceNo, LineKey, LotSerialNo, TierType, ItemCode, WarehouseCode, QuantityShipped )
SELECT dbo_SO_InvoiceDetail.InvoiceNo, dbo_SO_InvoiceDetail.LineKey, dbo_IM_ItemCost.LotSerialNo, dbo_IM_ItemCost.TierType, dbo_IM_ItemCost.ItemCode, dbo_IM_ItemCost.WarehouseCode, dbo_IM_ItemCost.QuantityOnHand
FROM qselNumbers, dbo_SO_InvoiceDetail INNER JOIN dbo_IM_ItemCost ON (dbo_SO_InvoiceDetail.WarehouseCode = dbo_IM_ItemCost.WarehouseCode) AND (dbo_SO_InvoiceDetail.ItemCode = dbo_IM_ItemCost.ItemCode)
WHERE (((dbo_IM_ItemCost.QuantityOnHand)=1) [highlight #FCE94F]AND ((qselNumbers.theNumber) Between 1 And [QuantityShipped][/highlight]) AND ((dbo_IM_ItemCost.QuantityCommitted)=0))
GROUP BY dbo_SO_InvoiceDetail.InvoiceNo, dbo_SO_InvoiceDetail.LineKey, dbo_IM_ItemCost.LotSerialNo, dbo_IM_ItemCost.TierType, dbo_IM_ItemCost.ItemCode, dbo_IM_ItemCost.WarehouseCode, dbo_IM_ItemCost.QuantityOnHand
HAVING (((dbo_SO_InvoiceDetail.InvoiceNo)="S009904") AND ((dbo_IM_ItemCost.LotSerialNo)>=[From SerialNo]));

 

[dhookom]

It could be that you are grouping by without adding theNumber to the output. What do you see with this query:

SELECT InvoiceNo, LineKey, QuantityShipped, theNumber
FROM qselNumbers, dbo_SO_InvoiceDetail 
WHERE theNumber Between 1 And [QuantityShipped]  AND InvoiceNo="S009904"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[supportsvc]

That is producing the correct records.

However, as soon as I add the IM_ItemCost where the SerialNOs are in that needs to be in the results (show the number of records of serial numbers from IM_ItemCost)

SELECT dbo_SO_InvoiceDetail.InvoiceNo, dbo_SO_InvoiceDetail.LineKey, dbo_SO_InvoiceDetail.QuantityShipped, qselNumbers.theNumber, dbo_IM_ItemCost.LotSerialNo, dbo_IM_ItemCost.QuantityOnHand, dbo_IM_ItemCost.QuantityCommitted
FROM qselNumbers, dbo_SO_InvoiceDetail INNER JOIN dbo_IM_ItemCost ON (dbo_SO_InvoiceDetail.WarehouseCode = dbo_IM_ItemCost.WarehouseCode) AND (dbo_SO_InvoiceDetail.ItemCode = dbo_IM_ItemCost.ItemCode)
WHERE (((dbo_SO_InvoiceDetail.QuantityShipped)>0) AND ((qselNumbers.[theNumber]) Between 1 And [QuantityShipped]) AND ((dbo_SO_InvoiceDetail.[InvoiceNo])="S009904") AND ((dbo_IM_ItemCost.QuantityOnHand)=1) AND ((dbo_IM_ItemCost.QuantityCommitted)=0));

 

[dhookom]

What happens if you save your original query as qselInvoiceQty and then create a query based on qselInvoiceQty and qselNumbers?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[supportsvc]

Ok I think it's working doing it that way. Will be testing some. Thank you!