• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

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.  
 
0
ktsCatalyst
Asked:
ktsCatalyst
  • 3
  • 3
  • 2
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
Can you present a specific example with some sample data?

For example, ...show us what you have, then show us what you need to result to be.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Oh, and Welcome to Experts-Exchange!

;-)

Jeff
0
 
Patrick MatthewsCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ktsCatalystAuthor Commented:
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
0
 
Patrick MatthewsCommented:
ktsCatalyst,

And how is the serial number determined?

Regards,

Patrick
0
 
ktsCatalystAuthor Commented:
Patrick,
The serial number will be updated on a form.  For the purposes of this example, this field will be left blank.
0
 
Patrick MatthewsCommented:
OK.

INSERT INTO Table2 (PartNo)
SELECT t1.PartNo
FROM Table1 t1 INNER JOIN
      tblNumbers n ON t1.Occurs >= n.Number
0
 
ktsCatalystAuthor Commented:
matthewspatrick,
this is exactly what I needed.  thanks for your help and quick response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now