473,881 Members | 1,726 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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!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

Nov 12 '05 #1
1 2647
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

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1001
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 format? I have 3 indexed fields: unit_id, customer_id, facility_id all in one table called units. I would like to do text searches unit ids but also account for user mistakes, like incomplete unit ids and return all possible matches. Doing...
61
24540
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 'stretch'</td> <td valign="top" width="300">some data that won't 'stetch'</td> </tr> </table>
6
6337
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, column 13: there is no attribute "SRC" <bgsound src="C:\My Documents\zingwent.mids"> You have used the attribute named above in your document, but the document type you are using does not support that attribute for this element. This error is...
4
2663
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 need to be able to do a search for specific tickets withing price ranges, different locations within the theaters, etc. etc. My problem is in the search one of the criteria is to search for a group of seats together. For example let's say...
87
9656
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: absolute;top:68px; left:563px; width:640px;height:480px;"> <IMG src="ReportImageBox_12.54.52.png" width=640 height=480></IMG>
1
344
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 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
14
3127
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 ; ExpenseType Data: 1 ; FOOD 2 ; AIRLINE 3 ; FARE
6
3331
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, EmpName,DeptID,DateOfJoin, Sal, Addr) Finance (EmpID, Sal) Club (Clubname, EmpID, Fee, DateOfJoin) Leave (EmpID, Date) Department (DeptID, DeptName, NoOfEmployees)...
2
5633
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. This seemingly happens randomly. Out of all of the database I have worked with, this has happened on 3 of them - several times randomly to each. All three of the databases that have exhibited this behaviour have been databases I have written for the...
0
9930
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11103
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9555
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5978
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4196
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3225
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.