Ok, so don't answer that first one.

I've been beating my head on what's probably an old chestnut of a database question and I cannot find or figure out the answer. So, I come begging help from the mighty crania of the empegBBS, fount of all knowledge!

The task seems simple enough (when I started it three days ago). I have a source data table - with many records. Each of these records is divided into 'description' and 'data' fields. There are multiple description fields and multiple data fields, some of each type contain NULL values.

Some (many) of the data fields, when considered alone without the description fields, are duplicate groups. There are no duplicate groups of description fields when they are considered alone (there's no two sets of data for any one unique description is another way to put it).

What I'm attempting to do is to create an data index table which lists description values next to a data group index number. That way I can see which groups of descriptions share common data values.

I started off using Joins, but then learned that NULL entries cannot be used for comparisons in a join, so no love there.

Indexes seem to do what I want when considered 'Unique' but not 'Primary'. But how do I substantiate and index value into a concrete field value? So, I'm stuck there.

I suppose I could do this in multiple steps:
Step 0: Use a 'Select Distinct' query to get my data groups into a table with an autonumbering field to get my index numbers.
Step 1: Use an update querty to change all NULL entries to a dummy string (like 'XYXYXYXY')
Step 2: Inner join to get index values in a table via a query.
Step 3: Set all step 1 dummy text back to NULL values using a second update query.

This seems like a big hack and doesn't seem very elegant (although it should work just fine).

I've attached a trivial sample (zipped) Access file. This isn't my real data, but it should be helpful as an example.

Tables:
Raw_Data - this is my source table
Distinct_Data_Table - this is a table I created by running the Distinct_Data_Query.
Desired_Output_Table - this is a hand-constructed version of what I'm aiming for.

The real data set runs about 56,000 records and has 14 data fields and three description fields.

Any help is very much appreciated.

-Zeke