Insert Duplicate Records into Access

[Tash]

I am trying to create Buffet Coupons for our hotel Front Desk to distribute. The user would input the guest name, expiration date and number of coupons into a Access Database. I would like to multiply this single record with the number of coupons needed and insert it into another table. This is what I have so far in SQL:

INSERT INTO TableCouponList ( TrackingNo, Name, ExpirationDate )
SELECT TrackingNo, Name, ExpirationDate
FROM TableCoupons;

This statement only inserts one record into the new table. How can I modify it to insert the record multiple times (based upon a value in one of the fields)?

Thanks

 

[jabrony76]

Try:

Declare @Counter int, @i int

Set @Counter = --# of rows you want to insert
Set @i = 0

While @Counter > @i
BEGIN

INSERT INTO TableCouponList ( TrackingNo, Name, ExpirationDate )
SELECT TrackingNo, Name, ExpirationDate
FROM TableCoupons;

Set @i = @i + 1

END

 

[amrita418]

Why not add another field to the TableCouponList table to store the count rather than inserting multiple records and later trying to count them?

Regards,
AA

 

[Tash]

amirita418, I actually have a field in TableCoupons that stores how many coupons I need. However, I need to insert multiples into the TableCouponList so that it will print that many times over.

jabrony76 - I'm going to work on that one right now,thanks for the info!

 

[amrita418]

The code that jbrony76 provided will insert same number of records for all TrackingNo. I thought the number would be different for each TrackingNo.

 

[Tash]

Mmm...ok, this is what I need, then. I have a field in TableCoupons called "NumberofCoupons". This number will be different for each entry. I need to take that number, and insert that number of coupons into the second table.

TableCoupons (Entries go into this table)
TrackingNo,Name,ExpirationDate,NumberofCoupons

TableCouponList (Duplicate records get inserted into this table)
TrackingNo,Name,ExpirationDate

Does this make sense? There might be a better way to do what I am doing. All I really need, is to take the same information and duplicate it for printing purposes. Our Front Desk staff will print anywhere from 10-14 coupons on average per customer.

 

[amrita418]

Try the code below:

declare 
     @counter int 
     set @counter = 15 -- set it to max value in numofcoupons
while @counter <> 0
begin
      INSERT INTO TableCouponList(TrackingNo, 
                                  Name, 
                                  ExpirationDate)
      SELECT  TrackingNo, 
              Name, 
              ExpirationDate
      FROM    TableCoupons
      WHERE   NumberofCoupons
 >= @counter;

    set @counter = @counter - 1
end

Regards,
AA

 

[r937]

see thread701-553683, it's exactly the same problem -- print a variable number of labels for each order

also, you did say Access (so a T-SQL solution might not work), so unless you like writing code in Access, the integers table is easier

r937.com | rudy.ca

 

[Tash]

r937 -

THANK YOU! That was very easy and exactly what I was looking for. It's also nice that I am not actually inserting the records since over time that would amount to a very big database. Now I just have to make a user interface to query which records they need to print and I am set.

Thanks again!