Insert Duplicate Records in Access using SQL

[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!

 

[Remou]

Hi
You could Dlookup the number of coupons and repeat the insert in a Do While loop.

 

[LittleSmudge]

Is TrackingNo the PrimeKey for the table ?

If not what is the TYPE of the PrimeKey ?

Where is the "number of tickets needed" held ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Tash]

LittleSmudge -

I have two tables:

TableCoupons
CouponNo,Name,ExpirationDate,NumberofCoupons

TableCouponList
CouponNO,Name,ExpirationDate

I need to take the single record entry in 'TableCoupons' and multiply it by the value in the "NumberofCoupons" field, then insert these records into the 'TableCouponList'. My objective is to print out multiple coupons for a single guest, but only making one record entry (to cut back on the data entry work).

 

[gwinn7]

You mean something like....

--------------------------------
Enter Guest Name:____Joe Shmoe
Enter Expiration:____12-5-2005
Coupons to Print:____10
<print now>
--------------------------------

If this is the case, your SQL might be something like...

i = 1
Do Until i = CouponstoPrint

Docmd.Setwarnings False
Docmd.RunSQL _
"INSERT INTO TableCouponList " & _
"(TrackingNo, Name, ExpirationDate ) " & _
"SELECT newtracking() as track, " & chr(34) & "Joe Shmoe" & _ "chr(34) " as Guest, ExpirationDate " & _
"FROM TableCoupons;"
Docmd.Setwarnings TRue
dbengine.idle
i=i+1

Loop

Because I have very little information about your project, your TableCouponList will have to have the "TrackingNo" to be the Primary Key. Depending on the sophistication of your tracking number, I would just simply avoid the insert of that number and utilize that field as an auto-incremented value.

This is pretty much what Remou stated above.

Gary
gwinn7

 

[LittleSmudge]

This might actually be an example of a table that does not need a PrimeKey

( A WHAT I hear you cry. )

TableCouponList
CouponNO,Name,ExpirationDate

clearly none of the above fields can be the PK.
Also the user NEVER needs to access a single record at a time ( other than the write cycle )
In use, the user selects the block of records that are to be printed
( Where CouponNo = Xxx )
which could yield none one or more records.


So WHAT is the point of adding a PrimeKey to the table that will never be utilised just to comply with some theoretical rule that says every table must have a PK.


On the other hand I can think that
TableCouponList
CouponNO,Name,ExpirationDate
is itself serving no useful purpose anyway


Using just
TableCoupons
CouponNo,Name,ExpirationDate,NumberofCoupons

Dim rst etc..

Set rst = db.OpenRecordSet("SELECT * FROM TableCoupons WHERE CouponNo >= " & couponValue )

While Not rst.EOF
    For intC = 1 To rst!NumberOfCoupons
        PrintCoupon(rst!CouponNo, rst!Name, rst!ExpirationDate)
    Next
    rst.MoveNext
Wend

With one table only, the above prints all outstanding coupon batches from couponNumber up to the latest record created and prints the number of those required.

This will work fine even if couponNumber is the last one created
(Change the WHERE clause to = if you just want the current CouponNo )

PrintCoupon is the function - or whatever code you need - to actually put ink onto page for a single coupon.



By The Way,
What part of the world used a word "Expiration" !



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Tash]

Thanks for all your suggestions, I got it working using the one table as LittleSmudge had said. Thanks for all your help - this one part of the program was driving me crazy. It's so nice when it finally starts coming together!