Loop Query values

M

Maarkr

What I have: Three tables for a many:many. Roster with ID, LFName... TngPer
with PerID, PerName (four training periods per month for the next 4 years,
named like Apr2010A, Apr2010B...)... and a TngJct with JctID, RostrID, PerID,
Status, MakeupDate...

What I need: I need code that will run an append query to append all the
Roster IDs (about 150) and PerIDs into the TngJct table. (Since all people
are supposed to do the four training periods per month their Status is
tracked as present, deferred, or null, so I must have a populated jct table
to query all names, periods, and status.)

This inserts one PerID (which is PerName Apr2010A) for each Roster ID into
the the jct table:
INSERT INTO TngJct ( RostrID, PerID )
SELECT Roster.ID, 25 AS perID
FROM Roster;

Is it best to build a function and loop the incremented perID variable,
executing the query each loop?
or Can I use the PerID from the TngPer table to have the query run thru all
the 200 PerID values? I tried a subquery (Select TngPer.TngID FROM TngPer)
AS perID in the above append query but I got a message that it will only
update one record.
thanks
 
J

John Spencer

You might try the following AFTER you backup your data.

INSERT INTO TngJct (RostrID, PerID)
SELECT Roster.ID, TngPer.PerID
FROM Roster, TngPer

That will create a record for each combination of a record in Roster and a
record in TngPer. 10 records in roster and 196 records in TngPer will give
you 1,960 records in TngJct. 150*196 will give you close to 30,000 new records.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
X

XPS350

What I have:  Three tables for a many:many.  Roster with ID, LFName.... TngPer
with PerID, PerName (four training periods per month for the next 4 years,
named like Apr2010A, Apr2010B...)... and a TngJct with JctID, RostrID, PerID,
Status, MakeupDate...

What I need:  I need code that will run an append query to append all the
Roster IDs (about 150) and PerIDs into the TngJct table. (Since all people
are supposed to do the four training periods per month their Status is  
tracked as present, deferred, or null, so I must have a populated jct table
to query all names, periods, and status.)

This inserts one PerID (which is PerName Apr2010A) for each Roster ID into
the the jct table:
INSERT INTO TngJct ( RostrID, PerID )
SELECT Roster.ID, 25 AS perID
FROM Roster;

Is it best to build a function and loop the incremented perID variable,
executing the query each loop?
or Can I use the PerID from the TngPer table to have the query run thru all
the 200 PerID values?  I tried a subquery (Select TngPer.TngID FROM TngPer)
AS perID in the above append query but I got a message that it will only
update one record.
thanks

Instead of a query you could also do a bit of programming:

Dim rsRoster As Recordset
Dim rsTngPer As Recordset
Dim rsTngJct As Recordset

Set rsRoster = CurrentDb.OpenRecordset("Roster")
Set rsTngPer = CurrentDb.OpenRecordset("TngPer")
Set rsTngJct = CurrentDb.OpenRecordset("TngJct")

While Not rsRoster.EOF
rsTngPer.MoveFirst
While Not rsTngPer.EOF
rsTngJct.AddNew
rsTngJct!RostrID = rsRoster!RostrID
rsTngJct!perID = rsTngPer!perID
rsTngJct.Update
rsTngPer.MoveNext
Wend
rsRoster.MoveNext
Wend

rsRoster.Close
rsTngPer.Close
rsTngJct.Close
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top