I need a query to use as the source for a combo box dropdown for a control in a spreadsheet-format subform. I have a table of firm names (FirmsTable) with a FirmName field, and a related table with the names of the attorneys working at those firms (AttorneysTable) with an AttorneyName field. In the subform (InputSubform), I want the dropdown for the attorney's name control (AttorneyName) to be based on a query that looks at the firm name control (FirmName) in the main form (CaseForm) and returns the list of attorneys.
But, we have local attorneys in the firms and a separate national coordinating firm. I want their attorneys to be listed as well. So, my question is, how do I write the query to look at CaseForm.FirmName and return the related attorney list AND also look at the name of the coordinating firm found in the FirmsTable and add their attorneys to give me my dropdown list.
I should note, on each case the local firm may change but the coordinating firm will always stay the same.
THANK YOU for any help!
But, we have local attorneys in the firms and a separate national coordinating firm. I want their attorneys to be listed as well. So, my question is, how do I write the query to look at CaseForm.FirmName and return the related attorney list AND also look at the name of the coordinating firm found in the FirmsTable and add their attorneys to give me my dropdown list.
I should note, on each case the local firm may change but the coordinating firm will always stay the same.
THANK YOU for any help!
