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

I don't have a copy of Access, so I can't see your example, so forgive me if these questions seem stupid.

It sounds like you've got a table that looks like this:

1:A
1:B
1:C
2:D
2:E
3:F
3:G
3:H

and you want to find all the letters that match a particular number, right?

So you just want to know how to construct a query that will give you back a result that shows all of the letters for each number. Basically, in pseudo code:
Code:

---

array = select unique number from table
for each array_element
  select * from table where number = array_element

---

Or are you wanting a new table that will combine all of the letters into a single field so that the numbers become unique?

Code:

---

 Original data set
Descr2    Descr1    Data1    Data2    Data3
          Alpha       1        2        X
          Bravo       3        4        Y
          Charlie     1                 X
          Delta       1        2        X
          Sigma       5        9     
1         Sigma       5        9        Z
=======================================
Then sorted by data1,data2,data3  and adding an index that increments with changes in the data.

Descr2    Descr1    Data1    Data2    Data3    index
          Alpha      1         2         X       1
          Delta      1         2         X       1
          Charlie    1                   X       2
          Bravo      3         4         Y       3
1         Sigma      5         9         Z       4
          Sigma      5         9                 5
========================================
Finally list the description pairs  that exhibit common data sets (index values)
1 -  |Alpha,  |Delta
2 -  |Charlie
3 -  |Bravo
4 - 1|Sigma
5 -  |Sigma

---

That's how I understand the problem.

Could you concatenate all the data fields together (or substring of each) to make a unique key and then join or create a numeric index on that?

qryUnique:
SELECT [Raw_Data].[Descr2], [Raw_Data].[Descr1], [Raw_Data].[Data1], [Raw_Data].[Data2], [Raw_Data].[Data3], Trim([data1]) & Trim([data2]) & Trim([data3]) AS INDX
FROM Raw_Data;

qryOutput:
SELECT DISTINCT qryUnique.Descr1, qryUnique.Descr2, qryUnique.INDX
FROM qryUnique;

Bitt,
The null entries in the data screw up the data=index_data since data(NULL)<>index_data(NULL). Your question makes perfect sense as you didn't have the data.

Mach,
Hadn't thought of concatenation. It should work great, but I don't have mathematical certainty that two concatenations wouldn't be equal (for example to entries with adjacent data pairs of (10,.234 = 10.234) and (1,0.234 = 10.234). Highly unlikely with so many data records - I know. However, this is the easiest to code, and I can check by looking at the total number or records of the resulting index table - they should equal the number of records in the source table.

gbeer,
Very cool. In fact I've already got a similar module written to take single year entries into ranges (I have discontinuous year ranges for a given description - so a straight up min/max group-by query didn't work). The code will be a bit longer because of the large quantity of data fields, but it should be mathematically bullet-proof for all data sets.

Thanks guys. I really appreciate the help.

-Zeke

Many moons after the first post and I'm still deep in Access. I found the following article over at Tech Republic very useful. As the article title implies it is 10 tricks for handling null values in Microsoft Access.

I thought someone else might also find this useful.

-Zeke