Solved

How do I create append query with looping functionality

Posted on 2009-04-13
8
201 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now