Home

Narrowing down queries

%3CLINGO-SUB%20id%3D%22lingo-sub-777956%22%20slang%3D%22en-US%22%3ENarrowing%20down%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-777956%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20searched%20far%20and%20wide%20and%2C%20so%20far%2C%20have%20not%20come%20with%20an%20answer%20to%20a%20problem%20I%20am%20experiencing.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20two%20categories%20I%20am%20concerced%20with%20are%20personnel%20and%20vehicles.%20%26nbsp%3BThis%20is%20in%20regard%20to%20a%20set%20of%20first%20responders%20and%20their%20vehicles.%20%26nbsp%3B%20Essentially%2C%20I%20have%20a%20master%20roster%20(tblPersMaster)%20of%20everyone%20in%20the%20unit%3B%20I%20likewise%20have%20a%20master%20roster%20(tblVicMaster)%20of%20all%20the%20vehicles%20in%20the%20unit.%20Each%20person%20and%20each%20vehicle%20is%20either%20allocated%20to%20Company%20A%20or%20Company%20B.%20%26nbsp%3BI%20am%20using%20a%20separate%20table%20to%20allocate%20either%20A%20and%20B%20(tblIRFAllocation).%20Sometimes%2C%20I%20will%20have%20to%20switch%20people%20and%2For%20vehicles%20from%20A%20to%20B%20or%20vice%20verse.%20%26nbsp%3BFrom%20here%20on%2C%20I%20will%20use%20Company%20A%20as%20my%20example.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20a%20query%20to%20narrow%20down%20all%20the%20responders%20I%20have%20associated%20with%20Company%20A.%20%26nbsp%3BI%20also%20have%20a%20query%20to%20narrow%20down%20the%20vehicles%20for%20company%20A%20QryAPersMaster%3B%20QRYAVicMaster.%20From%20here%2C%20I%20set%20a%20narrow%20it%20further%20by%20having%20a%20Go%2FNo-Go%20field.%20%26nbsp%3BMy%20final%20queries%20I%20am%20working%20with%20are%20QryPersGoRoster%20(for%20personnel%20that%20are%20able%20to%20responder)%20and%20QryVicGoRoster%20(for%20vehicles).%20It%20is%20necessary%20to%20have%20it%20set%20up%20like%20this%20because%20I%20am%20allowing%20subordinate%20teams%20to%20decide%20who%20and%20what%20vehicles%20they%20need%20for%20specific%20mission%20sets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%E2%80%99s%20where%20it%20gets%20tricky.%20%26nbsp%3BEach%20vehicle%20needs%20a%20driver%20and%20an%20assistant%20driver%20(A%2FDriver).%20%26nbsp%3BThe%20additional%20seating%20in%20each%20vehicle%20must%20be%20filled.%20In%20the%20past%2C%20I%20have%20linked%20%26nbsp%3BtblVicMaster%20to%20tblPersMaster.%20%26nbsp%3BThere%20are%20three%20fields%20on%20tblVicMaster%20labeled%20%E2%80%9CDriver%E2%80%9D%2C%20%E2%80%9CA%2FDriver%E2%80%9D%2C%20and%20%E2%80%9CAdditionalPax%E2%80%9D%20to%20which%20tblPersMaster%20is%20linked.%20%26nbsp%3BI%20allow%20only%20one%20selection%20for%20driver%20and%20a%2Fdriver%2C%20but%20multiples%20for%20additional%20pax.%20%26nbsp%3BI%20am%20trying%20to%20create%20a%20final%20query%20in%20which%20I%20can%20only%20select%20drivers%20from%20Company%20A%20who%20are%20going%20on%20the%20operations%20so%20that%20I%20can%20create%20a%20vehicle%20manifest.%20%26nbsp%3BHowever%2C%20I%20am%20only%20able%20to%20create%20a%20query%20in%20which%20I%20can%20select%20from%20everyone%20in%20the%20unit%2C%20not%20just%20those%20going.%20%26nbsp%3BIn%20other%20words%2C%20I%20have%20a%20set%20of%20vehicles%20that%20are%20going%20and%20I%20only%20want%20to%20see%20the%20available%20personnel%20to%20fill%20those%20vehicles.%20%26nbsp%3BIt%20wouldn%E2%80%99t%20be%20a%20big%20deal%20except%20for%20the%20fact%20that%20there%20are%20several%20hundred%20people%20in%20the%20unit%20and%20filtering%20through%20each%20one%20is%20not%20practical.%20%26nbsp%3BI%20am%20unsure%20whether%20I%20am%20going%20about%20it%20the%20right%20way%3B%20I%20do%20not%20know%20of%20any%20other%20way%20then%20to%20original%20the%20vehicle%20passengers%20in%20the%20master%20roster.%20%26nbsp%3BDoes%20anyone%20know%20of%20a%20better%20way%20or%20how%20to%20correctly%20organize%20these%20relationships%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-777956%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2013%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778045%22%20slang%3D%22en-US%22%3ERe%3A%20Narrowing%20down%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383702%22%20target%3D%22_blank%22%3E%40Blackmoinm17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20quite%20sure%20if%20I%20understand%20your%20%22story%22.%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20show%20a%20screenshot%20of%20some%20of%20the%20relations%2C%20fields%20in%20the%20tables%2C%20forms%20with%20subforms%20and%20the%20SQL%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBased%20on%20structure%20of%20the%20tables%20and%20the%20SQL%20I%20can%20advise%20you%20or%20give%20you%20a%20solution.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMarcel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Blackmoinm17
Frequent Visitor

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

@Blackmoinm17 

Hello,

 

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. 

 

Greetings,

 

Marcel

 

 

 

 

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies