Duplicate Info in a Field

  • Thread starter Thread starter Jill
  • Start date Start date
J

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.
 
Try this --
SELECT [Address], Count([Name]) AS NameCount
FROM YourTable
WHERE Count([Name]) >1
GROUP BY [Address];
 
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
 
Karl,

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

Any other idea?

Thank you.

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

--
Build a little, test a little.


Jill said:
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.
Click to expand...
Click to expand...
 
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.
 
Try this --
SELECT [Address], Count([Name]) AS NameCount
FROM YourTable
GROUP BY [Address]
HAVING Count([Name]) >1;

--
Build a little, test a little.


Jill said:
Karl,

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

Any other idea?

Thank you.

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

--
Build a little, test a little.


Jill said:
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.
Click to expand...
Click to expand...
Click to expand...
 
After a little bit of tweaking to make it work for my table, it worked. Super!

Thank you.

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

--
Build a little, test a little.


Jill said:
Karl,

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

Any other idea?

Thank you.

KARL DEWEY said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top