Grouping Records by Household

[Guest]

Alrighty then. I have a table that has a mass of individual mailing
addresses, and I want them to be grouped by household.

For Instance:

Transform This:
First Last Address City State
John Jefferson 123 Street Denver CO
Betty Jefferson 123 Street Denver CO
Frank Bell 345 Avenue Greeley CO

Into This:

Both First Last Address City
State
John and Betty John Jefferson 123 Street Denver CO
John and Betty Betty Jefferson 123 Street Denver CO
Frank Frank Bell 345 Avenue Greeley CO



See what I need? I've been racking my brain but I can't figure out how to
write this.

 

[Tom Ellison]

Dear In need:

Well, you could associate together everything with the same Address, City,
and State and having the same Last (name). This would work in theory. I'm
not sure how you're goint to get " John and Betty" rather than "Betty and
John" but perhaps that isn't essential.

What do you want to do when there are 3 different first names with the same
Last/Address/City/State?

What probably is never going to work is to match them when the address of
one is 123 ST instead of 123 Street. Even 123 Street (two spaces) will
blow the matching away.

What do you thing the chances are that the people who typed this in always
used Street and never ST? That they never put in two spaces? That they
always spell the street name, last name, and city correctly?

You may have an error rate well in excess of 10% in trying to do this. Some
testing may be in order. Consider a hierarchy of matching to help with the
testing. Let's assume the State, City, Last name, and the street number are
accurate. If you sort on those columns, in that order, do you get good
matches when you visually scan the results? If so, maybe you're good to go
on putting the names together.

Tom Ellison

 

[Gary Walter]

Alrighty then. I have a table that has a mass of individual mailing
addresses, and I want them to be grouped by household.

For Instance:

Transform This:
First Last Address City State
John Jefferson 123 Street Denver CO
Betty Jefferson 123 Street Denver CO
Frank Bell 345 Avenue Greeley CO

Into This:

Both First Last Address City
State
John and Betty John Jefferson 123 Street Denver CO
John and Betty Betty Jefferson 123 Street Denver CO
Frank Frank Bell 345 Avenue Greeley CO

Hi in need,

PMFBI

I'd first change field names that are
reserved words (say "FirstName"
and "LastName").

Then, as Tom pointed out, if you manage
to have data where all the "group" fields
are indeed exactly the same, and, when that
happens, you only have 2 records in a group,
one strategy might be:

SELECT
First(FirstName) & " and " & Last(FirstName) As Both,
First(FirstName) As OneFirstName,
LastName,
Address,
City,
State
FROM yourtable
GROUP BY
LastName,
Address,
City,
State
WHERE
Count(*)=2;

then UNION that with

SELECT
FirstName As Both,
FirstName,
LastName,
Address,
City,
State
FROM yourtable
GROUP BY
FirstName,
LastName,
Address,
City,
State
WHERE
Count(*)=1;

The first query won't give you
*two* records for each group though.

Apologies again for butting in,

gary

 

[Gary Walter]

Sorry.....
the "second query" would need to be:

SELECT
First(FirstName) As Both,
First(FirstName) As OneFirstName,
LastName,
Address,
City,
State
FROM yourtable
GROUP BY
LastName,
Address,
City,
State
WHERE
Count(*)=1;

 

[Guest]

Thanks guys, yes the error rate is a little high, but this gets most of them.
Thanks a lot.