Need help with multiple queries I think

[Trogg]

I have received a data request that I think will require 2 queries to complete. I have been asked to provide a list of accounts and insurance payments. The list will be limited to accounts that have SC Medicaid as a secondary insurance. That part is easy and the pertinent criteria and data elements are exampled as follows:

InsType InsuranceOrder InsRcpt
MDCD.SC 2 $3,273.59

I created the query to pull the above information by using MDCD.SC and 2 as criteria to get just those records. However, the requestor also wants to see from the record set produced above the primary InsType and the InsRcpt from it. Of course the InsuranceOrder for it would be 1. How might I accomplish this using what I have? Thanks in advance!

 

[BigRed1212]

I think you might need to provide a little more information on your database structure, a larger set of sample data, and a desired result that shows how things fall out.

That would help me and I'm sure it would help the others here who are a lot better at this than me.

 

[Trogg]

Certainly... the query that has been created comes from 4 tables... two insurance related tables, a financial table, and a demographic table all joined on the same unique field. Here is a larger sample:

AccountNumber AcctType Secondary InsuranceOrder InsRcpt
V001804193 OP.RREF MDCD.SC 2 $77.22
V001804222 OP.RREF MDCD.SC 2 $76.48
V001804311 OP.ER MDCD.SC 2
V001804538 IP MDCD.SC 2
V001804562 OP.REF MDCD.SC 2 $0.00
V001804693 OP.REF MDCD.SC 2 $27.63
V001804719 OP.REF MDCD.SC 2

What I would like to see is the above but also showing two additional columns at the end with the Primary insurance type and the InsRcpt for it. That information is also in the same insurance tables as the above but it's order would be "1". So I need to use the above record set due to the criteria of the first query but also show for that record set what the Primary insurance type was and how much was received from it for the record set. I hope this explains it better.

 

[randy700]

One way would be to create another query identical to the first, except WHERE InsuranceOrder = 1.
Then, a 3rd query, something like this...

SELECT Q1.AccountNumber, Q1.AcctType, Q1.InsType As Secondary, Q1.InsRcpt As SecRcpt, Q2.InsType As Primary, Q2.InsRcpt As PriRcpt FROM YourFirstQuery Q1 INNER JOIN YourSecondQuery Q2 ON Q1.AccountNumber = Q2.AccountNumber

Randy