Update Multiple field of a table from Multiple Criteria

bedevilnslay

Registered User.
Local time
Today, 01:40
Joined
Jul 13, 2012
Messages
11
Hi, I'm quite new to access and I'm building a query for HR people to find the right qualified person from range of data. Since there are 40-60 Qualifications depending on the employees's profile. The original employee database contains two tables. one is the general information with Staff ID as Primary key. and second tables contains all the information about qualification ID asscoated with Staff ID such as this table.

Staff_ID Qual_ID
1 1
1 2
1 3
2 1
2 2
2 4
3 2
3 3
4 2
4

I'm trying to generate a table in a form which list all people who have the right qualification based on user defined criteriia. For example I want to see a list of people contains Qualification ID 2 and 3. And it should come up a list with only two result which is Staff 1 and 3.

I realised that to achieve this I do need another table which has All the staff ID against each one of the qualification in order to run such query. Thus, I setup a table called tblEmployeeQualificaitonValidation with Staff_ID, Qual1, Qual2....Qual60.

And so, I create a Query which updates the First qualification field of tblEmployeeQualificaitonValidation to a True or False based on Employee ID and search whether first qualification exist in Employees's Qualification table. So the code is like this:

UPDATE tblEmployeeQualificationValidation INNER JOIN [tblEmployee Qualifications] ON tblEmployeeQualificationValidation.[Employee ID] = [tblEmployee Qualifications].[Employee ID]
SET tblEmployeeQualificationValidation.GEN001 = True
WHERE (([tblEmployee Qualifications]![Qualification ID & Description]) In ("GEN001"));

But I find it's difficult to update all the field (GEN001-GEN060) in one query. Do I have to build 60 queries and run with Macro. Or is there any way to build query into one with Switch, IIF or whatever possible function?

I'm kinda stuck right now. :banghead:Appreicated for any suggestion or help!!
 
Thus, I setup a table called tblEmployeeQualificaitonValidation with Staff_ID, Qual1, Qual2....Qual60.

Having a table like this violates normalization rules (i.e. the sequentially numbered fields). What would happen if you add a new qualification?

You would have to modify the table and EVERY related query, form and report!

The original employee database contains two tables. one is the general information with Staff ID as Primary key. and second tables contains all the information about qualification ID asscoated with Staff ID such as this table.

Staff_ID Qual_ID
1 1
1 2
1 3
2 1
2 2
2 4
3 2
3 3
4 2

I am guessing that there are actually 3 tables, one for the basic staff info, one for the basic qualification info and then a table that joins the two as you show. Am I correct? Perhaps you do not have access to that table that details each qualification.

Like this

tblStaff
-Staff_ID primary key
-FirstName
-LastName

tblQualifications
-Qual_ID primary key
-QualificationDescription

tblStaffQualifications
-StaffQualID primary key
-Staff_ID foreign key to tblStaff
-Qual_ID foreign key to tblQualifications


You probably can do this with a series of queries. First, you could find out which person has at least one of the qualifications (qualification 2 or 3 from your example). This query should do that

SELECT tblStaffQualifications.Staff_ID, tblStaffQualifications.Qual_ID
FROM tblStaffQualifications
WHERE (((tblStaffQualifications.Qual_ID) In (2,3)));

Now you could then use the above query and get a count by staff. The staff members that have qualifications 2 and 3 will have a count of 2 whereas any other staff member returned by the above query (either 2 or 3) will have a count of 1.

You can then use another query to find those that have a count of 2 to get to your final result.

That however is kind of cumbersome for a user to work through because not only would they have to say which qualifications they want but they must also provide the total number of qualifications they want (i.e. 2 in the example). Those are variables and are hard to control via a series of queries.

So I think some Visual Basic for Application (VBA) code will be necessary to make this easier for the user. I would use a multi-select list box for the user to select the qualifications that the staff member must have. The VBA code would then count the selected qualifications and then loop through them to gather the list of those selected. This information would be passed to a query. That query is a nested query that follows the series of queries that I described above. The VBA code would build the SQL text of that query and then assign it as the record source of a subform on frmSearch to display the results. I illustrate this in the attached database (just open frmSearch and try it out).

I included the series of queries & the nested query in the attached database. These are not actually used by the code. I just wanted to provide them so you could see what each query returns.
 

Attachments

Hi Jzwp,

Yes you aree right. For every new qualification, it need to add one more column in that validation table which is very nasty. And I did find a way to search criteria just like you did. Create a query for every user defined criteria and one extra query which group by all the result by employee ID and returns a table. Howevery, it will require numbers of queries and a simple macro to run all the queries. Certainly, your loop in VB solved this compeletely.

However, I do have few question regarding your VB code since I'm really newbie to both access and VB.

The loop part is quite clear, I will use that for 3 List box instead of 10 combo box and solve the multiple selection input problem. However, I don't understand your sql part. Because I tried to put 3 queries code into 1 query and didn't work. Your one looks like having 3 query but different format. Does it only works in VB or same code works in sql code as well?
 
Would you mind explain this code from ur attachment to me, I found hard to understand the syntax. Many thanks
"mySQL = mySQL & " WHERE (((tblStaffQualifications.Qual_ID) In (" & strIDs & ")))) as Q1 INNER JOIN tblStaff ON Q1.Staff_ID = tblStaff.Staff_ID""
 
"mySQL = mySQL & " WHERE (((tblStaffQualifications.Qual_ID) In (" & strIDs & ")))) as Q1 INNER JOIN tblStaff ON Q1.Staff_ID = tblStaff.Staff_ID""

The part shown in red above is the WHERE clause of one of the nested queries that finds those records with the qualification criteria collected from the list box. The variable strIDs holds the list of qualifications (Qual_ID) separated by commas that is generated by running through the loop. Variables cannot be included within the string (enclosed within the double quotes) but must be concatenated to the string hence the ampersands on either side of strIDs. The rest of the string above just joins the query Q1 to the staff table so that I could pull the names of the staff member in addition to the Staff_ID.

Does it only works in VB or same code works in sql code as well?

The code actually builds the SQL text of the query. Variables are supplied via the list box of the form (and the loop of course). Once the query is built, you can add a debug.print mySQL statement. This is usually a good way to troubleshoot a query built in code. The debug.print statement will print the contents of the mySQL variable to the VBA immediate window when you run the code (i.e. click the command button on the form). You can the see the actual SQL text and copy that to a new query & run it to check for errors. I show where you would put the debug.print statement in the code below:

Code:
Private Sub cmdSearch_Click()
Dim mySQL As String
Dim lngLoop As Long
Dim strIDs As String
Dim holdcount As Long

holdcount = Me.lstQual.ItemsSelected.Count


If Me.lstQual.ItemsSelected.Count > 0 Then
    For lngLoop = 0 To Me.lstQual.ItemsSelected.Count - 1
        If lngLoop = 0 Then
            strIDs = strIDs & Me.lstQual.ItemData(Me.lstQual.ItemsSelected(lngLoop))
        Else
            strIDs = strIDs + "," & Me.lstQual.ItemData(Me.lstQual.ItemsSelected(lngLoop))
        End If
    Next lngLoop
Else
    MsgBox "You must select at least 1 qualification from the list"
    Me.lstQual.SetFocus
    Exit Sub

End If


mySQL = "Select Q2.* FROM (SELECT Q1.Staff_ID, Count(Q1.Qual_ID) AS CountOfQual_ID, tblStaff.txtFName, tblStaff.txtLName"
mySQL = mySQL & " FROM (SELECT tblStaffQualifications.Staff_ID, tblStaffQualifications.Qual_ID"
mySQL = mySQL & " FROM tblStaffQualifications"
mySQL = mySQL & " WHERE (((tblStaffQualifications.Qual_ID) In (" & strIDs & ")))) as Q1 INNER JOIN tblStaff ON Q1.Staff_ID = tblStaff.Staff_ID"
mySQL = mySQL & " GROUP BY Q1.Staff_ID, tblStaff.txtFName, tblStaff.txtLName) as Q2 WHERE Q2.CountOfQual_ID=" & holdcount

[COLOR="red"]debug.print mySQL[/COLOR]
Me.SearchResults.Form.RecordSource = mySQL
Me.SearchResults.Visible = True
 
silly me, I was confused with that syntax before. Since my original thought was to run individual query to list the person with user defined qualification, then with one extra query which compares all those result that contains the same ID and group by to generate single name.
Thus I thought that single line sentence did all of those lol. But indeed it just generates a table with all the staffs who possess any one of those qualifications. And it eliminates the unsatisfied results with number of qualification < inputcount. That's brilliant!
 
Anyone who has not worked with code beforehand will have trouble interpreting it, so do not kick yourself over it.

I'm actually glad that your requirement was to find people who had all of the selected qualifications (i.e. 1 AND 2 AND ...) rather than trying to find the person or persons with the all OR most of the selected qualifications. Coding that would have been more involved.
 
You right. I do have a bit knowledge in other language, but just haven't been much involved in sql and vb. So it is easier for me to understand those function in coding rather than getting familiar with the windows type interface.
Didn't think much about most of selected qualifications case. But yes, it will be easy just do -1 of the inputcount with your methods. And it gets complex if the user requires that result must contains certain qualification. It might need to write a switch function to generate a table based on different case to match with the result.
 
In VBA, you can use arrays and/or recordsets rather than creating another table.
 
Hi jz, I did a bit modify on the code and it worked perfectly in both nestTestquery and form view with predefined qual ID. However, when I insert the variable SelectIDs in to the VB code, something werid happened. It promot a message with Qualification ID I selected from the list box and asking for input, as long as I type in the Qualification ID exactly same as the message, it gives me the correct result. The only key difference between my database and yours are the type of Qual_ID, my one is text, and yours is number. It is like the VB treating the input I get from the list selection as the name of variable instead of the variable itself. That's where the promot window comes from. Is there anyway I can convert this string to same type as data itself? Or am I just wrong with something else?

PHP:
Option Compare Database
Private Sub cmdSearch_Click()
 
Dim mySQL As String
Dim loopcount1 As Long
Dim loopcount2 As Long
Dim loopcount3 As Long
Dim SelectIDs As String
Dim holdcount As Long
 
holdcount = Me.Gen_ListB.ItemsSelected.Count + Me.ELE_ListB.ItemsSelected.Count + Me.CHE_ListB.ItemsSelected.Count
loopcount1 = Me.Gen_ListB.ItemsSelected.Count
loopcount2 = Me.ELE_ListB.ItemsSelected.Count
loopcount3 = Me.CHE_ListB.ItemsSelected.Count
 
For loopcount1 = 0 To Me.Gen_ListB.ItemsSelected.Count - 1
    If loopcount1 = 0 Then
        SelectIDs = SelectIDs & Me.Gen_ListB.ItemData(Me.Gen_ListB.ItemsSelected(loopcount1))
    Else
        SelectIDs = SelectIDs + "," & Me.Gen_ListB.ItemData(Me.Gen_ListB.ItemsSelected(loopcount1))
    End If
 
Next loopcount1
 
mySQL = "SELECT Q2.* FROM (SELECT Q1.[Employee ID], Count(Q1.[Qualification ID]) AS CountofQual_ID, [tblEmployee Information].[First Name], [tblEmployee Information].[Last Name]"
mySQL = mySQL & "FROM (SELECT [tblEmployee Qualifications].[Employee ID], [tblEmployee Qualifications].[Qualification ID]"
mySQL = mySQL & "FROM [tblEmployee Qualifications] "
mySQL = mySQL & "WHERE ([tblEmployee Qualifications].[Qualification ID]) IN (" & SelectIDs & " ))AS Q1 INNER JOIN [tblEmployee Information] ON Q1.[Employee ID] = [tblEmployee Information].[Employee ID]"
mySQL = mySQL & "GROUP BY Q1.[Employee ID], [tblEmployee Information].[First Name], [tblEmployee Information].[Last Name])  AS Q2 WHERE Q2.CountofQual_ID= " & loopcount1
 
 
Debug.Print SelectIDs
Me.CompetencySearchResult.Form.RecordSource = mySQL
Me.CompetencySearchResult.Visible = True
 
 
End Sub
 
Whether a field's datatype is text or number makes a huge difference and impacts the VBA code. Any text value must be enclosed in single quotes/apostrophe's in code. So in order for your version of the code to work properly, you have to code in the single quotes when you build the SelectIDs string.

I think the following will work (not tested).

If loopcount1 = 0 Then
SelectIDs = SelectIDs & "'" & Me.Gen_ListB.ItemData(Me.Gen_ListB.ItemsSelected(loopcount1)) & "'"
Else
SelectIDs = SelectIDs + ",'" & Me.Gen_ListB.ItemData(Me.Gen_ListB.ItemsSelected(loopcount1)) & "'"
End If
 
I need to create a report based on the result below so I can print in the way I want. I need to find a way to inner join the result with main employee information to generate a list of people and their qualification. I know how to do in query, but how do I call the result from this VB code?

Private Sub Clear_Click()
Me.Gen_ListB = Null
Me.BHP_ListB = Null
Me.Riot_ListB = Null
Me.CompetencySearchResult.Visible = False


End Sub

Private Sub cmdSearch_Click()

Dim mySQL As String
Dim loopcount1 As Long
Dim loopcount2 As Long
Dim loopcount3 As Long
Dim SelectIDs As String
Dim holdcount As Long

holdcount = Me.Gen_ListB.ItemsSelected.Count + Me.BHP_ListB.ItemsSelected.Count + Me.Riot_ListB.ItemsSelected.Count
loopcount1 = Me.Gen_ListB.ItemsSelected.Count
loopcount2 = Me.BHP_ListB.ItemsSelected.Count
loopcount3 = Me.Riot_ListB.ItemsSelected.Count

If holdcount = 0 Then

MsgBox "You must select at least 1 Qualification from the list"
Me.Gen_ListB.SetFocus
Exit Sub

Else
For loopcount1 = 0 To Me.Gen_ListB.ItemsSelected.Count - 1
If loopcount1 = 0 Then
SelectIDs = SelectIDs & "'" & Me.Gen_ListB.ItemData(Me.Gen_ListB.ItemsSelected(loopcount1)) & "'"
Else
SelectIDs = SelectIDs + "," & "'" & Me.Gen_ListB.ItemData(Me.Gen_ListB.ItemsSelected(loopcount1)) & "'"
End If
Next loopcount1

For loopcount2 = 0 To Me.BHP_ListB.ItemsSelected.Count - 1
If loopcount1 = 0 Then
SelectIDs = SelectIDs & "'" & Me.BHP_ListB.ItemData(Me.BHP_ListB.ItemsSelected(loopcount2)) & "'"
Else
SelectIDs = SelectIDs + "," & "'" & Me.BHP_ListB.ItemData(Me.BHP_ListB.ItemsSelected(loopcount2)) & "'"
End If
Next loopcount2

For loopcount3 = 0 To Me.Riot_ListB.ItemsSelected.Count - 1
If loopcount1 + loopcount2 = 0 Then
SelectIDs = SelectIDs & "'" & Me.Riot_ListB.ItemData(Me.Riot_ListB.ItemsSelected(loopcount3)) & "'"
Else
SelectIDs = SelectIDs + "," & "'" & Me.Riot_ListB.ItemData(Me.Riot_ListB.ItemsSelected(loopcount3)) & "'"
End If
Next loopcount3

End If

mySQL = "SELECT Q2.* FROM (SELECT Q1.[Employee ID], Count(Q1.[Qualification ID]) AS CountofQual_ID, [tbl1Employee Information].[First Name], [tbl1Employee Information].[Last Name]"
mySQL = mySQL & "FROM (SELECT [tbl1Employee Qualifications].[Employee ID], [tbl1Employee Qualifications].[Qualification ID]"
mySQL = mySQL & "FROM [tbl1Employee Qualifications] "
mySQL = mySQL & "WHERE ([tbl1Employee Qualifications].[Qualification ID]) IN (" & SelectIDs & "))AS Q1 INNER JOIN [tbl1Employee Information] ON Q1.[Employee ID] = [tbl1Employee Information].[Employee ID]"
mySQL = mySQL & "GROUP BY Q1.[Employee ID], [tbl1Employee Information].[First Name], [tbl1Employee Information].[Last Name]) AS Q2 WHERE Q2.CountofQual_ID= " & holdcount


Debug.Print SelectIDs
Me.CompetencySearchResult.Form.RecordSource = mySQL
Me.CompetencySearchResult.Visible = True


End Sub

Private Sub Form_load()
Me.CompetencySearchResult.Visible = False

End Sub
 
You cannot call a result from VBA and get it into a report directly. You have three options. First, is to use the query you built in the code and assign it to the record source for the report. If you still have to join the created query to another table/query then you can do that in code as well, just construct it and then assign it to the record source for the report. To do that you have to open the report in design view, assign the query text to the record source and then save the report. Then you can run the report. The second option is to build the entire query in code and then save the query as a query in Access (query definition). Then run the report. The third option is to create the query and run it in code code to create a new temp table and then base the report on that table.

I think option two (the saved query) carries the least amount of overhead. Every time you execute the code you will have to delete the existing query (from the previous execution) and rebuild the query with your code and then save it. This link provides some information on defining queries in VBA (querydef)
 
Thanks for the options indeed. I have considered second option while I was building the function at the time. However, it didn't work while I put variable into query. It just weird works perfect in VB but doesn't work as an independent query. I believe you built a sample for me at that time which called nest query. However I just can't get the query work with variable by itself. Do you have any clue on that?
 
However, it didn't work while I put variable into query. It just weird works perfect in VB but doesn't work as an independent query

Could you provide the code that worked and the query that was generated from the code? From there we should be able to troubleshoot why it did not work when you save it as a standalone query. If you could post a zipped copy of the database that might be better.
 

Users who are viewing this thread

Back
Top Bottom