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.

 

[JonXL]

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]

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]

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]

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]

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]

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]

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]

I think MSAccess would like parentheses like this but not sure (I don't have Access at work )

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]

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.