Solved

How do I create append query with looping functionality

Posted on 2009-04-13
8
204 Views
Last Modified: 2013-11-27
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
Comment
Question by:ktsCatalyst
  • 3
  • 3
  • 2
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24132598
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24132604
Oh, and Welcome to Experts-Exchange!

;-)

Jeff
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24132619
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:ktsCatalyst
ID: 24132730
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24132789
ktsCatalyst,

And how is the serial number determined?

Regards,

Patrick
0
 

Author Comment

by:ktsCatalyst
ID: 24132804
Patrick,
The serial number will be updated on a form.  For the purposes of this example, this field will be left blank.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 24132987
OK.

INSERT INTO Table2 (PartNo)
SELECT t1.PartNo
FROM Table1 t1 INNER JOIN
      tblNumbers n ON t1.Occurs >= n.Number
0
 

Author Closing Comment

by:ktsCatalyst
ID: 31569653
matthewspatrick,
this is exactly what I needed.  thanks for your help and quick response.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question