for each...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am very familar with excel and I use the for each loop to step through a
range quite often but here is my issue...

I have loaded data into a temp table in access and I want to step through
the records one at a time and then append the data to another table once i
evaluate it. I have no idea how to do this...

Ernst.
 
You will need to use a Recordset.
Something like this:

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select * From MyTable")

Do Until rs.EOF
'Evaluation code goes here
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

You can use rs.FieldName to evaluate each field in the current row of data.

Steve
 
In
SteveM said:
You can use rs.FieldName to evaluate each field in the current row of
data.
Click to expand...

Small correction: you would use rs!FieldName to get at the field named
"FieldName" -- the bang (!) operator instead of the dot (.)
 
I have loaded data into a temp table in access and I want to step through
the records one at a time and then append the data to another table once i
evaluate it. I have no idea how to do this...
Click to expand...

If you're evaluating it manually, you'll need a recordset as suggested; if you
can select which records should be appended using criteria based on data in
the table, then no code and no stepping is needed - just an Append query.

John W. Vinson [MVP]
 
The tmpTable has First,Last,Date,Start,Stop

The destination Table has EmpID,date,start,stop

I need to look up the EmpID from First/Last and then append data to the
destination table. I would much prefer to do all this with an append query
but not very good with SQL.

Ernst.
 
The tmpTable has First,Last,Date,Start,Stop

The destination Table has EmpID,date,start,stop

I need to look up the EmpID from First/Last and then append data to the
destination table. I would much prefer to do all this with an append query
but not very good with SQL.
Click to expand...

There's got to be another table with First, Last and EmpID... right? Otherwise
there's nothing to look up. And you must assume that the names are spelled the
same in this table and in tmpTable (e.g. not "Robert Jones" in one and "Bob
Jones" in the other), and that you will never, ever have two people who happen
to have the same name (a doubtful proposition!)

Given those assumptions...

INSERT INTO [destination] (EmpID, [Date], [Start], [Stop])
SELECT Employees.EmpID, tmpTable.[Date], tmpTable.[Start], tmpTable.[Stop]
FROM tmpTable INNER JOIN Employees
ON tmpTable.[First] = Employees.[First]
AND tmpTable.[Last] = Employees.[Last];

Note that Date, First, Last and Stop are all reserved words and bad choices of
fieldname. I'm using brackets around the fieldnames which should help but
you'ld do well to pick better names!

John W. Vinson [MVP]
 
Here's what I ended up with. Works great...

Sub MoveSchedule()

Dim sSQL As String
On Error Resume Next

sSQL = "INSERT INTO [tblSchedules] (EmpID, [ScheduleDate], [StartTime],
[StopTime]) " _
& "SELECT tblEmployees.EmpID, tblImport.[SchDate],
tblImport.[StartTime], " _
& "tblImport.[StopTime] FROM tblImport INNER JOIN tblEmployees ON " _
& "tblImport.[FirstName] = tblEmployees.[EmpFirst] AND
tblImport.[LastName] = tblEmployees.[EmpLast];"

DoCmd.SetWarnings (False)
DoCmd.RunSQL sSQL
DoCmd.SetWarnings (True)
End Sub

Just one question... What happens if the employee exists on the import but
does not exist in tblEmployees? anyway to trap this and add him/her?

Thanks,
Ernst.
 
Just one question... What happens if the employee exists on the import but
does not exist in tblEmployees? anyway to trap this and add him/her?
Click to expand...

As written, it will simply not insert anything. What EmpID would you WANT to
insert if you turn up with Rihcard Barnes (typo for Richard Barnes) in the
input file?

You can certainly *detect* such unmatched records with an Unmatched Query:

SELECT [tblImport].FirstName, [tblImport.LastName]
FROM tblSchedules LEFT JOIN
tblEmployees ON tblImport.[FirstName] = tblEmployees.[EmpFirst] AND
tblImport.[LastName] = tblEmployees.[EmpLast]
WHERE tblEmployees.FirstName IS NULL;

This "frustrated outer join" query will return every record in tblImport,
whether it has a match or not; if it has a match then there will be a matching
FirstName so it won't be NULL. If there is no match the IS NULL criterion will
show you that record.

John W. Vinson [MVP]
 
Back
Top