Insert Into query based on Field value

[deesheeha]

Hiya!

Im have a problem... I have a table called tbl_NoOfDuplicates and it holds a customer name, and a number showing the number of duplicates counted from a previous table...

Customer NoOfDuplicates
John Smith 10
Linda Kelly 3

What I want to do is add the customer name to another table (tbl_Duplicates) X amount of times, X being the number of duplicates. So Linda Kelly would be added to the table 3 times......

Im finding it hard to do this because Access reads tables as a whole while I want it to go through each record, one by one. Can anyone help me with this?

Cheers
Dee

 

[Remou]

If you create a table of numbers with integers from 1 to the maximum value of [NoOfDuplicates], you can do this with a query. However, I hope that this will not affect the normalization of your database.

SELECT tbl_NoOfDuplicates.Customer 
INTO tbl_Duplicates
FROM tbl_NoOfDuplicates, tblNumbers
WHERE tblNumbers.Nos<=[NoOfDuplicates]