Query to return an employee that has all required courses for a postion

[thechazm]

I am a little lost right now on generating a query to show an employee that has all the required courses to fill a position. Any help is greatly appreciated and I'll try to be as clear as I can on the DB schema.

[Tables involed and their makeup]

[PositionsList]
ID - AutoNumber
Position - Text

[Positions]
ID - AutoNumber
JobID - Number
Position - Number

[Position Courses]
ID - AutoNumber
PositionID - Number
CourseID - Number

[Courses]
ID - AutoNumber
Course Number - Text
Course Title - Text

[Personnel]
ID - AutoNumber
Last Name - Text
First Name - Text

[Personnel Courses]
ID - AutoNumber
PersID - Number
CourseID - Number

So to try and elaborate a little bit I need if someone from the [Personnel] table that has all the required courses from [Position Courses] that are contained in [Personnel Courses] for the position in [Positions].

I am close and I can get it to verify each one but the problem is with that I get multiple entries for every course that matches.

Like I said any guidance is greatly appreciated.

Thanks,

TheChazm

 

[vbaInet]

Starting from bottom up:

"contained in [Personnel Courses] for the position in [Positions]" - INNER JOIN between these two tables

"someone from the [Personnel] table that has all the required courses from [Position Courses]" - how do you qualify this? What is the criteria?

 

[thechazm]

If I understand the question correctly the [Positions] table gets filled with a position name and then the user selects using a combo box all the required courses required for that position. This data is then normalized into the table [Position Courses] as the criteria for that specific position.

I hope that clears it up. So in a simple sense I just need it to return each person that has completed all the required courses for a specific position.

So some how I have to run through each entry in the [Position Courses] and [Personnel Courses] and where all the entries match return that [Personnel] ID.

Thanks

 

[vbaInet]

Right, what I mean is how do you identify those employees that have completed all required courses? I.e. is there a minimum number of courses each empoyee needs to complete or some other criteria?

 

[thechazm]

Ok let me try to give an example:

Some user inputs a new position in the [Position] Table. Then he sets up which courses are required to fill that position.

So example data of this would be:

[Positions]
ID = 1
Position = "Tradesman 1"

[Position Courses]
ID = 1
PosID = 1
CourseID = 3

ID = 2
PosID = 1
CourseID = 7

ID = 3
PosID = 1
CourseID = 10

So with the above example he created one position but for that position he specified that those three courses were needed to fill that position.

Now here would be an example of the [Personnel] data.

[Personnel]
ID = 1
LastName = "Joe"
FirstName = "Smoe"

ID = 2
LastName = "Smith"
FirstName = "John"

[Personnel Courses]
ID = 1
PerID = 1
CourseID = 3

ID = 2
PerID = 1
CourseID = 7

ID = 3
PerID = 1
CourseID = 10

ID = 4
PerID = 2
CourseID = 50



Now from the example above the person Joe, Smoe completed all the courses but not Smith, John.

I just want it to return Joe, Smoe since he completed all the required courses for that position.

Still don't know if I answered your question on not but I could provide an example database to maybe clear some things up and I appreciate the effort helping me.

 

[vbaInet]

Hard to visual seeing records as columns rather than rows. Can you upload a sample db with some sample data + a spreadsheet of your expected output. I will be able to understand better that way.

 

[thechazm]

No problem I'll start producing an example and I'll post it as soon as possible. Thank you for your time.

 

[vbaInet]

Oki doki! I'll most likely pick it up tomorrow if I don't get it within the next 30 mins.

 

[jdraw]

What is the question to be answered?

Something like Which Employees "have the required Courses" to qualify for Position X?

 

[thechazm]

(Removed Attachetment due to bad data)
Here is the example db with some data in it. I'll try to fix up an excel sheet but problably will not be done before you leave.

In the DB just open the only form there and hopefully it will explain and give you a visual of what I am trying to do.

Thanks

 

[thechazm]

Yes that is correct jdraw

 

[thechazm]

I am sorry but the example DB I posted was to specific and it looks like no one matches all the courses required for a specific position. I'm going to create a smaller simpler one to make it more effective.

 

[vbaInet]

Still expecting your new sample db. Or if you're fine let us know.

 

[thechazm]

I appreciate the patiance. I have been crazy booked but I'll get it posted today.

 

[thechazm]

View attachment ExampleDB.zip
Ok so here is the corrected file.

This has two people that are supposed to be able to do an inspector's position.

The Inspector position required four courses to fill that role:
10865 - G320
10871 - G321
10872 - G322
10874 - G323

There are two people that have all those courses completed:
Ainsworth
Allison-Russell

There are 236 personnel that do not have all the required courses.

The goal is to show Ainsworth and Allison-Russell into the personnel assigned drop down on the form since they have all the required courses.

 

[thechazm]

With that example does that make it clearer on what I am trying to do? I was able to write a function that builds the SQL needed to show the personnel by building a Crosstab query and then using a query on top of that to return only the individuals that have all the required courses in the drop down but when I click on a different position all the previous personnel selected disappear unless I click back on the Position next to where they were saved...

Here is the code I made but again I think I am making this harder than what it needs to be.

Function BuildSQLforPosition(lPOS As Long) As String
Dim db As Database, rs As DAO.Recordset
Dim strTmp As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from [Position Courses] WHERE [Position ID] = " & lPOS, dbOpenSnapshot)
If rs.EOF = True Then
    strTmp = "TRANSFORM [Personnel Courses].Course SELECT Personnel.ID, Personnel.[Last Name], Personnel.[First Name] FROM Personnel INNER JOIN [Personnel Courses] " & _
    "ON Personnel.ID=[Personnel Courses].[Personnel ID] GROUP BY Personnel.ID, Personnel.[Last Name], Personnel.[First Name] PIVOT [Personnel Courses].Course;"
    db.QueryDefs("tmpCRS").SQL = strTmp
    db.QueryDefs.Refresh
    BuildSQLforPosition = "Select * from [Personnel] WHERE [ID] = 0"
    
    Exit Function
End If
strTmp = "TRANSFORM [Personnel Courses].Course SELECT Personnel.ID, Personnel.[Last Name], Personnel.[First Name] FROM Personnel INNER JOIN [Personnel Courses] " & _
    "ON Personnel.ID=[Personnel Courses].[Personnel ID] WHERE"
Do While rs.EOF = False
    strTmp = strTmp & " ((([Personnel Courses].Course)=" & rs("Course ID") & ")) OR"
    rs.MoveNext
Loop
strTmp = Left(strTmp, Len(strTmp) - 3)

strTmp = strTmp & " GROUP BY Personnel.ID, Personnel.[Last Name], Personnel.[First Name] PIVOT [Personnel Courses].Course;"
db.QueryDefs("tmpCRS").SQL = strTmp
db.QueryDefs.Refresh
strTmp = ""
strTmp = "SELECT tmpCRS.ID, [Last Name] & """ & ", " & """& [First Name] AS FullName FROM tmpCRS WHERE ("
rs.MoveFirst
Do While rs.EOF = False
    strTmp = strTmp & "((tmpCRS.[" & rs("Course ID") & "]) Is Not Null) AND"
    rs.MoveNext
Loop
strTmp = Left(strTmp, Len(strTmp) - 4)
strTmp = strTmp & ") ORDER BY [Last Name] & """ & ", " & """& [First Name];"
BuildSQLforPosition = strTmp
Set rs = Nothing
Set db = Nothing
End Function

 

[vbaInet]

So it's just how Continuous forms and forms in Datasheet view work. It can't be done.

However, there's a sample db on LPurvis' website that might give you some pointers on how you can coerce it to work, but it's hard work.

http://www.databasedevelopment.co.uk/examples.htm

Look at the Continuous Form Combo example.

 

[thechazm]

Thanks for the link and I knew the reason why it would hide the listings of the personnel before because I was changing the recordset of that cont. forms combo and since its all one instance you can't seperatly have them modified unless I create a class object to handle each row that I need to modify (might be fun but not worth the work right now).

I think I am just going to set the combo box to list all personnel and override the dropdown by displaying a popup form to make the selection of the person that way the person will always be located in the rowsource of the combo box.

It's amazing at how difficult something like this has become and I still feel like there should be someway to do this completly in SQL but I don't know of a way.

Thanks for the help and the info.

 

[vbaInet]

That's right!

Trust me there isn't. It's not the first time this question has been asked and it's been something that has been worked on for many years.

Happy developing!