Query to Identify Excluded Codes Billed Together?

[LeslieMB]

Long story short: I work in healthcare, and I need to be able to identify when a patient has had two billing codes that cannot be used together entered together. I was originally trying to set this up in Excel, but Excel can't handle the amount of data I'm working with.

I have two queries that were automatically generated via the analyze table wizard. The first one contains the patients' names, which facility the patient is in, and which billing codes have been entered for that patient. The other query contains two columns with pairs of billing codes that cannot be billed together for one patient.

My objective is to build a query that checks to see if the patient and facility names match, then checks the billing codes to see if any of them are not able to be billed together.

Here's what the Patient/Facility/Code query looks like (with dummy information):

ID	Lookup to Facility	Facility	Lookup to Patient Names	Patient	Lookup to ICD-10 Codes	Code
1	Facility 1	Facility 1	Patient 1	Patient 1	F41.9	F41.9
2	Facility 1	Facility 1	Patient 1	Patient 1	R25.2	R25.2
3	Facility 1	Facility 1	Patient 1	Patient 1	R25.3	R25.3
4	Facility 1	Facility 1	Patient 1	Patient 1	F32.9	F32.9
5	Facility 2	Facility 2	Patient 2	Patient 2	I63.9	I63.9
6	Facility 2	Facility 2	Patient 2	Patient 2	S72.141D	S72.141D
7	Facility 2	Facility 2	Patient 2	Patient 2	I69.391	I69.391
8	Facility 2	Facility 2	Patient 2	Patient 2	R13.12	R13.12
9	Facility 2	Facility 2	Patient 2	Patient 2	I10	I10

And here's what the query that identifies codes that can't be billed together looks like:

ID	Lookup to Main Codes	Main Code	Excluded Codes
1	A41.9	A41.9	A40.0
2	A41.9	A41.9	A40.1
3	A41.9	A41.9	A40.3
4	A41.9	A41.9	A40.8
5	A41.9	A41.9	A40.9
6	A41.9	A41.9	R78.81

Any thoughts?

Thank you!

[NTC]

In regard to codes that can't be billed together: is not this a permanent static list? so it should be a fixed table rather than a query. I ask because this part is unclear to me. Let's call this the NoNo list.

In regard to your first query you have 2 columns with identical info: Look Up (ICD...) and Code; do these ever differ? and more importantly which field is to be compared with the NoNo list?

[LeslieMB]

In regard to codes that can't be billed together: is not this a permanent static list? so it should be a fixed table rather than a query. I ask because this part is unclear to me. Let's call this the NoNo list.

In regard to your first query you have 2 columns with identical info: Look Up (ICD...) and Code; do these ever differ? and more importantly which field is to be compared with the NoNo list?

Thanks for your reply! To clarify:

The NoNo list was imported from a list I created in Excel. In Excel, it is two columns: one with the main code, and one with excluded codes. This gets complicated because some of the codes have over 2,000 codes they cannot be billed with, but many of those over 2k codes can be billed with each other. The Excel column has the first code repeated in the next row for every distinct code it cannot be billed with, so I guess Access decided that it needed to be imported as a couple of tables and a query to pull it all together.

This is a small sample of the NoNo list in Excel:

D64.9	C92.40
D64.9	C92.41
D64.9	C92.42
D64.9	C92.50
D64.9	C92.51
D64.9	C92.52
D64.9	C92.60
D64.9	C92.61
D64.9	C92.62
D64.9	C92.90
D64.9	C92.91
D64.9	C92.92
D64.9	C92.A0
D64.9	C92.A1
D64.9	C92.A2
D64.9	C92.Z0
D64.9	C92.Z1
D64.9	C92.Z2
D64.9	D46.0
D64.9	D46.1
D64.9	D46.20
D64.9	D46.21
D64.9	D46.22
D64.9	D46.4
D64.9	D46.9
D64.9	D46.A
D64.9	D46.B
D64.9	D46.C
D64.9	D46.Z
E03.9	E00.0
E03.9	E00.1
E03.9	E00.2
E03.9	E00.9
E03.9	E01.0
E03.9	E01.1
E03.9	E01.2
E03.9	E01.8
E03.9	E02

The entire list is over 43,000 rows long.

The other query was also imported from Excel. This report only exports from the billing software to Excel, where I have to fix the absolutely terrible formatting, THEN import that Excel sheet into Access. In Excel, the relevant columns are Facility, Patient, and Code. Each row is a different billing code, and the patient and facility names are repeated. Here's an example, information redacted for HIPAA purposes:

Facility	Patient	Code
Facility1	Patient1	F41.9
Facility1	Patient1	R25.2
Facility1	Patient1	R25.3
Facility1	Patient1	F32.9
Facility1	Patient1	G20
Facility1	Patient1	R25.1
Facility1	Patient1	E11.9
Facility1	Patient1	R25.9
Facility1	Patient1	R26.9
Facility1	Patient1	F29
Facility1	Patient1	Z99.3
Facility1	Patient1	R25.1
Facility1	Patient2	I63.9
Facility1	Patient2	S72.141D
Facility1	Patient2	I69.391
Facility1	Patient2	R13.12
Facility1	Patient2	I10
Facility1	Patient2	I77.1
Facility1	Patient2	R13.12
Facility1	Patient2	M62.511
Facility1	Patient2	M62.521
Facility1	Patient2	R26.89
Facility1	Patient2	I69.928
Facility1	Patient2	M25.551
Facility1	Patient2	R29.6
Facility2	Patient3	I10
Facility2	Patient3	R60.0
Facility2	Patient3	I73.9
Facility2	Patient3	I48.91
Facility2	Patient3	I50.20
Facility2	Patient3	I87.2
Facility2	Patient3	M25.472
Facility2	Patient3	R60.0
Facility2	Patient3	R26.89
Facility2	Patient3	M62.521

What I need to do is have some way to easily compare the pairs of codes that cannot be billed together from the NoNo list, to the codes that are billed for each patient in order to identify if one patient has had two or more excluded codes billed together. I tried to do it in Excel, but Excel couldn't handle it.

I'm open to re-arranging the data, or changing whatever. I'm very new to Access, and no one else in the company knows how to use it, so any advice would be greatly appreciated.

[NTC]

I've done a good bit of work involving ICD codes for some reason. So I appreciate the volumes involved. In the most general terms:
a. you have the code list per unique patient (whether table, query - not important at this moment); call that PCodes [make it distinct so the same code doesn't repeat for a patient] this has 2 fields: Patient & Code
and you are needing to check that there is no invalid coding as per the NoNoList. This is 2 column: Code & BadCode
b. make query with PCodes and NoNoList:join PCodes to NoNoList on the code field: save this as PBadCodes. This is 3 column: Patient Code BadCode
c. make query with PCodes and PBadCodes make 2 joins: Patient to Patient and PCodes.Code to PBadCodes.BadCode: call this GoodAndBad

If all the coding was done correctly the GoodAndBad query result will be no records because there will be no matches. Where there is a match there is a problem.

Now this is an outline - the implementation of this depends on whether you are doing 1 patient at a time during data input or doing an overall review of multiple patients as a report - - but this is the idea.

[LeslieMB]

YES! This seems to work! I had gotten close to that, but didn't make it to the third step. Thank you so much!