Loop Query values

  • Thread starter Thread starter Maarkr
  • Start date Start date
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
 
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
 
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
Click to expand...

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
 
Back
Top