Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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