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!
