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