eliminate duplicates

[holly]

I have a table with 7 different columns of team members on different teams.
I need a count of all team members. How do I query seven different columns
and eliminate dups?

 

[Douglas J. Steele]

You've discovered yet another problem with denormalized tables. If you have
7 different columns, each containing the same basic information, you have a
repeating group.

If you can't go back and redesign your tables, the easiest approach would be
to create a UNION query that normalizes the data so that it's in only 1
column:

SELECT Member1 AS Member
FROM MyTable
UNION
SELECT Member2 AS Member
FROM MyTable
UNION
SELECT Member3 AS Member
FROM MyTable
UNION
SELECT Member4 AS Member
FROM MyTable
UNION
SELECT Member5 AS Member
FROM MyTable
UNION
SELECT Member6 AS Member
FROM MyTable
UNION
SELECT Member7 AS Member
FROM MyTable

Since you're using the UNION statement, all duplicates will be eliminated,
so that the resultant query will return a list of unique members.

 

[holly]

Thank you!

You've discovered yet another problem with denormalized tables. If you have
7 different columns, each containing the same basic information, you have a
repeating group.

If you can't go back and redesign your tables, the easiest approach would be
to create a UNION query that normalizes the data so that it's in only 1
column:

SELECT Member1 AS Member
FROM MyTable
UNION
SELECT Member2 AS Member
FROM MyTable
UNION
SELECT Member3 AS Member
FROM MyTable
UNION
SELECT Member4 AS Member
FROM MyTable
UNION
SELECT Member5 AS Member
FROM MyTable
UNION
SELECT Member6 AS Member
FROM MyTable
UNION
SELECT Member7 AS Member
FROM MyTable

Since you're using the UNION statement, all duplicates will be eliminated,
so that the resultant query will return a list of unique members.