ms access - Repeat records according to a quantity field

Question

I have an Excel sheet which I receive from my customer and I get imported to Access and I would call that table [tblCustomer] and that would look something like this:

ProductID     Name         Expire date      SumofQty
------------  -----------  ---------------  --------
3             Flour        13-Dec-2013      6       
6             Meat         20-Jan-2014      10

So the table contain maybe 100 items. I want in the same table or another table to copy the same record 6 times as per SumofQty and than the next record copy it 10 times and so on. I need this cause I will create labels for each product right now I'm doing manually.

Answer 1

You can do that in a Query quite easily by using a "Numbers table". Create a table named [Numbers] in your database consisting of a single field named [n] that has a field type of Numeric (Long Integer). Create rows in that table with values 1, 2, 3, ... up to a number that well exceeds the largest value you ever expect to see in [tblCustomer].[SumofQty]. In my test I used 2500, so my [Numbers] table looks like

   n
----
   1
   2
   3
...
2499
2500

Then, for sample data in table [tblCustomer]

ProductID  Name   Expire date  SumofQty
---------  -----  -----------  --------
        3  Flour  2013-12-13          6
        6  Meat   2014-01-20         10

the query

SELECT tblCustomer.*
FROM
    tblCustomer
    INNER JOIN
    Numbers
        ON Numbers.n <= tblCustomer.SumofQty

returns

ProductID  Name   Expire date  SumofQty
---------  -----  -----------  --------
        3  Flour  2013-12-13          6
        3  Flour  2013-12-13          6
        3  Flour  2013-12-13          6
        3  Flour  2013-12-13          6
        3  Flour  2013-12-13          6
        3  Flour  2013-12-13          6
        6  Meat   2014-01-20         10
        6  Meat   2014-01-20         10
        6  Meat   2014-01-20         10
        6  Meat   2014-01-20         10
        6  Meat   2014-01-20         10
        6  Meat   2014-01-20         10
        6  Meat   2014-01-20         10
        6  Meat   2014-01-20         10
        6  Meat   2014-01-20         10
        6  Meat   2014-01-20         10