SQL query to get unique addresses from a list.

April 18, 2007 11:10 AM

Access SQL Query - I have multiple individuals each with an address. I need to make mailing labels, but if two or more people share an address, I would only like one label.

I have a database with student information. Each student record contains the student's first and last names, and their home address, among other details.

The customer wants to print mailing labels for each "family," a concept that the database has no built-in provision for.* So, I'm looking for a query that would simulate this idea of a family.

Criteria:
Though siblings will have the same last name, obviously last names could be shared between families. The best I can think of is to rely on the fact that siblings should have the same home address. Or, to guard against typos, at least the same street number.

How can I write a query to pull out unique street addresses?

* Alternatively, if you have any good ideas about how to implement the concept of a Family in the database, I'd love to hear some of them.

posted by odinsdream to computers & internet (9 comments total)

I think you want to use the DISTINCT operator:

SELECT DISTINCT address, lastname, firstname FROM table
posted by chrisamiller at 11:25 AM on April 18, 2007

Is this what you mean?

SELECT DISTINCT lastname, address FROM students;
posted by gsteff at 11:26 AM on April 18, 2007

To implement the concept of a family, I would split the student table into two tables: One for address information, with a unique ID for each row, and one for students' names and other individual details. The Student table would simply have a column that would store the Address_id that would reference the Address record for that student. If more than one student lives at an address, you still have just one address record, and can use the Address table to generate your labels.

How to generate the name line of the labels would be a bit trickier, but you could write a report that would look for all students at a given address, and iterate through their first names (of which there might be only one of course) and then put their last name, like so:

Greg, Marcia, Peter, Jan, Bobby, & Cindy Brady

You will run into families where not all the siblings have the same last name, incidentally. I know of several just among my circle of friends.
posted by cerebus19 at 11:27 AM on April 18, 2007

If you have the siblings' names stored in the database in 2 seperate fields (for example, [FirstName] and [LastName], you could do something like this:

SELECT DISTINCT
"The " & [LastName] & " Family" As RecipName
, [Address1]
, [City]
, [State]
, [Zip]
FROM
[tblAddress]

As long as the address information and last name are identical only 1 record will be pulled for each address.
posted by mezzanayne at 11:29 AM on April 18, 2007

cerebus19; as far as selecting a name to go with an address when multiple names are available, I can work with the client to come up with some sorting criteria.

I think it would likely be the oldest child. The database has a birthday field, so that would be possible.

As for the suggestion to split the table and make a new Address table, I'm not sure I can figure out how to actually make that happen. For the first child in the family it makes sense, you would enter the address by hand.

Second child, however, is a little trickier - a pull-down menu to pick an address from all of the current recorded addresses would be too cumbersome.
posted by odinsdream at 11:53 AM on April 18, 2007

I wouldn't re-normalize the database at this point if you already have applications/forms that use it. Personally I'd create a new join table with the fields id, attn_to, student1, student2 and have student1 and 2 be foreign keys referring to the original student records.

That way you can pick how you address those 'household' mailings on a case by case basis if you want but just pre-populate them with the surname. They'll be sufficiently slender enough - I would think - that you can give them a quick sanity check by hand.

If you don't need to populate that join table with a single query I'd iterate the full student table after doing a SELECT * FROM students ORDER BY zipcode, streetaddress. Each time you hit a new zipcode during the iteration compare record X to X+1. If the zip is the same and the initial chars of of address1 (from 0..find_first(' ')) are identical, insert it into your join table.

If you do need a single query I suppose you could join table X to table X on X1.zipcode=X2.zipcode, X1.key != X2.key. SQLServer supports the substring function, dunno about access. Anytime that X2.key isn't null you have a duplicate.
posted by phearlez at 12:24 PM on April 18, 2007

Why not do a GROUP BY statement:

SELECT address, firstname, lastname FROM students GROUP BY address

If you want the eldest person to be the one you retreive, throw in an ORDER BY birthdate ASC

The tricky part is you'll need to normalize data entry into the database so that people always enter the same thing (i.e. "Road", not "Rd", "Rd.", etc)...
posted by twiggy at 8:18 PM on April 18, 2007

I'd be skeptical of any results you get from GROUP BY unless those addresses have been run though some kind of validator/normalizer before they're inserted. I don't know for sure but I believe Jet's group by is case-sensitive as well so a line with just Rd vs rd wouldn't be grouped.
posted by phearlez at 10:16 AM on April 19, 2007

All of these are great suggestions. Thanks everyone.
posted by odinsdream at 11:26 AM on April 19, 2007