ktsCatalyst
asked on
How do I create append query with looping functionality
I am working with Access 2007 and trying to create an Append Query with a loop to add records that is dependent upon one of the fields in the Select. One of the fields, named "OCCURS" in table1 contains the number of records that needs to be created table2.
For example: if "OCCURS" = 4, then 4 records should be added in table2.
For example: if "OCCURS" = 4, then 4 records should be added in table2.
Oh, and Welcome to Experts-Exchange!
;-)
Jeff
;-)
Jeff
Hello ktsCatalyst,
No need to loop. Instead, create a new table, tblNumbers, with a single column, Number, data type Long.
Populate that table with number from, say, 1-10. Now, your append query becomes something like this...
INSERT INTO table2 (FieldX, FieldY, FieldZ)
SELECT t1.FieldA, t1.FieldB, t1.FieldC
FROM table1 t1 INNER JOIN
tblNumbers n ON t1.Occurs >= n.Number
Regards,
Patrick
No need to loop. Instead, create a new table, tblNumbers, with a single column, Number, data type Long.
Populate that table with number from, say, 1-10. Now, your append query becomes something like this...
INSERT INTO table2 (FieldX, FieldY, FieldZ)
SELECT t1.FieldA, t1.FieldB, t1.FieldC
FROM table1 t1 INNER JOIN
tblNumbers n ON t1.Occurs >= n.Number
Regards,
Patrick
ASKER
Here's what I'm trying to do:
Table1
PartNo (A1002C)
PartDesc (Cover)
Qty (5)
Occurs (5)
Table2
PartNo
SerialNo
Read records in Table1. In this example, Occurs = 5; Create 5 entries in =Table2
Table1
PartNo (A1002C)
PartDesc (Cover)
Qty (5)
Occurs (5)
Table2
PartNo
SerialNo
Read records in Table1. In this example, Occurs = 5; Create 5 entries in =Table2
ktsCatalyst,
And how is the serial number determined?
Regards,
Patrick
And how is the serial number determined?
Regards,
Patrick
ASKER
Patrick,
The serial number will be updated on a form. For the purposes of this example, this field will be left blank.
The serial number will be updated on a form. For the purposes of this example, this field will be left blank.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
matthewspatrick,
this is exactly what I needed. thanks for your help and quick response.
this is exactly what I needed. thanks for your help and quick response.
For example, ...show us what you have, then show us what you need to result to be.