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

[starl]

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:

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]

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:

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.

 

[JonXL]

... 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]

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]

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]

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]

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.