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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
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 SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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 SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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 SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.