How to show table's title when combining 2 table in query

[sanchez]

Hi guys I need your help here..
lets say, I got several tables, tblStudent and 3 program/events tables ( tblIndustrialTraining, tblTalentDevelopment, and tblTrainingofTrainees )
So I've been ask to show one Name of Student(which is from tblStudent) and shows what events or activities he/she has joined based on those 3 tables..
Do I need to perform full join query?


I've did tried a query which I pick a student's name and select each program/event 's title from each table, and the result comes out..but how I need to differentiate which events is belong to which table..?

[June7]

How are these tables related? Is tblStudent related to each of the other 3? Will there be multiple records for each student in each of the 3 tables? If so, cannot include all 3 tables in query, use form/subform arrangement or report/subreport.

Do the 3 tables have same fields?

[sanchez]

tblStudent has 1-to-many relationship with those 3 table..Nppe they don't have a same field..the scenario is a student may join different events as many as they want but not necessary must join all of them..
So my Boss would to see for particular student (For example Susan), what event/activities she had joined all these time..

[June7]

Sounds more like a many-to-many relationship. Each student can have many activities and each activity will have many students.

Options:

1. form/subforms or report/supreports

2. a UNION query to combine all activities into a single dataset then join that query to the students table


Did you try a query that joins all 3 tables to the student table? What do you see?

[sanchez]

yes2 it actually many-to-many relationship..
i've tried a query which first I select Students name from tblStudent and select activities from 1 table first..I do that for all 3 tables..and then i copy the SQL and put UNION and then Run..The result like I said before it did come but I can't differentiate which activities belong to which table..I cannot combine all activities table into single because they have different attribute..
Here's my table:
MainTable :-
1) tblStudent-
-StudentID
-Name
-PhoneNum
-MatricNum
-College

1.1) tblStudentTalent
-ID
-StudentID
-TalentID

2) tblTalentDevelopment
-TalentID
-EventTitle
-Date
-Quarter (we divide a year into several quarter 1, 2,3 ..)

3) tbl IndustrialTraining
-IndsID
-ProgramName.

4) tblStudentInds.
-ID
-StudentID
-IndsID
-CompanyName
-Year

[sanchez]

Just now I've tried using report and I just choose student name and activity from each table..the result is the activities were listed repeatedly..I've attached a picture

[June7]

Yes, 'duplicates' because of many-to-many relationships. I knew this would happen but thought it best you see it. This is why I said you would need report/subreport to show data from all 3 tables.

Otherwise, a UNION would be like:

SELECT StudentID, TalentID, EventTitle AS Activity, [Date], Quarter, "D" AS Source FROM tblDevelopment
UNION SELECT StudentID, TalentID, ProgramName, Null, Null, "I" FROM tblIndustrialTraining
UNION SELECT StudentID, ?, ?, ?, ? , "T" FROM tblTrainingofTrainees;