Duplicate Info in a Field

[Jill]

I have a table where there may/may not be duplicate information in a field.
How can I create a query to find out? For example, there may be several
different names who share the same address. I would like to find out which
address is shared by more than one person.
Thank you in advance.

 

[KARL DEWEY]

Try this --
SELECT [Address], Count([Name]) AS NameCount
FROM YourTable
WHERE Count([Name]) >1
GROUP BY [Address];

 

[Jerry Whittle]

That can be very difficult. I know from experience. For example if an address
is entered as "123 Main Street" and "123 Main St" for the same city, Access
thinks there are more than one address.

How does the data look in your tables? Do you have a seperate table for
addresses or something like below in one table? If so it would be very
difficult as all three addresses could be for the same building.

FName LName Street City State Zip
Jim Jones 123 Main Street Clovis NM 88101
Tim Jones 123 Main St Clovis NM 88101
Jill Jones 123 N. Main St. Clovis NM 88101

 

[Jill]

Karl,

It says "Cannot have aggregate function in WHERE clause."

Any other idea?

Thank you.

Try this --
SELECT [Address], Count([Name]) AS NameCount
FROM YourTable
WHERE Count([Name]) >1
GROUP BY [Address];

--
Build a little, test a little.

I have a table where there may/may not be duplicate information in a field.
How can I create a query to find out? For example, there may be several
different names who share the same address. I would like to find out which
address is shared by more than one person.
Thank you in advance.

 

[Jill]

Jerry,

I used an example because I thought it would be easy to explain what I want
to do. Acutally what I need to do is to find out if any class is taught by
more than one instructor. So please replace the address as a course number
and the names as instructors' names or their ID numbers.

Thank you.

 

[KARL DEWEY]

Try this --
SELECT [Address], Count([Name]) AS NameCount
FROM YourTable
GROUP BY [Address]
HAVING Count([Name]) >1;

--
Build a little, test a little.

Karl,

It says "Cannot have aggregate function in WHERE clause."

Any other idea?

Thank you.

Try this --
SELECT [Address], Count([Name]) AS NameCount
FROM YourTable
WHERE Count([Name]) >1
GROUP BY [Address];

--
Build a little, test a little.

I have a table where there may/may not be duplicate information in a field.
How can I create a query to find out? For example, there may be several
different names who share the same address. I would like to find out which
address is shared by more than one person.
Thank you in advance.

 

[Jill]

After a little bit of tweaking to make it work for my table, it worked. Super!

Thank you.

Try this --
SELECT [Address], Count([Name]) AS NameCount
FROM YourTable
GROUP BY [Address]
HAVING Count([Name]) >1;

--
Build a little, test a little.

Karl,

It says "Cannot have aggregate function in WHERE clause."

Any other idea?

Thank you.

Try this --
SELECT [Address], Count([Name]) AS NameCount
FROM YourTable
WHERE Count([Name]) >1
GROUP BY [Address];

--
Build a little, test a little.


:

I have a table where there may/may not be duplicate information in a field.
How can I create a query to find out? For example, there may be several
different names who share the same address. I would like to find out which
address is shared by more than one person.
Thank you in advance.