Cute problem!
What I would do is build query from Households. I would then build ONE
field in the query that returns the "lowest alphabetical" last name from the
members table.
You then build a report that is sorted by households, and place in the
detail section a sub-report that lists all members in alpha betical order
also.
Your main household export can be sorted by this last name.
select id, MemberDate, City, District,
(select top 1 LastName from tblMembers where HouseHold_id = tblhouseholds.id
order by LastName) as FamilyName from tblHouseHolds
That should do it. The above query will result in a list of households
orders by the lastname.
Note that you can use the above trick with your existing join query, and NOT
use a sub-report. However, your will find that a sub-report makes setting of
the members format, layout, and sort order easier. And, you don't even have
to mess with a nasty join in the main query that the report is based on.
However, either way..the above idea is to grab ONE name from the members
list. Further, you could even add a check box, or a simple field to the
member table that shows who is the household owner, and then replace the
"top 1" query I had with a simple condition that grabs ONLY the one member
who is the household owner. As you have it now...how did you plan to select
what member will be used in the sorting? My above example takes the lowest
alpha name from the members list...but likely there is some way to tell
which member is the house owner?
So, even better, is to make a combo box with the wizard on the main form
that lets you select who from the members sub-form list is the owner. This
combo box would store the id of the member from the members list into a
field called HouseOwnerId.
You then can use:
select id, MemberDate, City, District, HouseOwnerId,
(select LastName from tblMembers where id = tblHouseHold.HouseOwnerId) as
FamilyName from tblHouseHolds
