Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-13-2009, 11:30 AM   #1
Mel_P
Registered User
 
Join Date: Mar 2009
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Mel_P is on a distinguished road
Unhappy Using Append Query in a loop to create 6 records in one go.

Hello - I want to create a set of 6 records in an Access 2003(2000) database. I can create a record thus (SQL from append query):

INSERT INTO tblTest ( AssessmentID, AssessmentNumber, Hazard, [q(y/n)], [c(y/n)], info, moreinfo )
SELECT [Input AssNo] & [Input Hazard] AS AssID, [Input AssNo] AS AssNo, [Input Hazard] AS Haz, 'Y' AS q, 'N' AS c, 'lots' AS Inf, 'Lotsmore' AS morinf;

[Input Hazard] has 6 values (e.g. A, B, C, D, E, F) for each value of Input AssNo - which is a number e.g. 1339?

A, B, C, D, E, F are always the same so they can be "hard coded".

How do I get a query to loop and create 6 records please?

Thanks, Mel

Mel_P is offline   Reply With Quote
Old 03-13-2009, 11:38 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,305
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Using Append Query in a loop to create 6 records in one go.

The only pure SQL way would require a table with those 6 values in it as 6 records. Otherwise you're looking at a VBA loop, I suspect.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 03-13-2009, 11:56 AM   #3
Mel_P
Registered User
 
Join Date: Mar 2009
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Mel_P is on a distinguished road
Re: Using Append Query in a loop to create 6 records in one go.

Do you mean another "look up table"?

Mel

Mel_P is offline   Reply With Quote
Old 03-13-2009, 11:58 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,305
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Using Append Query in a loop to create 6 records in one go.

Basically yes. With that, if next week it needed to be 7 records, all you'd have to do is add a record to this table.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 03-13-2009, 02:56 PM   #5
Mel_P
Registered User
 
Join Date: Mar 2009
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Mel_P is on a distinguished road
Re: Using Append Query in a loop to create 6 records in one go.

I have a table of 75000 records in sets of 15 and I have to add 4 records for each set to make these sets of 18, i.e. 80,000 records!

Mel_P is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
append records to query peace77 Queries 7 04-21-2008 05:59 AM
Using an APPEND query to move records from one table to another... FostermanUK Queries 3 03-11-2008 02:36 AM
record ids auto number problem. jason2885 Forms 4 07-03-2007 10:16 PM
append query is multiplying records carpstar Queries 1 08-15-2002 07:41 PM
append query: delete records that are exactly the same jaomata Queries 3 12-26-2001 12:49 AM




All times are GMT -8. The time now is 12:02 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World