Removing Duplicate Fields

antonides · November 29th, 2003, 11:13 PM

Hi All,

I've been working on this problem for 2 days now and I can't find a solution

I have a database in which looks a little like this

ID Name Address
1 anton 32 algi rd
2 lesly 24 smith st
3 bianca 32 algi rd
4 brian 92 faraday cres
5 bill 32 algi rd.

I want a query that will enable me to display all the names and addresses however if the Address is a duplicate (as in ID's 1, 3, 5) I don't want it to display the address 3 times. I only want it to display once.

However it gets more complicated I want one of the names (either anton bianca or lesley doesn't matter which) to show up with the address.

In other words I want the Query to output

ID Name Address
1 anton 32 algi rd
2 lesly 24 smith st
4 brian 92 faraday cres.

I have tried the

SELECT DISTINCT Address FROM Details

However I need the name and ID's to show up with the address!

Thanks very much in advance,

Anton

pmmgpgp · November 29th, 2003, 11:54 PM

Anton

First create a "totals" Query with
fields ID and Address; using Min in
the Total block for ID.

This will return something like:
ID Address
1 32 algi rd
2 24 smith st
4 92 faraday cres

Now build a second Query tying
the above back to Your origional
table to get A name for each.

Dave

Steven · December 1st, 2003, 07:37 PM

Following on from Dave's response....
You only need one query, which selects the Min ID, Min Name and GroupBy Address,
For example:

Code:

SELECT Min(id) AS id, Min(Name) AS Name, Address
FROM YourTable
GROUP BY Address;

Steven

I am a loud man with a very large hat. This means I am in charge