By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,502 Members | 3,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,502 IT Pros & Developers. It's quick & easy.

Help Switching Table Types, Please

P: n/a

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

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.