Multiple Records

[Luis]

Good morning:

I have been trying to figure it out, but I do not know
what else I can do to solve it.

I have the following query in which I would like to see
each record once instead of multiple times with the same
information. I tried grouping them, but I could not get
it to work-Maybe I was doing it wrong. The following is
my query's SQL:

SELECT tbl_diagnosis.Diagnosis,
tbl_disease_protocols_and_standard_dose.Protocol,
tbl_protocols_detail_hcpcs.HCPCS,
tbl_protocols_detail_hcpcs.TYPE,
tbl_protocols_detail_hcpcs.[Dose per m2],
tbl_protocols_detail_hcpcs.[Avg m2],
tbl_protocols_detail_hcpcs![Avg m2]*[Dose per m2] AS
Dose, qry_profit_analysis_all_providers.[2004 Billing
Unit] AS [Billing Unit], [Dose]/[Billing Unit] AS [Billed
Units], [Dose]*tbl_infusion_constants![BILLED UNITS] AS
[Infusion Billed Units], tbl_fee_schedules.PROVIDER,
tbl_fee_schedules.ALLOWABLE,
qry_profit_analysis_all_providers.[Cost per mg], [Billed
Units]*tbl_fee_schedules!ALLOWABLE AS [Total Allowable],
[Infusion Billed Units]*tbl_fee_schedules!ALLOWABLE AS
[Infusion Allowable], [Dose]*[Cost per mg] AS [Total
Cost], [Total Allowable]-[Total Cost] AS Profit,
[Infusion Allowable] AS [Infusion Profit]
FROM tbl_fee_schedules RIGHT JOIN ((((tbl_diagnosis INNER
JOIN tbl_disease_protocols_and_standard_dose ON
tbl_diagnosis.Diagnosis =
tbl_disease_protocols_and_standard_dose.Diseases) INNER
JOIN tbl_protocols_detail_hcpcs ON
tbl_disease_protocols_and_standard_dose.Protocol =
tbl_protocols_detail_hcpcs.Protocols) LEFT JOIN
tbl_infusion_constants ON
tbl_protocols_detail_hcpcs.HCPCS =
tbl_infusion_constants.HCPCS) LEFT JOIN
qry_profit_analysis_all_providers ON
tbl_protocols_detail_hcpcs.HCPCS =
qry_profit_analysis_all_providers.[2004 HCPCS]) ON
tbl_fee_schedules.[SERVICE CODE] =
tbl_protocols_detail_hcpcs.HCPCS
WHERE
(((tbl_disease_protocols_and_standard_dose.Protocol)
Like "TAC*" Or
(tbl_disease_protocols_and_standard_dose.Protocol)
Like "TEC*") AND ((tbl_fee_schedules.PROVIDER)=[Which
PROVIDER would you like to see?]))
ORDER BY
tbl_disease_protocols_and_standard_dose.Protocol,
tbl_protocols_detail_hcpcs.TYPE;

When I tytpe Medicare it gives Medicare values, but it
repeats it several times...

Please help. Thank you so much in advance.

Luis

 

[John Vinson]

When I tytpe Medicare it gives Medicare values, but it
repeats it several times...

Try opening the query in design view; view its Properties; and set the
Unique Values property to Yes. Or, equivalently, edit the SQL so the
first line starts

SELECT DISTICNT


John W. Vinson[MVP]

 

[Luis]

Hello John:

It worked. The only possible issue is if a record that
pertains to any of the insurances I have (Ex. Medicare,
United) it will not show it...it only displays those who
have values. HOw can I overcome this? Is there a way?

Thank you.

 

[John Vinson]

Hello John:

It worked. The only possible issue is if a record that
pertains to any of the insurances I have (Ex. Medicare,
United) it will not show it...it only displays those who
have values. HOw can I overcome this? Is there a way?

I guess I don't understand. Do you want to show data which does not
exist?? YOu've got a Left Join to Providers so it should show the
record even if there is no data in that table. You may need to
experiment with Inner Joins and Left Joins to pull the data that you
want.

John W. Vinson[MVP]