Query Union Select Filter Problem

[jeremy0028]

I have a tbl called people has the following

PersonID(PK)
LastName
FirstName
MiddleName
PrimaryInsurance(lookup)
SecondaryInsurance(lookup)

I also have a tbl called charges with a combo box called person ID so when i click the add charge command button on tbl people the person id from the charges table is linked to the person id on the people tbl. the person id on the charge form has the following

SELECT [tblPeople].[PersonID], [tblPeople].[LastName]&", "& [tblPeople].[FirstName]&" "& [tblPeople].[MiddleName] FROM [tblPeople];

I'm creating a bill to combo box

I'm trying to combine Primary, Secondary insurances from the tbl People into 1 column so that only the insurances that are related to the patient show up in the combo box
and not any other. This is what i have in the bill to combo box on the charge form

SELECT PrimaryInsurance FROM tblpeople UNION Select SecondaryInsurance FROM tblPeople;

This works fine however it displays all patients that have insurances but what i want it to display is only insurances that are related to that patient only.

Any Ideas

 

[dhookom]

I you want to limit the Row Source of the combo box to a particular PersonID, you must include the PersonID in the sql:
SELECT PersonID, PrimaryInsurance
FROM tblpeople
UNION
Select PersonID, SecondaryInsurance
FROM tblPeople;

You can save this union query and then filter it based on the PersonID in your form.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]