Need to replace multiple field values in Table A with matching value in Table B

starl

Administrator
Joined
Aug 16, 2002
Messages
5,974
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
Trying to create a proper relational database of student names and top 3 preferred colleges.
So, Table A has the following fields: Student Name, School_1, School_2, School_3
Table B: Autonumber key being used as primary key, School Name

The School values stored in Table A are the primary keys in Table B.
I create a relationship tying the School_# to the ID in Table B.

I'm trying to pull the data so that when I pull student A and their schools, I get the school names instead of the values. I got it to work when only pulling School_1, but not when I include all the schools.
What works:
SQL:
SELECT SA_Profile.SA_Name, Colleges.College_Name
FROM Colleges 
INNER JOIN SA_Profile ON Colleges.[ID] = SA_Profile.[GoalSchool_1];

how do I get it to also return Colleges.[ID] = SA_Profile.[GoalSchool_2], Colleges.[ID] = SA_Profile.[GoalSchool_3]?

Note that when I look at working Access query in Design View, it only shows a relationship between Colleges.ID and SA_Profile.GoalSchool_1. I don't know why the relationships I created for all 3 schools don't show, nor how to add them.
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,974
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
First, I want to thank you for the in-depth answers! It's obvious I'm trying to pick this stuff up and I know less than even what I think I know, as proven by @JonXL's answer, which returns exactly what I wanted. No need for a JOIN at all (I've got some reading to do - obviously I'm missing something about the purpose of JOINs.. or is it because my db wasn't properly relationed? (is that word? *lol*)).
Looking back at your original post, it sounds like you are really just trying to get the names of the schools already having their IDs. This query should be able to do that - no joining required:

SQL:
SELECT
    SA_Profile.SA_Name,
    (SELECT College_Name FROM Colleges WHERE Colleges.[ID] = SA_Profile.[GoalSchool_1]) AS [First Choice],
    (SELECT College_Name FROM Colleges WHERE Colleges.[ID] = SA_Profile.[GoalSchool_2]) AS [Second Choice],
    (SELECT College_Name FROM Colleges WHERE Colleges.[ID] = SA_Profile.[GoalSchool_3]) AS [Third Choice]
FROM SA_Profile

I didn't test, but see no reason why it wouldn't work offhand. Though let me know if it gives problems.
I've already been able to modify this to pull a specific student and additional fields, so I do think it will do what I want. Thank you!

Also a shout out to @xenou for introducing aliases to me. Led to me actually figuring out the Design View interface more.
And thank you @Norie and @petertenthije for the relational database examples.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
... No need for a JOIN at all (I've got some reading to do - obviously I'm missing something about the purpose of JOINs.. or is it because my db wasn't properly relationed? (is that word? *lol*)).

Ideally you would be able to use the join because the data would be structured correctly. With the solution I've provided, you can get the information you're after, but it can cause problems - specifically I know those statements will not work if you try to feed this query into a crosstab. (In that case, you will need to replace the nested SELECT statements with DLookup().)

And going forward you will be at a disadvantage as you try to pull pretty much anything else out of your data in way of meaningful insights. For example, suppose you want to know which schools are most preferred. With two tables and three different fields all holding part of the story, writing the queries to give you that answer is going to be nightmarish and, honestly, will probably involve some sort of midway query that represents the data in a more normalized fashion so you can feed it into queries meant to work with normalized data.

I'm glad it worked out for you, but since it sounds like you don't have much control to fix this, I will also wish you good luck as you go forward with this data set. :)
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,974
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
if you try to feed this query into a crosstab
What's a crosstab?

Well, I do have control, but I wanted to keep it simple.... it's (currently) in Access and I don't want to overwhelm it, or me. My past projects didn't really need proper relational databases, so my skill level is really at the most basic of queries (INSERT, UPDATE, SELECT, DELETE). And I'm using ADO.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,909
Office Version
  1. 365
Platform
  1. Windows
What's a crosstab?

Well, I do have control, but I wanted to keep it simple.... it's (currently) in Access and I don't want to overwhelm it, or me. My past projects didn't really need proper relational databases, so my skill level is really at the most basic of queries (INSERT, UPDATE, SELECT, DELETE). And I'm using ADO.
Hi Tracy.

I believe he is referring to a CrossTab Query.
See: Make summary data easier to read by using a crosstab query
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,974
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
oh! It's like a pivot table :)

(on a side not, I am SO embarrassed that @Joe4 has seen my post. Now he won't think I'm so brilliant anymore 🤪 )
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,909
Office Version
  1. 365
Platform
  1. Windows
oh! It's like a pivot table :)

(on a side not, I am SO embarrassed that @Joe4 has seen my post. Now he won't think I'm so brilliant anymore 🤪 )
Yes, it is sort of like a Pivot Table in Access.

Not to worry. We all cannot be experts about everything!
There are some things in Excel that I am kind of embarrassed about not knowing better myself (like Pivot Tables, Charts, and Tables).
I seldom have need to use them, so I am not as knowledgeable about those things.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,269
Messages
5,635,190
Members
416,846
Latest member
ImGoing2needaFormula

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top