adding strings to a table based on their entry into another table

[Shannan Casteel via AccessMonster.com]

I have a table called tblParttest. It has fields PartNo, PartNo2, PartNo3, ..
.., PartNo25 and Quantity, Quantity2, Quantity3, ...Quantity25.

If I enter "1234" into the PartNo field and "4" into the Quantity field, I
would like "1234" to be put into another table 4 times. The table would be
called tblClaimedParts, and after this first entry would look something like
this:

Claimed Parts
1234
1234
1234
1234

If I were to enter "5432" into the PartNo2 field (still of record 1) and "7"
into the Quantity2 field, tblClaimed parts would then look like this:

ClaimedParts
1234
1234
1234
1234
5432
5432
5432
5432
5432
5432
5432

If there is a way to just get it to look like this then I would very much
like to know. An even better scenerio would appear as follows:

ClaimedParts Quantity
1234 4
5432 7

I would appreciate any help I can get.

Thanks in advance.
--
Message posted via http://www.accessmonster.com

[pietlinden]

Having seen your proposed design from a previous post, may I ask why
you want to do this? It is certainly possible, but my guess is that
this may make things better in the short term (the data will be
entered) but worse in the long term (because the data will be
denormalized and hard to summarize).

I would choose option 2.
create a table consisting of PartID and Quantity. Then use a totals
query to get the sum of Quantity.

[Shannan Casteel via AccessMonster.com]

I have to have several fields for the part numbers and quantities because
this is for warranty claims and I must allow several parts to be entered in
one record.

pi********@hotmail.com wrote:

Having seen your proposed design from a previous post, may I ask why
you want to do this? It is certainly possible, but my guess is that
this may make things better in the short term (the data will be
entered) but worse in the long term (because the data will be
denormalized and hard to summarize).

I would choose option 2.
create a table consisting of PartID and Quantity. Then use a totals
query to get the sum of Quantity.

--
Message posted via http://www.accessmonster.com

[pietlinden]

You'll probably have to adjust the fieldnames and table names to suit
your needs.

Private Sub Command0_Click()
Dim rsSrc As DAO.Recordset 'refers to table
containing how many of each record to create
Dim rsDest As DAO.Recordset 'table to write data to.
Dim intCounter As Integer

Set rsSrc = DBEngine(0)(0).OpenRecordset("tblCreateRecords",
dbOpenForwardOnly)
Set rsDest = DBEngine(0)(0).OpenRecordset("tblClaimedParts",
dbOpenTable)

Do Until rsSrc.EOF
For intCounter = 1 To rsSrc.Fields("Qty")
With rsDest
.AddNew
.Fields("ClaimedPartID") = rsSrc.Fields("ClaimedParts")
.Update
End With
Next intCounter
rsSrc.MoveNext
Loop

rsSrc.Close
Set rsSrc = Nothing
rsDest.Close
Set rsDest = Nothing
End Sub