eliminate duplicates

[holly]

I have a table with 7 columns of team member names. I need a count of all
team members, but need to eliminate duplicate names. How do I query seven
different columns in a table and eliminate the dups?

 

[KARL DEWEY]

First fix your table structure so that names are all in the same field
(column to you).
Why are they in didderent fields - is each field for a different position?
Then use a field for name and a field named position that has position as
data in the field.

 

[John W. Vinson]

I have a table with 7 columns of team member names.

Well... then you have an incorrectly designed table. That's spreadsheet
thinking, not relational thinking!

What is the rest of the structure of this table? What is its purpose?

I need a count of all
team members, but need to eliminate duplicate names. How do I query seven
different columns in a table and eliminate the dups?

A "Normalizing Union" query can do this. You need to use the SQL window to do
it, and I don't know your fieldnames of course, so you'll need to adapt this.
Create a new query without selecting any table, and then choose View... SQL;
you'll see a text window with just the word SELECT; in the upper left.

Edit it to

SELECT Name1 FROM yourtable WHERE Name1 IS NOT NULL
UNION
SELECT Name2 FROM yourtable WHERE Name2 IS NOT NULL
UNION
<etc through all seven fields>

This will assemble all the names from all seven fields in all records and will
remove duplicates.

You should really consider normalizing your table structure to avoid this
problem! "Fields are expensive, records are cheap" - rather than having one
(guessing here) Team table with seven membername fields, you should consider
three tables:

Teams
TeamID
<information about the team as a whole>

Players
PlayerID
LastName
FirstName
<other biographical data>

TeamPlayers
TeamID <link to Teams>
PlayerID <link to Players>
Position <or other info about this player on this team>

John W. Vinson [MVP]