Query for three tables

[CELES]

Hi All,
I have a database and need to create a query that will pull information from 3 tables.
Table Name Description
1 - courses: contains list of courses and their (course) levels
2 - Students: contains list of students and their training levels
3 - TrainingRecord: contains list of courses completed by each student and completion status.

The biggie here is I only want to know which courses a student needs to take based on their training level.

Thanks

 

[bdb04]

It is hard to give you much of an answer without knowing the structure of your tables, but you should be able to use the QBE pretty easily to put that together. Perhaps if you could give us some idea of what fields are in each table, we could be of more help.

 

[CELES]

Will something like this help?



TblTrainingRecord

RecNumber
AttendeeSEID
CourseNumber
FromDate
ToDate
Status
Notes

1
AAAA1
13458
1/2/2009
1/30/2009
COMPLETE


2
AAAA1
13466
1/30/2009
2/15/2009
INCOMPLETE


3
BBBB1
15341
1/30/2009
2/15/2009
WAIVED


4
BBBB1
15344
2/2/2009
2/19/2009
COMPLETE


5
AAAA1
15344
2/2/2009
2/19/2009
COMPLETE


6
BBBB1
15346
3/1/2009
3/3/2009
INCOMPLETE


TblCourseList

CourseNumber
Level
Course

13458
1
Transition to Management Training (TMT) 13458

13466
1
Management Aspects of EEO (MAEEO) (Online) 13466

15344
2
Leading Teams 15344

15346
3
Administrative Procedures for Managers (APM) (Online) 15346

15347
3
Senior Manager Course (SMC) Week 1

15348
1
Senior Manager Course (SMC) Week 2

15349
0
Frontline Manager Course (FLMC) 15349


TblManagers

SEID
LastName
TrainingLevel

AAAA1
GRAY
FLRP/CDP

BBBB1
BLUE
Manager

KJNCB
DEAN
DM






TblTrainingLevel

Level
Position

0
FLRP/CDP

1
MANAGER

2
DM

3
SENIOR MANAGER

 

[CELES]

I loaded the AccHTML but don't know how to post it. Is this what you want?

<CENTER><TABLE cellSpacing=0 align=center cellpadding=2px width=50% ><TR ><TD CLASS="AccTBMain" border-left=1px solid colSpan= 5 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInner" align=Left ><A HREF="javascript: void(0)" onclick="myFunc('437786281','USysRegInfo : ',' Record Count: 3');" title=" Record Count: 3"><font color="White">USysRegInfo : Table</font></A></TD><TD CLASS="AccTBInner" align=right >Access 2000</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('437786281','Subkey : ','Text(255) Indexed (Duplicates OK) Field Description: HKEY_CURRENT_ACCESS_PROFILE maps to HKEY_LOCAL_MACHINE if there is no profile in use');" title="Text(255) Indexed (Duplicates OK) Field Description: HKEY_CURRENT_ACCESS_PROFILE maps to HKEY_LOCAL_MACHINE if there is no profile in use">Subkey</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('437786281','Type : ','Long Integer Field Description: 0 = Key, 1 = REG_SZ, 4 = REG_DWORD');" title="Long Integer Field Description: 0 = Key, 1 = REG_SZ, 4 = REG_DWORD">Type</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('437786281','ValName : ','Text(255) ');" title="Text(255) ">ValName</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('437786281','Value : ','Text(255) Field Description: |ACCDIR in this value column is filled in with the Access directory');" title="Text(255) Field Description: |ACCDIR in this value column is filled in with the Access directory">Value</A></TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><img src='AccArr.gif' width='7' height='7'/></TD><TD CLASS="AccDataElem" >HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\Access HTML Add-in</TD><TD CLASS="AccDataElem" >0</TD><TD CLASS="AccDataElem" ><BR></TD><TD CLASS="AccDataElem" ><BR></TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\Access HTML Add-in</TD><TD CLASS="AccDataElem" >1</TD><TD CLASS="AccDataElem" >Library</TD><TD CLASS="AccDataElem" >|ACCDIR\AccHTML.mda</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\Access HTML Add-in</TD><TD CLASS="AccDataElem" >1</TD><TD CLASS="AccDataElem" >Expression</TD><TD CLASS="AccDataElem" >=AccHTMLLoader()</TD></TR><TR ><TD ID="IE" CLASS="AccIEOnlyMain" colSpan= 5 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccIEOnlyInnerRight" > Record: </TD><TD CLASS="AccButton" ><img src='AccNavL.jpg' width='40' height='15'/></TD><TD CLASS="AccIEOnlyData" > 1 </TD><TD CLASS="AccButton" ><img src='AccNavR.jpg' width='40' height='15'/></TD><TD CLASS="AccIEOnlyInnerLeft" > of 3</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccIEOnlyMain" colSpan= 5 ><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP" ><TR ><TD ID="Data437786281" CLASS="AccInfoBarInnerRight" >USysRegInfo</TD><TD ID="Info437786281" CLASS="AccInfoBarData" > Record Count: 3</TD></TR></TABLE></TD></TR><TR ><TD CLASS="tpkrow" colSpan = 5 ><CENTER><font size = 1>Access HTML Add-in provided by theprimarykey.com</font></CENTER></TD></TR></TABLE></CENTER>

 

[Will_B]

Forgetting about queries for a moment, what is the business logic that identifies a course as being one that a student needs to take? For example, if I've taken all of the Level 2 courses, does that mean I need to take all the Level 1 courses next?

Give a couple of examples when you reply and tell us if Level 1 courses are the beginner or final (advanced) level of courses. Try to use the name of your table fields in your examples.

 

[CELES]

The courses are set up for the managers, beginners are level one, intermediate are level 2, department heads are level 3, etc. (currently there are 5 levels). There are several courses offered to the different levels. For example, level 1 offers three courses, level 2 offers 4 courses. Because the courses are not scheduled in the order that each manager may need, we would like to be able to see which managers need the course when they become available. For example, if course number 46592 were scheduled, the query would display everyone that needs to take the course. Course #46592 will only be present in one level. It is possible be a 2nd level manager that never received one or more 1st level courses, that person should be in the list. The list should display any person equal to or below the training level and did not complete the course. There is one exception, the managers can be waived a course. For example, a manager comes on board at level 4; they will not have to complete level 1 course there status would be waived. They also would not be in the list. The statuses are complete, incomplete, waived, and in cases where the person never took the class the status should be blank. So when we list the records we should only see people at or below the training level with an incomplet or blank status.

The tables are structure as:
TblManagers: The SEID is the primary key.
SEID LastName FirstName TrainingLevel
AAAA1 GRAY MARY FLRP/CDP
BBBB1 BLUE CINDY Manager
KJNCB DEAN STEPHANIE DM

TblCourseList: Level is the Foreign key from the trainingLevel table
CourseNumber Level Course
13458 1 Transition to Management Training (TMT) 13458
13466 1 Management Aspects of EEO (MAEEO) (Online) 13466
15344 2 Leading Teams 15344
15346 3 Administrative Procedures for Managers (APM) (Online) 15346

TblTrainingRecord: SEID is foreign key from TblManagers and CourseNumber is foreign key from TblCourseList
RecNumber AttendeeSEID CourseNumber FromDate ToDate Status
1 AAAA1 13458 1/2/2009 1/30/2009 COMPLETE
2 AAAA1 13466 1/30/2009 2/15/2009 INCOMPLETE
3 BBBB1 15341 1/30/2009 2/15/2009 WAIVED
4 BBBB1 15344 2/2/2009 2/19/2009 COMPLETE
5 AAAA1 15344 2/2/2009 2/19/2009 COMPLETE
6 BBBB1 15346 3/1/2009 3/3/2009 INCOMPLETE
** There is one problem with the above table; it should show a blank from date, to date and status for a person who hasn’t taken any course.

Hope this makes sense.

 

[Will_B]

I came up with the following approach as you were typing your last reply. Let me run this by you and you can edit it to fit the details you provided (thanks, by the way). My concept is (1) identify ALL possible courses each manager could take based on their Training Level; (2) identify all courses each manager has completed or had waived; (3) for each manager, find all courses from Task 1 that are not listed in the results from Task 2.

We can do these three tasks in one query by using one SQL string within another. The trick is that we'll create a new "identifier" that combines the ManagerID with the course number. This will let us match ALL courses with COMPLETED/WAIVED courses.

Open the Query Designer and switch the view from "Design View" to "SQL View" --- then paste this SQL in. To see the results, switch to "Datasheet View". Add any additional fields you want in your results and save it (once you're satisfied it works).

SELECT tblManagers.SEID, tblManagers.LastName, tblManagers.TrainingLevel, tblCourseList.Level, tblCourseList.CourseNumber, tblCourseList.Course, [SEID] & "-" & [CourseNumber] AS MyID
FROM (tblManagers INNER JOIN tblTrainingLevel ON tblManagers.TrainingLevel = tblTrainingLevel.Position) INNER JOIN tblCourseList ON tblTrainingLevel.Level = tblCourseList.Level
WHERE ((([SEID] & "-" & [CourseNumber]) Not In (SELECT [AttendeeSEID] & "-" & [CourseNumber] AS MyID FROM TblTrainingRecord WHERE (((TblTrainingRecord.Status)="complete" Or (TblTrainingRecord.Status)="waived"));)));

You can examine the criteria separately if you want. While in Design View copy the "Not In(..." criteria and paste that into another new query in SQL View. Delete the "Not In(" and the last parentheses after you paste it. *** Here you might want to add "INCOMPLETE" along with "COMPLETED" and "WAIVED" if you don't want incomplete courses considered as needing to be taken.

This probably is NOT exactly what you need, but I think you can edit my query to make it fit the situation you described.

 

[CELES]

Will,
This is returning a blank table. I'll look at it somemore a little later today. I'll keep you posted.

 

[Will_B]

In case it helps identify the problem, I used the table structure, table and field names, and sample data you provided in the 6:48pm posting (although I left some fields out). Also, in my tables I left ALL fields as data type of TEXT. Could the "TrainingLevel" field in tblManagers be a number (0-3) rather than a phrase (e.g., Manager, DM)?

Here is how my query looks in Design View:

By the way, I came up with a way to include all courses of levels lower than the manager's training level, but I'll wait to hear that what I sent you works before I trouble you with more SQL.

 

[CELES]

Yep, my query looks the same as yours but I never could get this to work. Are you able to see the records?