Matching two fields in Access Database

I am building an Access 2016 database with two different tables. The first table has a list of patients and doctors, procedures and cost, etc. The doctors on the list are represented by a number instead of their name. Say "1," or "35."

I have another table that has what is essentially a key for that information. In the first column I have those numbers and in the second column I have the names of the doctors to which each number represents. So Column one says "1" and Column 2 says "Dr Jones, MD."

In Microsoft Access, how do I force the database to display the names instead of the doctor using the second table? RIght now the query comes up with the field for doctor and shows the numbers. I need the database to say "Oh, the doctor field is listed as 1 so therefore it is actually Dr Jones," and display that. I'm sure it has something to do with dependencies or relationships but I'm not sure what to do. I can provide an example, if necessary.
TarkisalAsked:
Who is Participating?
 
Bill PrewConnect With a Mentor Commented:
In a query join the two tables on that id column, and then use the friendly name column from the "key" table in any reports or forms you want.


»bp
0
 
aikimarkConnect With a Mentor Commented:
When you bind a table/query to a list or dropdown control, set the width of the column(s) you don't want the user to see = 0
0
 
Martyn SpencerConnect With a Mentor ConsultantCommented:
You could create a view that joins the two tables together, or create a view for the patients table and define a lookup field that links to the doctor table. The view, if defined correctly will be read/write and you can use it wherever you wish to list patients with the doctor's full name.

I have not used Access recently, but you may even be able to modify your patient table to define the doctor field as a lookup to the doctor table.

One advantage of defining a lookup field is that when you create forms, Access automatically creates combo boxes for you with the correct links.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
One advantage of defining a lookup field is that when you create forms, Access automatically creates combo boxes for you with the correct links. I would advise against creating
That's the ONLY advantage of creating lookups at the table level. There are many reasons for NOT doing so:

http://access.mvps.org/access/lookupfields.htm

Of course, there's also this somewhat ill-informed rebuttal:

https://improvingsoftware.com/2009/10/02/blog-response-lookup-fields-in-access-are-evil/

I don't think the guy realizes the he was validating those reasons (all of his rebuttals talk about ways around the "evils" ... which kind of is the point of NOT using Lookups at the table level), and in the point regarding upsizing, he's just wrong wrong wrong!

The point here is: If you're designing a database, take the time to do it right, and avoid the traps of table-level Lookup fields.
0
 
Martyn SpencerConsultantCommented:
The point here is: If you're designing a database, take the time to do it right, and avoid the traps of table-level Lookup fields.
The questioner mentions having two tables, patient and doctor. Without seeing the detail, this was enough to lead me to conclude that they had already normalised the data adequately.

In the days when I used Access, which is some time ago now, a lookup field could only be defined on a view and it was always defined as a relationship between two tables. I mentioned using a lookup field either in a view (which would be my preferred route and was mentioned first) or if they wanted to, they could create a table-based lookup field.

My understanding is that when creating a table-based lookup field you can ensure that it refers to a lookup table rather than having Access "hide" the implementation. To be clear, that is what I am suggesting if the questioner does decide to go down the table-based lookup field route. If this is not possible, I am happy to stand corrected on the suggestion to use a table based lookup field and would revise my answer to only mention using one in a view.

If it is the case that a lookup field does allow the user to exploit a relationship between two tables and the user understands how lookup fields work and their potential limitations, it could well still be a valid choice and using them does not necessarily imply poor database design.
0
 
Martyn SpencerConsultantCommented:
I suggest that the first answer does suitably answer the question and the points raised by aikimark and me may also have value. I have not suggested that the interaction between Scott and I receive points because I don't feel that they are directed at the person asking the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.