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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I think the issue here is that you don't really have a "proper relational database" - unfortunately.

Instead of three school fields on the students table, you should have a third table with the students and schools IDs along with a ranking indicator.

With that setup, you can more easily pull the top n of each student in a query - and display the names of the students and schools in the output.

Is that kind of reconfiguring something you're open to doing?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
A 'proper' relational database wouldn't have fields name School_1, School_2, School_3.

A basic design would be to have 3 tables:

Student (StudentID, StudentName)
School (SchoolID, SchoolName)
StudentSchool (StudentID, SchoolID, Choice)

The Student table would have a one-to-many relationship with the StudentSchool table based on StudentID.
The StudentSchool table would have a many-to-one relationship with the School table based on SchoolID.
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,974
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
ok, I can (sortof) see what you're saying. StudentSchool would have (up to) 3 rows for each student..
Are you saying it's impossible to make it work the way I have things set up?
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,974
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

I think the issue here is that you don't really have a "proper relational database" - unfortunately.

Instead of three school fields on the students table, you should have a third table with the students and schools IDs along with a ranking indicator.

With that setup, you can more easily pull the top n of each student in a query - and display the names of the students and schools in the output.

Is that kind of reconfiguring something you're open to doing?
So sorry I didn't see your response when I first checked here! In answer to your direct question, I'd really prefer to avoid that. This is actually a db I'm querying from Excel. Not an excuse, but proper relational isn't 'required.' ..if this becomes too much a pita, then I'll just do a Lookup on the College table.

On a somewhat side note.. if creating a db to hold survey answers such as Yes, No, Maybe, you mean each question would have its own table? with multiple rows from each survey taker and they're answers?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,831
Office Version
  1. 2019
Platform
  1. Windows
SQL:
SELECT
    SA.SA_Name,
    C1.College_Name,
    C2.College_Name,
    C3.College_Name
FROM
    SA_Profile SA
    LEFT JOIN
    Colleges C1
    ON Colleges.[ID] = SA_Profile.[GoalSchool_1]
    LEFT JOIN
    Colleges C2
    ON Colleges.[ID] = SA_Profile.[GoalSchool_2]
    LEFT JOIN
    Colleges C3
    ON Colleges.[ID] = SA_Profile.[GoalSchool_3]

This would be one way with the non-normalized data.
I think Access is wonky with left joins though (I think it requires a lot of parentheses or it doesn't work -- perhaps best to build the query in the query designer rather than hand code the sql).

If all three colleges were always non-null and related to an actual record in the colleges table then left joins could be replaced with inner joins (but somehow that strikes me as too much to hope for as a rule).



On a somewhat side note.. if creating a db to hold survey answers such as Yes, No, Maybe, you mean each question would have its own table? with multiple rows from each survey taker and they're answers?
Oh no ... now we enter the world of junction tables. Three tables - Students, Questions, and Student_Questions_Answers ... I think. Gotta run right now but someone will probably be able to explain it better anyway.
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,974
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

perhaps best to build the query in the query designer
Tried that, but I never could get that thing to work right for me.. it's not intuitive...
 

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
249
On a somewhat side note.. if creating a db to hold survey answers such as Yes, No, Maybe, you mean each question would have its own table? with multiple rows from each survey taker and they're answers?

Not necesarily, you could include them in one table as well. For instance a table with these headers:

  • Date
  • Survey taker (ideally a reference number referring to your student table)
  • Question
  • Answer


Or use two tables.
In these two tables, you could linke the survey number and the survey question number.
Table 1: General survey details
  • Survey number
  • Survey question number
  • Survey question

Table 2: Survery results
  • Date
  • Survery taker
  • survey number
  • Survey question number
  • Answer
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,831
Office Version
  1. 2019
Platform
  1. Windows
I think MSAccess would like parentheses like this but not sure (I don't have Access at work :( )
SQL:
SELECT
    SA.SA_Name,
    C1.College_Name,
    C2.College_Name,
    C3.College_Name
FROM
    (((SA_Profile SA
    LEFT JOIN
    Colleges C1
    ON Colleges.[ID] = SA_Profile.[GoalSchool_1])
    LEFT JOIN
    Colleges C2
    ON Colleges.[ID] = SA_Profile.[GoalSchool_2])
    LEFT JOIN
    Colleges C3
    ON Colleges.[ID] = SA_Profile.[GoalSchool_3])
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,915
Messages
5,638,961
Members
417,062
Latest member
Canucks21

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