Hi Randy,
This could be done using update query(ies), I think.
I revised your "Name" field to "TeacherNam e". ("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.Teac her field may contain extra
spaces, I'd run this query first to remove them:
UPDATE Attendance SET Attendance.Teac her = 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.Teac her =
Teachers.Teache rName SET Attendance.Teac herID = [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.Teac her, Attendance.Teac herID
FROM Attendance LEFT JOIN Teachers ON Attendance.Teac her =
Teachers.Teache rName
WHERE (((Teachers.Tea cherName) 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.N et 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.c om...
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!teac her field
search the teachers!Name field,find a match, get the ID and enter it
into the Attendance!teac hID field...
then I can delete the Attendance!teac her 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.Move First
teachers.MoveFi rst
Do While Not Attendance.EOF
teachers.MoveFi rst
TeachTxt = UCase(Trim(Atte ndance!Teacher) )
With teachers
Do While Not .EOF
If UCase(Trim(.FNa me)) = TeachTxt Then
Attendance!Teac hID = .id
Exit Do
End If
Loop
End With
Attendance.Move Next
Loop
End Function
Thanks for any Help
Randy
