Narrowing down queries

Copper Contributor

Hello All, 


I have searched far and wide and, so far, have not come with an answer to a problem I am experiencing. 


The two categories I am concerced with are personnel and vehicles.  This is in regard to a set of first responders and their vehicles.   Essentially, I have a master roster (tblPersMaster) of everyone in the unit; I likewise have a master roster (tblVicMaster) of all the vehicles in the unit. Each person and each vehicle is either allocated to Company A or Company B.  I am using a separate table to allocate either A and B (tblIRFAllocation). Sometimes, I will have to switch people and/or vehicles from A to B or vice verse.  From here on, I will use Company A as my example. 


I use a query to narrow down all the responders I have associated with Company A.  I also have a query to narrow down the vehicles for company A QryAPersMaster; QRYAVicMaster. From here, I set a narrow it further by having a Go/No-Go field.  My final queries I am working with are QryPersGoRoster (for personnel that are able to responder) and QryVicGoRoster (for vehicles). It is necessary to have it set up like this because I am allowing subordinate teams to decide who and what vehicles they need for specific mission sets. 


Here’s where it gets tricky.  Each vehicle needs a driver and an assistant driver (A/Driver).  The additional seating in each vehicle must be filled. In the past, I have linked  tblVicMaster to tblPersMaster.  There are three fields on tblVicMaster labeled “Driver”, “A/Driver”, and “AdditionalPax” to which tblPersMaster is linked.  I allow only one selection for driver and a/driver, but multiples for additional pax.  I am trying to create a final query in which I can only select drivers from Company A who are going on the operations so that I can create a vehicle manifest.  However, I am only able to create a query in which I can select from everyone in the unit, not just those going.  In other words, I have a set of vehicles that are going and I only want to see the available personnel to fill those vehicles.  It wouldn’t be a big deal except for the fact that there are several hundred people in the unit and filtering through each one is not practical.  I am unsure whether I am going about it the right way; I do not know of any other way then to original the vehicle passengers in the master roster.  Does anyone know of a better way or how to correctly organize these relationships? 


Thank you!



1 Reply




I am not quite sure if I understand your "story". 

Can you show a screenshot of some of the relations, fields in the tables, forms with subforms and the SQL? 


Based on structure of the tables and the SQL I can advise you or give you a solution.