Query involved multiple tables (1 Viewer)

[NT100]

I’ve several tables in a database, the tables’ structures and sample data (partly extracted) below

1. tblTutor – Tref, Name, First Name, Alias, District, Interests. Key: TRef


2. tblTAppt – UID, ApptStart_Dt, ApptEnd_Dt, Company, TRef (Foreign key)



3. tblTAssign_FPA - TRef, AcademicYr, Rotation, Session, TRef (foreign key).



4. tblTeachTT_FPA – DtTeach, Rotation, Session, SGp, AcademicYr


tblTutor contains personal demographic information
tblTAppt contains the appointment records
tblTAssign_FPA contains the job assignments
tblTeachTT_FPA contains the teach program’s timetable


I need to build a report with a query to get the tutors from tblTAssign_FPA in year of 2006, for examples, tbltutor.Lastnames, tblTutor.FirstName, tblTAppt.company and tblTeachTT_FPA.SGp they’ll teach and group them in the order of tblTeachTT_FPA.SGp and then have tutor’s names sorted in order .

Would you have any suggestions? Thanks in advance

Sample data can be viewed with the attachment.

 

[arnelgp]

if you can upload your db with some live data on it, i'll be happy to help you.


welcome back to me!

 

[isladogs]

Welcome back to you indeed after almost a year away
You're certainly 'on a roll' replying since you came back today

 

[NT100]

Great!
Pls. read the attached database

Thanks in advance.

 

[isladogs]

Hi

It isn't clear to me what you're trying to do with this data.
However, I've done what I think you described in post #1.

I need to build a report with a query to get the tutors from tblTAssign_FPA in year of 2006, for examples, tbltutor.Lastnames, tblTutor.FirstName, tblTAppt.company and tblTeachTT_FPA.SGp they’ll teach and group them in the order of tblTeachTT_FPA.SGp and then have tutor’s names sorted in order .

1. Presumably you meant 2016 not 2006

2. All Access tables need a PK field
I've made TRef the PK field for table tblTAppt as it was missing.

3. I've made this query using all 4 tables:

SELECT DISTINCT tblTAssign_FPA.AcademicYr, tblTeachTT_FPA.SGp, tblTutor.LastName, tblTutor.FirstName, tblTAppt.Company
FROM tblTeachTT_FPA INNER JOIN ((tblTutor INNER JOIN tblTAssign_FPA ON tblTutor.TRef = tblTAssign_FPA.TRef) INNER JOIN tblTAppt ON tblTutor.TRef = tblTAppt.TRef) ON tblTeachTT_FPA.AcademicYr = tblTAssign_FPA.AcademicYr
GROUP BY tblTAssign_FPA.AcademicYr, tblTeachTT_FPA.SGp, tblTutor.LastName, tblTutor.FirstName, tblTAppt.Company
ORDER BY tblTeachTT_FPA.SGp, tblTutor.LastName, tblTutor.FirstName;

This gives the following results:

I can't make any sense of that but hopefully you can!

If it's what you wanted, that's great.
Obviously you can add additional fields as you wish.

If its nothing like what you wanted, please explain again

 

[arnelgp]

i am sorry mr.nt100, but i can't find any unique field that will link your tblTeachTT_FPA table to tblTutor table directly or indirectly.

 

[plog]

Using the database you posted, show me exactly what data you hope to end up with.

 

[NT100]

Using the database you posted, show me exactly what data you hope to end up with.

A report in which unique teacher name list for each student group.

 

[isladogs]

A report in which unique teacher name list for each student group.

Ok so does the query output I gave you in post 5 give you the correct results?
If not you need to show us what it should be

 

[plog]

A report in which unique teacher name list for each student group.

Using the database you posted, show me exactly what data you hope to end up with.