Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...On your site I feel quite confident that the contacts and feedback will make my life a little less hectic..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL Forum

Help to identify sets of distinct unordered valuesHelpful Member! 

thread701-1688505
Read More Threads Like This One
teach314 (TechnicalUser)
17 Jul 12 22:04
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'.

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

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)

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

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 (Programmer)
18 Jul 12 1:28
I would first create a union query [TT_quniTeach314] like:

CODE --> SQL

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:

CODE --> SQL

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]

CODE --> SQL

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:

CODE --> SQL

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 (TechnicalUser)
18 Jul 12 18:05
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:

CODE

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 (Programmer)
18 Jul 12 18:26
You could take the final query and join it to the normalizing union query.

Duane
Hook'D on Access
MS Access MVP

teach314 (TechnicalUser)
18 Jul 12 22:23
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.
Helpful Member!  dhookom (Programmer)
18 Jul 12 22:55
I would join to the MinOfID to either the union query or the ranking query.

Duane
Hook'D on Access
MS Access MVP

teach314 (TechnicalUser)
18 Jul 12 23:30
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!
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Queries and JET SQL

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close