Can I do this?

[Laurel]

I have an unnormalized database that isn't mine - I can't re-structure it.
It contains the names and addresses of all the members of our congregation,
and I've been asked to generate a directory, with the address of each
household showing only once, and the names of the children falling under the
parents. It needs to be sorted by the last name of the HHD_Label row.
Previously, we simply listed them alphabetically, last name, first name, and
families with the same last name were all over the map (to mix a metaphor).
A "household" is all the people living at the same address. The thing that
has me the most stumped, is how to gather the households together in the
result set, but still sort by the last name of the HHD_LabelTag row.

I'm willing to add some columns of information to try to deal with this.
But I can't figure out the SQL. Can anyone help?

Desired output

<Household_Directory_Name> <address> <city> <state>
<zip> <phone> <e-mail>
<other household member (first_name, last_name>
<phone> <e-mail>
<other household member>
<phone> <e-mail>
<other household member>
<phone> <e-mail>

Here are the relevant database columns - one row per person

Household key (a number - the same for all members of a household)
HHD_LabelTag (set to yes for one member of the family - currently used to
mail to households using just the last name)
Househould_Directory_Name
Other_Household_Member (I think I need this so the spouse doesn't show up in
a list with the children.)
first_name
last_name
address
city
state
zip
phone 1
phone 2
e-mail

 

[Guest]

It sounds like you want to sort by address and then by last name, then send
the output of this query into a report. I beleve when you create a new
report using the wizard you have some choices regarding grouping of data in
the report.

The reason why I say then by last name is for situations where you may have
differant people sharing a house such as a group of students.

 

[Laurel]

No, that's the problem. I need to have the directory ordered by last name
(of the household member marked for that purpose) so that people can find
entries - like a phone book. But I need to keep the people in a household
together.
I have a special field for that marked row where I'll have an appropriate
family designation (e.g., Joan and John Miller).

That's the problem that sent me here.

 

[John Nurick]

Hi Laurel,

Basically you just join the table to itself, something like this:

SELECT
Heads.Household_Directory_Name,
Heads.First_name, Heads.Last_Name
Heads.address, Heads.city, Heads.state, Heads.zip,
Heads.phone, Heads.[e-mail],
Members.first_name, Members.last_name,
Members.phone, Members.[e-mail]
FROM MyTable AS Heads LEFT JOIN MyTable AS Members
ON Heads.[Household key] = Members[Household key]
WHERE Heads.HHD_LabelTag AND NOT Members.HHD_LabelTag;

This should give you a record for each person for whom HHD_LabelTag is
false - but each record contains fields for both that person and the
"tagged" person for that household. I.e. the query is even less
normalised than the table. But you can use it as the basis of a report
that groups and sorts as required.

In the group header, you'd put most or all of the "Heads" fields, while
the "Members" fields would go in the Details section of the report.

But I don't understand how the Other_Household_Member field is meant to
work. Is the idea that if you look in a record where HHD_LabelTag is
true and there's a name in Other_Household_Member, then that person is
(a) the spouse of the person in the record and (b) lives in the same
household? And is the name entered in a consistent form that can be
matched against the first name and last name fields in other records
with the same [Household key] value? If so, it will be possible to
include a calculated field in the query that can be used to sort the
members so the "other household member" is first on the list. Maybe
this:

...
IIF(Heads.Other_Household_Member =
Members.First_Name & " " & Members.Last_name, -1, 0) As Spouse
...

 

[Laurel]

Yes, thank you. I think this will do it. I had already begun to explore
the possibility of a calculated field such as you suggest. Especially in
situations where children may have a different last name than their
parents.... Hmm.... but maybe the HHD_LabelTag person has the unique
name.... like a father who hasn't adopted kids... hmmm.... Well. That's in
the refinement stage. Thanks for the suggestion for the main problem.

Hi Laurel,

Basically you just join the table to itself, something like this:

SELECT
Heads.Household_Directory_Name,
Heads.First_name, Heads.Last_Name
Heads.address, Heads.city, Heads.state, Heads.zip,
Heads.phone, Heads.[e-mail],
Members.first_name, Members.last_name,
Members.phone, Members.[e-mail]
FROM MyTable AS Heads LEFT JOIN MyTable AS Members
ON Heads.[Household key] = Members[Household key]
WHERE Heads.HHD_LabelTag AND NOT Members.HHD_LabelTag;

This should give you a record for each person for whom HHD_LabelTag is
false - but each record contains fields for both that person and the
"tagged" person for that household. I.e. the query is even less
normalised than the table. But you can use it as the basis of a report
that groups and sorts as required.

In the group header, you'd put most or all of the "Heads" fields, while
the "Members" fields would go in the Details section of the report.

But I don't understand how the Other_Household_Member field is meant to
work. Is the idea that if you look in a record where HHD_LabelTag is
true and there's a name in Other_Household_Member, then that person is
(a) the spouse of the person in the record and (b) lives in the same
household? And is the name entered in a consistent form that can be
matched against the first name and last name fields in other records
with the same [Household key] value? If so, it will be possible to
include a calculated field in the query that can be used to sort the
members so the "other household member" is first on the list. Maybe
this:

...
IIF(Heads.Other_Household_Member =
Members.First_Name & " " & Members.Last_name, -1, 0) As Spouse
...

I have an unnormalized database that isn't mine - I can't re-structure it.
It contains the names and addresses of all the members of our
congregation,
and I've been asked to generate a directory, with the address of each
household showing only once, and the names of the children falling under
the
parents. It needs to be sorted by the last name of the HHD_Label row.
Previously, we simply listed them alphabetically, last name, first name,
and
families with the same last name were all over the map (to mix a
metaphor).
A "household" is all the people living at the same address. The thing
that
has me the most stumped, is how to gather the households together in the
result set, but still sort by the last name of the HHD_LabelTag row.

I'm willing to add some columns of information to try to deal with this.
But I can't figure out the SQL. Can anyone help?

Desired output

<Household_Directory_Name> <address> <city> <state>
<zip> <phone> <e-mail>
<other household member (first_name, last_name>
<phone> <e-mail>
<other household member>
<phone> <e-mail>
<other household member>
<phone> <e-mail>

Here are the relevant database columns - one row per person

Household key (a number - the same for all members of a household)
HHD_LabelTag (set to yes for one member of the family - currently used to
mail to households using just the last name)
Househould_Directory_Name
Other_Household_Member (I think I need this so the spouse doesn't show up
in
a list with the children.)
first_name
last_name
address
city
state
zip
phone 1
phone 2
e-mail