473,325 Members | 2,608 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Help Switching Table Types, Please


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
1 2598
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Matias Silva | last post by:
I am still a novice when it comes to MySQL and I was wondering if switching the indexing from INDEX to a FULLTEXT would have any dramatic repercussions on current searches based on the the INDEX...
61
by: Toby Austin | last post by:
I'm trying to replace <table>s with <div>s as much as possible. However, I can't figure out how to do the following… <table> <tr> <td valign="top" width="100%">some data that will...
6
by: mike | last post by:
Hello, After trying to validate this page for a couple of days now I was wondering if someone might be able to help me out. Below is a list of snippets where I am having the errors. 1. Line 334,...
4
by: Orion | last post by:
Hi, This is kind of last minute, I have a day and a half left to figure this out. I'm working on a project using ms-sqlserver. We are creating a ticket sales system, as part of the system, I...
87
by: expertware | last post by:
Dear friends, My name is Pamela, I know little about CSS, but I would like to ask a question I have an image on a web page within a css layer: <DIV ID=MyLayer STYLE = "position:...
1
by: Randy | last post by:
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...
14
by: alwayshouston | last post by:
Hi All! I am working on this very small database and I am confused in the designing a simple form. I only have three tables in the database. First Table: tblExpense Columns: ExpenseID ;...
6
by: jenipriya | last post by:
Hi all... its very urgent.. please........i m a beginner in oracle.... Anyone please help me wit dese codes i hv tried... and correct the errors... The table structures i hav Employee (EmpID,...
2
by: programmerx101 | last post by:
Ok, I'm looking for expert advice on this one. I have a database which keeps going into read_only mode. Sometimes it goes into read_only / single user mode. Once it was taken offline completely....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.