I have worked through printing labels in our Access based ERP system and it works 80% of the time. I can print one label for each product line item based on the QTY ordered. I ran into a problem when QTY 2 or more have different lot numbers.
For example:
PRODUCT CODE = 12345
QTY = 2
LOT = XYZ
PRODUCT CODE = 54321
QTY = 1
LOT = ABC
This prints correctly. This results in 3 labels printing. (QTY X product code works in both cases. This is because there is only one lot number per product code)
PRODUCT CODE = 12345
QTY = 2
LOT = XYZ
LOT = ZYX
PRODUCT CODE = 54321
QTY = 1
LOT = CBA
This is where it fails. This results in 5 labels printing when it should be 3. (2 X Each lot number/product code, + 1 X the single product code/lot number)
I use a count table in a query of our order tables to generate this label data.
SELECT ORDHFILE.BL, ORDTFILE.QORD, ORDTFILE.PNUM, ORDTLOT.LOTNUM
FROM tblCount, ORDTLOT, ORDHFILE INNER JOIN ORDTFILE ON ORDHFILE.BL = ORDTFILE.BL
WHERE (((ORDHFILE.BL)=[Enter Order Number:]) AND ((tblCount.CountID)<=[QORD]));
(BL in the above is actually an order number)
Would someone please give me a little guidance here?