Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I create append query with looping functionality

Posted on 2009-04-13
8
Medium Priority
?
213 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 93

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 93

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 93

Accepted Solution

by:
Patrick Matthews earned 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 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