MS ACESS :restrict numbers of rows in ms access query

Question

I have two tables i want to select some specific numbers of records from table 1. the count of records must be equal to value mention in table 2.

Table1
Table 1

Table2
Table 2

Number of records in output must be equal to qty column in table 2. is it possible using access or i have to use VBA.

Gustav Gustav · Accepted Answer · 2018-05-21T10:31:25

You can use a Cartesian (multiplying) query:

SELECT 
    Products.*, 
    Barcodes.*
FROM 
    (SELECT DISTINCT 
        [Tens]+[Ones] AS Factor, 
        10*Abs([Deca].[id] Mod 10) AS Tens, 
        Abs([Uno].[id] Mod 10) AS Ones
    FROM 
        MSysObjects AS Uno, 
        MSysObjects AS Deca) AS F, 
    Products 
INNER JOIN 
    Barcodes
    ON Products.Barcode = Barcodes.Barcode
WHERE 
    F.Factor <= Barcodes.Qty;

This will create up to 100 labels per barcode.

MS ACESS :restrict numbers of rows in ms access query

2 Answers