eliminate duplicates

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?

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.
--
KARL DEWEY
Build a little - Test a little


"holly" wrote:

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?

On Fri, 18 May 2007 07:52:02 -0700, holly
wrote:

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]