Help to identify sets of distinct unordered values 1

[teach314]

hi - I am struggling to do the following:

consider a table like the one below. The actual table has 12 columns and over 900 rows. The values in each row have no 'doubles'.[tt]

ID F1 F2 F3 F4 F5 F6
1 12 15 8 19 34 21
2 18 10 29 11 77 9
3 34 8 12 15 21 19
4 8 15 19 21 34 12
5 1 12 54 45 99 33
6 1 33 12 99 45 54[/tt]

I want to find rows that have a distinct set of the 6 values, regardless of order. In the example given, rows 1, 3 and 4 all have the same 6 values. Similarly, rows 5 and 6 share the same 6 values. Row 2 has no matches.

Ideally, the output would show the values in each ROW descending from LEFT to RIGHT. Also, it would be nice if the ID column showed the first occurrence for each distinct set of 6 values. (as shown below)[tt]

ID T1 T2 T3 T4 T5 T6
1 34 21 19 15 12 8
2 77 29 18 11 10 9
5 99 54 45 33 12 1[/tt]

many thanks for any help. I realize that the tables aren't in normalized form, but I could convert them if it would help solve the problem.

 

[dhookom]

I would first create a union query [TT_quniTeach314] like:

SELECT ID, F1 AS TheVal, 1 AS Fld
FROM TT_Teach314
UNION ALL
SELECT ID, F2, 2
FROM TT_Teach314
UNION ALL
SELECT ID, F3, 3
FROM TT_Teach314
UNION ALL
SELECT ID, F4, 4
FROM TT_Teach314
UNION ALL
SELECT ID, F5, 5
FROM TT_Teach314
UNION ALL SELECT ID, F6, 6
FROM TT_Teach314;

Then create a ranking query [TT_qrnkTeach314] to order the records for each ID:

SELECT TT_quniTeach314.ID, TT_quniTeach314.TheVal, Count(TT_quniTeach314_1.ID) AS CountOfID
FROM TT_quniTeach314 AS TT_quniTeach314_1 INNER JOIN TT_quniTeach314
 ON TT_quniTeach314_1.ID = TT_quniTeach314.ID
WHERE (((TT_quniTeach314.TheVal)<=[TT_quniTeach314_1].[TheVal]))
GROUP BY TT_quniTeach314.ID, TT_quniTeach314.TheVal
ORDER BY TT_quniTeach314.ID, Count(TT_quniTeach314_1.ID);

Then create a crosstab query [TT_qxtbTeach314]

TRANSFORM Min(TT_qrnkTeach314.TheVal) AS MinOfTheVal
SELECT TT_qrnkTeach314.ID
FROM TT_qrnkTeach314
GROUP BY TT_qrnkTeach314.ID
PIVOT "T" & [CountOfID];

Finally create a totals query with SQL of:

SELECT Min(TT_qxtbTeach314.ID) AS MinOfID, TT_qxtbTeach314.T1, TT_qxtbTeach314.T2,
 TT_qxtbTeach314.T3, TT_qxtbTeach314.T4, TT_qxtbTeach314.T5, TT_qxtbTeach314.T6
FROM TT_qxtbTeach314
GROUP BY TT_qxtbTeach314.T1, TT_qxtbTeach314.T2, TT_qxtbTeach314.T3, TT_qxtbTeach314.T4,
 TT_qxtbTeach314.T5, TT_qxtbTeach314.T6;

Duane
Hook'D on Access
MS Access MVP

 

[teach314]

hey dhookum, that is a really thorough and educational solution. I tried it on my large tables and things everything worked without a hitch. much thanks for your time.

I'm still new enough to SQL that I really have to fight the urge to see data in non-normalized 'spreadsheet' type tables. But I see that you started by using a UNION query to normalize the data, did the needed work, then used a crosstab query to put things back into 'spreadsheet' format.

I have a quick question. after your ranking query, the data looks like this:

ID	TheVal	CountOfID
1	34	1
1	21	2
1	19	3
1	15	4
1	12	5
1	8	6
2	77	1
2	29	2
2	18	3
2	11	4
2	10	5
2	9	6
3	34	1
3	21	2

... etc...36 rows in total

so, if I forget about using the crosstab qry to convert back to 'spreadsheet' format, what is the best way to show all DISTINCT sets of TheVal while still in normalized form? The output would look exactly like above, but showing only rows having ID = 1, 2 or 5 as shown in my original posting. (18 rows in total)

TQ,teach314

 

[dhookom]

You could take the final query and join it to the normalizing union query.

Duane
Hook'D on Access
MS Access MVP

 

[teach314]

I think you must mean "... and join it to the ranking query". In any case, I'm not yet able to get this to join properly to the final query. I'll keep trying.

 

[dhookom]

I would join to the MinOfID to either the union query or the ranking query.

Duane
Hook'D on Access
MS Access MVP

 

[teach314]

ok, thanks dhookum - your suggestion works well (setting ID of ranking query to MinOfID in the final query.) The only thing that seems odd about doing it this way was that the final query invokes a non-normalized crosstab query. I guess I was hoping we could get the final results in normalized form from either the ranking query or the union query, both in normalized form, without having to use the crosstab query at all.

many thanks for your help so far!