Repeat records according to a quantity field

问题:

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.

回答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

回答2:

Here's the solution using Erik von Asmuth's comment which pointed to an answer originally created by Gustav.

SELECT t.*
FROM tblCustomer AS t
INNER JOIN (SELECT DISTINCT 
            [Hundreds]+[Tens]+[Ones] AS Factor, 
            100*Abs(Hundo.id Mod 10) AS Hundreds,
            10*Abs(Deca.id Mod 10) AS Tens, 
            Abs(Uno.id Mod 10) AS Ones
        FROM 
            msysobjects AS Uno, 
            msysobjects AS Deca,
            msysobjects As Hundo
        WHERE Abs(Deca.id Mod 10) <> 0 
                or Abs(Uno.id Mod 10) <> 0
                or Abs(Hundo.id Mod 10) <> 0) AS sq on sq.Factor <= t.SumofQty

I added the hundreds column as well because I think I'm probably going to need it. My method also filters out 0 in the sub query, otherwise I was getting 2 rows for things with a quantity of 1.