I am trying to collapse a table containing physician data with multiple records per physician down to one record per physician. At present, the data looks something like this:

Physician ID Physician ID Version Payment Class MD Group Insurance Company
-------------- ------------------------ ----------------- ----------- -----------------------
xxxxxxx001 0001 Basic100 GroupA Ins_01
xxxxxxx001 0001 Basic105 GroupA Ins_01
xxxxxxx001 0001 Basic100 MDsRUs Ins_02
xxxxxxx001 0001 Basic105 GroupA Ins_02

xxxxxxx002 0001 Basic100 MDsRUs Ins_01
xxxxxxx002 0001 Basic100 MDsRUs Ins_02

xxxxxxx003 0001 Basic105 GroupA Ins_01
xxxxxxx003 0001 Basic105 GroupA Ins_02

Physician ID - unique ID number for that physician (e.g., for that belly button)
Physician ID Version - modifier added to ID number that allows one physician to have more than one record that reflects their different office locations, their different relationships with different MD groups or hospitals, ...
Payment Class - the predefined set of fees they are paid for their services. These can differ based on version number.
MD Group - the group this physician is affiliated with for that version.
Insurance Company - the insurance company paying the claim

The unique combination of fields that identifies a physician involves all 5 fields. For Physician 002 and 003, there is no problem as each record represents a unique combination of these values. Physician 1 is another story. The first 2 records have the same ID, version, MD Group and Insurance Company, but the Payment Class is different. The third and fourth records continue with the same ID and version, but have changes to MD Group, Payment Class and Insurance Company.

What I would like to do is choose which record's value I keep in the final file for physician 001. I need one record for physician 001 for Insurace Company Ins_01 and another for Ins_02.

There isn't a way to sort the data so I could simply use an SQL statement and keep the last or first value of Payment Class or MD Group. I am used to programming in SAS, where you can access adjacent records while processing the current record. How can I do something like that in Access? Are we talking about using VBA?

I appreciate your insight and wisdom.

Bruno Berszoner
Boston, Massachusetts

(IMG:style_emoticons/default/welcome2UA.gif)

I'm curious why you "need" to have one record per physician? In a well-normalized relational database, you might have one record per physician in the PERSON table, but each separate 'billing' would need/get its own record in a separate table.

More info, please...

Sorry, but this might take a while to explain.

Like most analyses in health care data, I am using data from a transactional system for analytical purposes. Data is extracted from a transactional system and put into a data warehouse based on the values of various tables at that point in time. Over time, retroactive changes are made in the transactional systems that do not necessarily get updated in the data warehouse.

I have claims data that includes the member ID and the member's Primary Care Physician (PCP) ID and version number in addition to the physician providing the service (the servicing provider). Payment is based on the servicing provider, but I need to report out based on the PCP, not the servicing provider. To do this, I need to add the payment class and MD group from the PCP to the claim record.

If all of the data were perfect, you would be right that the normalized data should mean that everything gets pulled correctly. I have the claims data grouped by Member ID, PCP ID, PCP Version and month of the service. Because a physician's information can change over time, I have corresponding physician records by month. Sample tables are below.

Claims Data
Member ID PCP ID PCP Version Insurance Company Month Amount Paid
------------- -------- -------------- ----------------------- ---------- --------------
xxxx3100 xxxx001 0001 ins_01 Jul 2010 $75.00


PCP Data
Physician ID Physician ID Version Payment Class MD Group Insurance Company Month
-------------- ------------------------ ----------------- ----------- ----------------------- ---------
xxxxxxx001 0001 Basic100 GroupA Ins_01 Jul 2010
xxxxxxx001 0001 Basic105 GroupA Ins_01 Jul 2010

If I join the two tables together based on PCP ID -> Physician ID, PCP Version -> Physician ID Version, Insurance Company and Month, the join gives good results if there are no duplicates in PCP table. It also works if there is a record in each table for a given month. That's not my case. I have records where there is a claim for a member with a given PCP in a month for which there is no corresponding PCP record. With the data I am using, we are talking about thousands of unmatched records covering millions of dollars.

So, I am planning to modify my joins to drop the month from the join conditions. This way, I get the PCP's Payment Class and MD Group assigned to the claim. But first, I need to reshape the PCP data to get down to one record per PCP. In most cases, the Pay Class and MD Group do not change for a PCP in different months (that's the reason for adding the version -- if the pay class of MD group changes, then the physician gets a new version number). But I have PCP records where their affiliation with an MD group doesn't change, but the MD Group's data change. Ths generates multiple records for a PCP in a month for a version.

I guess the best way to address this is to go up a level or two and make sure that the MD group information is properly addressed. But that will take a lot of time and make my already large Access DB much larger. Instead, I want to set some rules that will allow me to define which of the multiple records I want to keep. But I would need to be able to compare the current record to the previous and next records in order to do this.

I hope this clarifies things a bit.

Bruno

Bruno

Thanks for the clarification.

I may (?!again?!) be reading too much into your description ... something you said about not having data for each month triggered a thought.

If you first create/use a query that returns all the months, then join that query (left join) to your data, you could get a "by month" reporting, even for months in quey there was no physician/payment data. Would that be more useful in your situation?

That might work. How would I make that happen?