根据数量字段重复记录 [英] Repeat records according to a quantity field

问题描述

我有一个从客户那里收到的Excel工作表，然后导入到Access中，然后将该表称为[tblCustomer]，看起来像这样:

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

因此该表可能包含100个项目.我想在同一张表或另一张表中将相同的记录按SumofQty复制6次，然后将下一条记录复制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.

推荐答案

您可以使用数字表"在查询中轻松地做到这一点.在数据库中创建一个名为[Numbers]的表，该表由一个名为[n]的字段组成，其字段类型为Numeric (Long Integer).在该表中创建具有值1、2、3，...的行，该行的数量远远超过您期望在[tblCustomer].[SumofQty]中看到的最大值.在我的测试中，我使用了2500，所以我的[Numbers]表看起来像

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

然后，获取表[tblCustomer]中的示例数据

Then, for sample data in table [tblCustomer]

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

查询

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

返回

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

这篇关于根据数量字段重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！