CODEWITHSUNDEEP
sqlms-access
D. Diamond
D. Diamond

Reputation: 11

Create duplicate records based on field value Access

I have an Access table that has the following fields named tReOrder:

I am trying to create a script or query that will duplicate a record by whatever value is in the ReOrder Qty.

I have tried multiple queries, scripts, spent a few hours googling and cannot find a solution for this.

This is so we can automatically print out enough labels to label what we re-order for our stocks.

Please let me know if more data is needed.

Upvotes: 0

Views: 2220

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

  • Create a table called numbers with a single field called id of Long Integer data type:

    enter image description here

  • Populate this table with the integers 0 through 9:

    enter image description here

  • The following query will then yield the numbers 1-1000:

    select 100*n100.id+10*n10.id+n1.id+1 as n from numbers n1, numbers n10, numbers n100

    If your quantity is not likely to exceed 100, this may become:

    select 10*n10.id+n1.id+1 as n from numbers n1, numbers n10

    If your quantity is likely to exceed 1000, the following will yield 10000 records:

    select 1000*n1000.id+100*n100.id+10*n10.id+n1.id+1 as n 
from numbers n1, numbers n10, numbers n100, numbers n1000

    (Expand/contract the pattern for the order of magnitude suitable for your data)

  • You can now use this table of numbers as part of a cross join or cartesian product in order to create a query which will return a given number of records (up to the number of records returned by the above SQL), for each record in another table.

    For your table, such a query might be:

    select tReOrder.* 
from tReOrder, 
(select 100*n100.id+10*n10.id+n1.id+1 as n from numbers n1, numbers n10, numbers n100) t
where t.n <= tReOrder.[ReOrder Qty]

    Using this query, if your table were to contain the following records:

    enter image description here

    You would receive the following result:

    enter image description here

Upvotes: 1

June7
June7

Reputation: 21370

Following is query that works for me. It uses hidden table MSysObjects which is virtually certain to have enough records.

SELECT tReOrder.*
FROM tReOrder, 
   (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
WHERE F.Factor<[ReOrder Qty]
ORDER BY Barcode;

Upvotes: 1

Related Questions