Thread: Re-query results from a query
10-13-11, 04:47 #1Registered User
- Join Date
- Oct 2011
Unanswered: Re-query results from a query
I have a roster database for staffing rosters and am trying to get the following:
When staff are rostered for a particular role then I put the roster details in a table (linked from a form), with several fields. The first field is Day and then RoleID from my roles table and then StaffID from my staff table.
Sometimes, we need to find a replacement for that particular role due to holidays, sickness and this is also recorded in the rostering table with another field telling me who it is they have replaced for that role. However, every now and then, it is fairly rare but does happen, is that the replacement staff needs to be replaced in the roster by another member, and this is also recorded in the roster table as ReplStaffID which is linked to the StaffID field in the staffing table.
For example, Staff A is originally rostered for Role 1 but is unable to fulfill this due to holidays and asks Staff B to do the role for them. Staff B agrees and the change is noted in the roster table, with Staff A showing in the ReplStaffID field. A couple of days later Staff B has, say, a dental appointment and can't do the role and gets Staff C (with Staff B in the ReplStaffID field) to do it for them, and maybe even sometimes there may be another replacement - extremely rare but possible.
If I want to look at a particular role, etc I have a query which filters the roster table to show me what I need, which works fine, but sometimes it can be a pain trying to determine who replaced whom for which role.
Basically what I'm looking to try and do is as follows:
1. Run a filtering query to show me which staff where originally rostered, and any replacements for the role(s) I want to look at, so my results are as follows:
2. What I then want to do, if possible (and I'm assuming this will be some kind of VBA code) is for the database to check if any of the ReplByID's have been replaced and add this column to my query, with the following results:
3. After this I would love the database to then go back through the results and see if any of the second replaced staff (F & E in the table above) and check to see if they have been subsequently replaced, and so on, adding another column each time, and the stopping the re-query when all staff have been added to the results. In other words when the total number of entries for that RoleID on a certain Day in the Roster table is equal to the number of records showing in StaffID, ReplByID, ReplBy2ID and so forth.
I know I could do several linked queries and keep adding a calculated column - however, whilst it would be very rare for the number of replacements to go past 2 levels (i.e. the BEF entry in the example above), it is possible and I don't want to have to visit the main table each time and work out whether I need to add any further queries to get the results I want.
I've tried searching the majority of forums but can't seem to find anything that will give me the results.
Anyone got any ideas?
Many thanks in advance.
10-14-11, 16:06 #2Registered User
- Join Date
- Jan 2002
- Bay Area
Maybe someone can improve on this solution using a series of queries in VBA code.
Step 1: get all records where there has been a replacement--create Table2
Step 2: append all records where there has not been a replacement to Table2
Step 3: create sorted results in Table3
The database with a form and code is attached.
DoCmd.SetWarnings False sqlText = "SELECT Roster.Day, Roster.RoleID, Roster.StaffID, " & _ "Roster.ReplByID INTO Table2 FROM Roster " & _ "WHERE (((Roster.ReplByID) Is Not Null)) " & _ "ORDER BY Roster.Day, Roster.RoleID;" DoCmd****nSQL sqlText sqlText = "INSERT INTO Table2 ( [Day], RoleID, StaffID, ReplByID )" & _ "SELECT Roster.Day, Roster.RoleID, Roster.StaffID, " & _ "Roster.ReplByID FROM Roster " & _ "WHERE (((Roster.ReplByID) Is Null)) " & _ "ORDER BY Roster.Day, Roster.RoleID, Roster.StaffID;" DoCmd****nSQL sqlText sqlText = "SELECT Table2.Day, Table2.RoleID, Table2.StaffID, " & _ "Table2.ReplByID INTO Table3 FROM Table2 " & _ "ORDER BY Table2.Day, Table2.RoleID, Table2.StaffID;" DoCmd****nSQL sqlText DoCmd.SetWarnings True stDocName = "Table3" DoCmd.OpenTable stDocName, acNormal, acEdit
Day RoleID StaffID ReplByID
10/3/2011 Roll1 StaffA
10/3/2011 Roll2 StaffB StaffE
10/3/2011 Roll2 StaffE StaffF
10/3/2011 Roll2 StaffF
10/3/2011 Roll3 StaffC StaffD
10/3/2011 Roll3 StaffD
10/4/2011 Roll1 StaffA
10/4/2011 Roll2 StaffB StaffE
10/4/2011 Roll2 StaffC StaffB
10/4/2011 Roll2 StaffE
10/4/2011 Roll3 StaffD