Hi Randy,
This could be done using update query(ies), I think.
I revised your "Name" field to "TeacherName". ("Name" is a reserved word.)
I also renamed both "ID" and "TeachID" to "TeacherID".
If you do the same, you could just copy and paste the SQL strings below into
a new query's SQL view, and you'd be "good to go".
Later, when building queries, etc... you'll discover that Access will
automatically link fields of the same name.(bonus!)
1.) If you're concerned that the Attendance.Teacher field may contain extra
spaces, I'd run this query first to remove them:
UPDATE Attendance SET Attendance.Teacher = Trim([Teacher]);
2.) Now that the majority of teacher names will likely match, run this query
to insert the TeacherID into the Attendance table's "TeacherID" field that
you have created.
UPDATE Attendance INNER JOIN Teachers ON Attendance.Teacher =
Teachers.TeacherName SET Attendance.TeacherID = [Teachers]![TeacherID];
3.) After you have run the #2 query, you can also use this "Find Unmatched"
query (created using the query wizard) to tell you which records have the
mis-spelled names and correct them.
SELECT DISTINCTROW Attendance.Teacher, Attendance.TeacherID
FROM Attendance LEFT JOIN Teachers ON Attendance.Teacher =
Teachers.TeacherName
WHERE (((Teachers.TeacherName) Is Null));
4.) I'd also advise against doing the "Lookup" at the table design level. Do
it using a combo-box on a form instead.
--
HTH,
Don
=============================
Use
My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
================================
"Randy" <randy@NO_SPAM.COM> wrote in message
news:1h********************************@4ax.com...
Access= 2002
I'm NOT a Programmer, but I have used VB in the past to do some things
( Spaghetti Code King) so I have some understanding of Coding
I need to replace a text field (teacher) in Table (attendance) with a
lookup fileld. the lookup field will point to a newly created table
(teachers)
the original DB was never intended to do the job that it is doing, but
now is nessessary.
the (teacher) field is a TEXT field, and the uses continue to enter
wrong names, misspelled names, wrong Data, Etc.
So I created a (teachers) table with two fields (ID) which is the
Primary Key, and Name which is text.
Now in the attendance table, I have added a lookup field called
TeachID, using the teacher table as it's source.
I need a ONETIME macro to look at the current attendance!teacher field
search the teachers!Name field,find a match, get the ID and enter it
into the Attendance!teachID field...
then I can delete the Attendance!teacher field...
The teachers table had already been populated, but there are over
25000 records in the attendance table and would be too much to do by
hand...
here is what I was trying...
Function UpdtID()
DoCmd.OpenTable "Attendance", acViewDesign, acEdit <----- ???
DoCmd.OpenTable "Teachers", acViewDesign, acEdit <---- ???
Attendance.MoveFirst
teachers.MoveFirst
Do While Not Attendance.EOF
teachers.MoveFirst
TeachTxt = UCase(Trim(Attendance!Teacher))
With teachers
Do While Not .EOF
If UCase(Trim(.FName)) = TeachTxt Then
Attendance!TeachID = .id
Exit Do
End If
Loop
End With
Attendance.MoveNext
Loop
End Function
Thanks for any Help
Randy
