I have two forms set up on two different tables. The first Form(table) contains records of medical records requests. There are multiple requests (up to 5) that have the same claim # yet different provider names and other data that are all stored on different rows.
I want to be able to generate a list of the providers on the second form(table) based on the claim# entered.
I have created a UNION Query that looks like this:
SELECT [Medical Records Requests].[Claim Num], [Medical Records Requests].ProvName
FROM [Medical Records Requests] UNION SELECT Pending.PendingClaimNum, Pending.Providers
It displays the provider on every record in the table via listbox. Again, I'm trying to display only those providers with the corresponding claim# from table 1. Is this possible? can I apply an advanced filter, or does this need to be accomplished in the query itself?
I've attached a jpeg of the forms i'm working with.
Thanks a lot!