Can't seem to filter in UNION Query

Hello,

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
FROM Pending;

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!



dbscreenshot.jpg
c9k9hAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
<I only want to show the providers from [medical records request] based on the claim number entered in [pending] so WHERE ClaimNum=" YourClaimNumberHere would not work as the claim # will be a variable>

Then you don't need a Union query at all. If the listbox is on the same form, then just do this in the AfterUpdate event of the Claim Number control on your Pending form:

Me.YourListbox.RowSource = "SELECT Providers FROM [Medical Records Request] WHERE [Claim Number]=" & Me.ClaimNumberControl



0
 
dirknibleckCommented:
Hi again,

if you change the query to be:

SELECT * FROM
(SELECT [Medical Records Requests].[Claim Num], [Medical Records Requests].ProvName
FROM [Medical Records Requests] UNION SELECT Pending.PendingClaimNum, Pending.Providers
FROM Pending)

WHERE [Claim Num] = [Forms]![yourForm]![control];

You should be good to go.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
When you say "generate a list of providers", do you want to show Providers from both [Medical Records Requests] as well as [Pending]?

SELECT [Medical Records Requests].[Claim Num] AS ClaimNum, [Medical Records Requests].ProvName AS Provider
FROM [Medical Records Requests] UNION SELECT Pending.PendingClaimNum AS ClaimNum, Pending.Providers AS Provider FROM Pending WHERE ClaimNum=" YourClaimNumberHere

Set your ListBox to ColumnCount = 2 and ColumnWidths = 0;1 and you should show the Provider name in that column.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
c9k9hAuthor Commented:
Hello and thank you both! (again!)

I only want to show the providers from [medical records request] based on the claim number entered in [pending] so WHERE ClaimNum=" YourClaimNumberHere would not work as the claim # will be a variable
0
 
c9k9hAuthor Commented:
Dirk:

when I use the code provided above, save and reopen it returns this:

SELECT *
FROM [SELECT [Medical Records Requests].[Claim Num], [Medical Records Requests].ProvName
FROM [Medical Records Requests] UNION SELECT Pending.PendingClaimNum, Pending.Providers
FROM Pending]. AS [%$##@_Alias]
WHERE [Claim Num] = [Forms]![frmMedRecords]![control];
0
 
dirknibleckCommented:
Yeah, it does that to me all of the time too. It works when you run it (so long as you don't save it after opening it like this).

If you go to edit it, you just need to remember to convert the "[" in the "FROM [ SELECT" to "(", and to replace "]. AS [%$##@_Alias]" with ")" or ") as alias"
0
 
c9k9hAuthor Commented:
well the listbox is on a different form than where it is pulling the actual providers from, but on the same form as the claim number from the previous form is being generated via :

Private Sub openpending_Click()

On Error GoTo Err_openpending_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Pending"
   
    stLinkCriteria = "[Claim Num]=" & "'" & Me![PendingClaimNum] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_openpending_Click:
    Exit Sub

Err_openpending_Click:
    MsgBox Err.Description
    Resume Exit_openpending_Click
   
End Sub


Also, how would i go about creating Me.ClaimNumberControl as it is throwing me an error saying that the data member is not found?

The list box is set up for 2 columns, claim# and provider like you both had mentioned and that works fine. I took away the union query as the rowsource for now to try this method.
0
 
c9k9hAuthor Commented:
please disregard that last code snippet, I had to comment that out.
0
 
c9k9hAuthor Commented:
This is what I'm working with now:

Private Sub pendclaimnum_AfterUpdate()
Me.lstProviders.RowSource = "SELECT ProvName FROM [Medical Records Request] WHERE [Claim Num]=" & Me.PendingClaimNum
End Sub

Debugging at Me.PendingClaimNum "Method or data member not found"
0
 
Rey Obrero (Capricorn1)Commented:
the name of your combo is not the name you are placing in your row source

Private Sub pendclaimnum_AfterUpdate()
'Me.lstProviders.RowSource = "SELECT ProvName FROM [Medical Records Request] WHERE [Claim Num]=" & Me.PendingClaimNum


Me.lstProviders.RowSource = "SELECT ProvName FROM [Medical Records Request] WHERE [Claim Num]=" & Me.PendClaimNum
End Sub
0
 
c9k9hAuthor Commented:
I Changed to

Me.lstProviders.RowSource = "SELECT ProvName FROM [Medical Records Request] WHERE [Claim Num]=" & Me.pendclaimnum

Still nothing...

What should the row source of the lstProviders be? in the properties of the actual list box? it's blank as of now.
0
 
c9k9hAuthor Commented:
lstProviders Properties
screen.jpg
0
 
Rey Obrero (Capricorn1)Commented:
you se the column count to 2, and you only have one field in the

Me.lstProviders.RowSource = "SELECT ProvName FROM [Medical Records Request] WHERE [Claim Num]=" & Me.pendclaimnum


change 2 to 1 in the column count and bound column
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What's the name of the "Claim Number" textbox on your Pending Claim form? That's what you should use in the Where section, as Cap has been suggesting.

Also change your ColumnWidths back to 1 ... or just delete the value entirely since you've only now got one column in the listbox.
0
 
c9k9hAuthor Commented:
The Name of the Claim number textbox on the pending claim form is pendclaimnum where the source is PendingClaimNum

I had swapped the bound columns and it was pulling the claim #'s from all of the records in the table.

So theoretically if i use
Me.lstProviders.RowSource = "SELECT ProvName FROM [Medical Records Request] WHERE [Claim Num]=" & Me.pendclaimnum where "pendclaimnum" is the claim# text field on the pending form, this should return all providers bound to that claim# on the "Medical Records Requests" table?
the [Claim Num] is referencing the control source (column) on the Medical Records Request Table, should i chage [Claim Num] to the text box name on the med rec request form? It's actually the same name "Claim Num" but would i do something like Claim_Num?
Just to clarify some other things

ProvName is where the Provider names are being stored on the "Medical Records Requests" table.
Claim Num is where the claim number bound to these providers is being stored on the Med Rec Reqs. table.

So the SQL is pretty straight forward and this should work correct?
should I get rid of the Row source in the properties? (medical records request ) table? I dont think I should need it....


0
 
Rey Obrero (Capricorn1)Commented:
<should I get rid of the Row source in the properties? (medical records request ) table?> yes,

you can use instead
SELECT distinct ProvName FROM [Medical Records Request]
0
 
c9k9hAuthor Commented:
Okay, I'm really stumped here so one more time:

Im using SELECT distinct ProvName FROM [Medical Records Requests] for the row source of the list box.
For the After Update event of the Claim # Text field on the pending form/table I'm using:
Private Sub pendclaimnum_AfterUpdate()
Me.lstProviders.RowSource = "SELECT ProvName FROM [Medical Records Request] WHERE [Claim Num]= " & Me.pendclaimnum
End Sub

Where ProvName is the column in Med Rec Reqs, Claim Num is another column in Med Rec Reqs, and pendclaimnum is the text field on the pending form/table.

Whats happening:

When the form loads, it generates a list of all providers from all records. If i update the claim number on the pending claim "pendclaimnum" (after update) the list clears.


0
 
Rey Obrero (Capricorn1)Commented:
<For the After Update event of the Claim # Text > 
why are you using  {  Claim # } here ?
is that the actual name of the field from table [Medical Records Request]?


to clear this once and for all
post the field names in table [Medical Records Request]

what is the data type of [Claim Num] / [Claim #]  ? Number or Text ?
0
 
Rey Obrero (Capricorn1)Commented:
better if you will post an image of table [Medical Records Request] in design view
0
 
c9k9hAuthor Commented:
The data type is Text.

There are more field names in this table, but these are the relevant ones.


medtable.jpg
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
we are using   FROM [Medical Records Request]  in the row source where as the name of the table is  [Medical Records Requests]



try this


Private Sub pendclaimnum_AfterUpdate()
Me.lstProviders.RowSource = "SELECT ProvName FROM [Medical Records Requests] WHERE [Claim Num]= '" & Me.pendclaimnum &"'"
End Sub
0
 
c9k9hAuthor Commented:
Unbelievable! You know, I just thought that because it was text that i would add the "'"'s and thought i was on to something, but i guess you need to have the correct table name!

It works perfectly, thank you SOOOO much, this was driving me absolutely crazy!

One more *small* thing... i have a field on the pending table set up for the control source of the list box, however, I'm assuming it wont show, as this info is not being added to the record..... what would be the quickest fix you think?
0
 
c9k9hAuthor Commented:
Come to think of it... i can probably just use that same code in a query driven report right? that would probably be the easiest way to do it....
0
All Courses

From novice to tech pro — start learning today.