Combine multiple Inner Join same table

[kaylachris]

I have three Inner Join statements that pull data from the same two tables. The only difference between them is the ON statement. How can I combine all three of these into the same statement? I've tried UNION but it spits out 3 rows of data. A row for each SELECT QUERY. I've tried reading the differences between inner, outer, left, right joins because I think one of those is what I need. Reading it just confused me more. What I need is for the following three queries to be output as one row like this:



ClassNum | DateStart | DateEnd | PI | ASI | ASI2

SELECT tbl_ClassData.*, tbl_UserAccounts.L_Name
FROM tbl_UserAccounts INNER JOIN tbl_ClassData ON tbl_UserAccounts.InstructorNum = tbl_ClassData.PI
UNION
SELECT tbl_ClassData.*, tbl_UserAccounts.L_Name
FROM tbl_UserAccounts INNER JOIN tbl_ClassData ON tbl_UserAccounts.InstructorNum = tbl_ClassData.ASI
UNION
SELECT tbl_ClassData.*, tbl_UserAccounts.L_Name
FROM tbl_UserAccounts INNER JOIN tbl_ClassData ON tbl_UserAccounts.InstructorNum = tbl_ClassData.ASI2;

Also, if someone could point me in the direction of a good tutorial for SQL Join statements I'd appreciate it.

[ranman256]

make 3 queries, test each to see if they are pulling the correct data,
then combine them in the union query:

Select * from Q1
union

Select * from Q2
union
Select * from Q3

[kaylachris]

Already did that.. the code is in my first post. It doesn't produce the results I need. This is the output it gives me:

ClassNum | DateStart | DateEnd | PI
ClassNum | DateStart | DateEnd | ASI
ClassNum | DateStart | DateEnd | ASI2
(Classnum, start and end dates are the same)

This is the output I need:


ClassNum | DateStart | DateEnd | PI | ASI | ASI2

[isladogs]

Your table structure isn't correct
You shouldn't have three 'identical' fields that can all be joined to the same field in another table

It would appear that PI, ASI & ASI2 are all going to contain L_Name fields so I assume its Tutors 1, 2 & 3
Recommend you change the ClassData table to remove those 3 fields and replace with InstructorNum & InstructorType (with values PI, ASI,ASI2)

Then I think you just need 1 query and no Unions

SELECT tbl_ClassData.*, tbl_UserAccounts.L_Name
 FROM tbl_UserAccounts INNER JOIN tbl_ClassData ON tbl_UserAccounts.InstructorNum = tbl_ClassData.InstructorNum

[kaylachris]

Your table structure isn't correct....replace with InstructorNum & InstructorType (with values PI, ASI,ASI2)

Each class has 2-3 Instructors assigned to the class. PI, ASI, and ASI2 are positions within the class... I suppose I should have clarified that from the beginning. I wish each class only had one instructor it would simplify my life greatly.

tbl_ClassData has fields: ClassID [key], PI (InstructorNum for primary instructor), ASI (InstructorNum for assistant instructor), ASI2 (InstructorNum for secondary instructor), StartDate, EndDate, CourseID

tbl_User Accounts has fields: InstructorNum [key], L_Name,... login info and other PII

An instructor can be a primary in one class and a secondary in a differnet class

The query I need is to display the Primary, Assitant, and secondary instructors names instead of their InstructorNum so that I can populate it on a report.

[ssanfu]

A normalized structure would have another table for the instructors. The Instructor fields (PI, API, API2) in tbl_ClassData would be moved to tblInstructors.
The fields in tblInstructors would be InstructorID_PK (autonumber), ClassID_FK (link to tbl_ClassData) , InstructorNum_FK (link to tbl_User Accounts), InstPriority (PI, API, API2)


But, using your structure, maybe something like this?

Look at Query4....

[accesstos]

Unable to open Steve's file (Unrecognised database format) in my system, so maybe I give the same suggestion.

@keylachris, You need three instanses of the table tbl_UserAccounts as PIs, ASIs and ASIs2 that relates with field InstructorNum to the corresponding field of tbl_ClassData.

Try it:

SELECT 
tbl_ClassData.classID, 
tbl_ClassData.PI, 
PIs.L_Name AS Instructor, 
tbl_ClassData.ASI, 
ASIs.L_Name AS Assistant, 
tbl_ClassData.ASI2, 
ASIs2.L_Name AS Secondary
FROM ((tbl_ClassData LEFT JOIN tbl_UserAccounts AS ASIs ON tbl_ClassData.ASI = ASIs.InstructorNum) 
LEFT JOIN tbl_UserAccounts AS ASIs2 ON tbl_ClassData.ASI2 = ASIs2.InstructorNum) 
LEFT JOIN tbl_UserAccounts AS PIs ON tbl_ClassData.PI = PIs.InstructorNum;

Cheers,
John

[kaylachris]

A normalized structure would have another table for the instructors. The Instructor fields (PI, API, API2) in tbl_ClassData would be moved to tblInstructors.
The fields in tblInstructors would be InstructorID_PK (autonumber), ClassID_FK (link to tbl_ClassData) , InstructorNum_FK (link to tbl_User Accounts), InstPriority (PI, API, API2)

Steve / isladogs,

It finally clicked what the two of you were trying to get me to do with splitting the table. I've since restructured my tables in an attempt to "normalize" them. Could one of you please take a look at my attempt to normalize my data in the attached db? I'm still in the beginning stages of creating the db and new to the entire db concept. I'm learning as I go but rather do it correctly than trying to relearn and fix it later.

OTD-G Course Manager_be.zip

Thx in advance,
Chris

[ssanfu]

I've since restructured my tables in an attempt to "normalize" them. Could one of you please take a look at my attempt to normalize my data in the attached db?

Looking at the relationship window, I think you still need more work on the tables.

My first question is "What is the purpose of the database?" Keeping this in mind..........

Did you design/draw the tables/relationships on paper/whiteboard/the window/etc BEFORE jumping into Access?

"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.


I'm going to start off with how I design tables. Virtually every table has an Autonumber primary key (PK) field.
AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

I also add the tables to the Relationship Window and link the tables - and I set RI - many developers do not do either. On one hand, it is easier for me to visualize - on the other hand, there are 4 fingers and a thumb.

I an going to attach a text file with a lot of links (you should read - several times) to try and keep this post short(er).

Let's look at tbl_StudentPT.

The problem with this tables is that it has repeating groups.

Repeating groups are fields that have the same type of data. Push-ups, Push-ups Score, Sit-ups, Sit-ups Score, Run and Run Score are TYPES of activities.
It would be better to have a table
"tblPTScores" with fields
"PTScores" (PK/Auto),
RecordID_FK (link to tbl_StudentPT),
Activity (Pushups, Situps,Run) and
Score.

What would happen if, using current tbl_StudentPT design, you had to add Pullups and Squats? You would have to modify the table by adding 4 fields. then you would have to change the queries, forms, reports and code.
The current design of tbl_StudentPTis known as "Committing Spreadsheet".



Looking at table tbl_StudentData,

These fields define attributes of a student. Last name, first name, etc I understand.
But what about these fields?

Having a link to tbl_ClassData means that a student can only take 1 class at a time. Not sure what "ComponentCode" is. Do you need to track PayGrade? If yes, this table is the wrong place to have the field.



In table "tbl_ClassData, there is not a class name/description field. What about start and end dates? Should this be in this table? Will there be classes over multiple time frames? Do you want to have to enter the class number/description every time there is a class scheduled? I would suggest the dates should be in a different table - how about in the junction table "tbl_ClassInstructors"?

In tables "tbl_InstructorData" and "tbl_ClassInstructors", I would add a field "IsActive" (Y/N) to be able to exclude "ClassInstructors" (classes) that have expired and Instructors that do not teach any more.

OK, enough for now........ happy reading....


Edit: In tbl_StudentData, it looks like you are using a SSN as the PK field. REALLY not a good thing to do!! Another reason to use an Autonumber type for the PK field.

[kaylachris]

First off, thanks for all the great info!

Purpose of Db: To make life simple, that is once I get this beast done. I'm an instructor for military training. As it stands now, we manually copy data from two separate servers; manually input it to a ridiculously massive spreadsheet; add more data too this (course completion requirements); manually enter the same data onto all the appropriate forms (diplomas, transcripts, etc..); finally, we manually input a portion of this data back into a server.

I’m able to pull data from the two incoming servers one downloads as a .xls and the other as .csv.
The outgoing server can take batch uploads as well.

Goals
- Import data from incoming servers. Manipulate it so it’s in standard form (normalized?)
- Automate moving a student to a new class if they fail out
- Simplify data input (scroll through record-set of only one class instead of the masses)
- Provide abilities to get class avg for grades, PT, etc.. (currently doesn’t exist)
- Generate all necessary forms
- Automate email of necessary documents that require digital signature
- Automate E-file documents in appropriate containers (still working on how to automate file encryption)
- Generate batch file for upload to necessary server

As for your other questions:

Did you design/draw the tables/relationships on paper/whiteboard/the window/etc BEFORE jumping into Access?

I did draw out my plan. It seems that I was to broad in my original plan though. Luckily I posted here and someone was kind enough to slap me silly until I figured out what I was missing 😊

Having a link to tbl_ClassData means that a student can only take 1 class at a time. .....tbl_ClassData, there is not a class name/description field

ComponentCode: Active Duty, Reserves, etc.. Just haven’t built the table that links to that yet
PayGrade: in this dataset its nothing more than a name prefix, another table to build that corresponds to the correct rank.

ClassNum_PK describes itself… L###-##
L – Course designator
### first digit represents if it’s a day/night/swing class, next 2 digits is year sequence number
## Fiscal Year 2 digit date
ClassNum is unique to a set of students
ClassNum, Start/End date are assigned by DoD

In tbl_StudentData, it looks like you are using a SSN as the PK field

Yes, StudentData PK is a SSN. It’s how DoD tracks just about everything. However, this Db will be residing on a classified server, in a file folder that only has permissions granted to instructors, and the file itself will be encrypted. Seems like a lot of work for someone to grab a hold of a few SSN. Then again, I’m the idiot developing on an unsecure network and trying to remember what I did so that I can recreate the same thing on the secure network. I might just be crazy.

Since this thread has gotten off topic and you already answered the original question. I’m going to close it down and take a step back to re-examine my tables once again. Seems like I have a lot of reading homework to do over the weekend. Once I think I have it again I’ll open a new thread in a more appropriate area than queries. 😊

[kaylachris]

A normalized structure would have another table for the instructors. The Instructor fields (PI, API, API2) in tbl_ClassData would be moved to tblInstructors.
The fields in tblInstructors would be InstructorID_PK (autonumber), ClassID_FK (link to tbl_ClassData) , InstructorNum_FK (link to tbl_User Accounts), InstPriority (PI, API, API2)


But, using your structure, maybe something like this?

Look at Query4....

This solves the originally problem for those looking for a similar problem.

[kaylachris]

For those that wish to follow the rest of this thread: https://www.accessforums.net/showthread.php?t=76026