Creating Extended Family Listing

[jbc]

I'm creating a database of my family and extended
relatives. I need to keep track of mailing address, phone
number, birthdays.

Reports need to create. Mailing labels, listing of family
members by household, birthday listing.

I created 2 tables. One with household information:
tblMainAddress
ID
LastName
StreetAddress
City
State
Zip
HomePhone
EmailAddress
Email2Address

ID Primary key

tblMembers
ID
MemberID
FirstName
Birthday
Addressee1st Y/N
Addressee2nd Y/N
Child Y/N

ID Primary key

Both tables linked one to many ID (tblMainAddress) to
MemberID data integrity enforced

I have addressee1 and 2 for the labels I want to have
control over which name appears first.

The trouble I am having is getting a listing:

Want it to appear like this:

LastName, FirstName (addressee1)& FirstName (addressee2
StreetAddress
City, State Zip
Phone
Children: xxx, xxx

Having trouble getting this to happen without duplicating
the home address for each person.

Is there something wrong with my logic in setting up the
database? Any suggestions would be appreciated.

jbc

 

[Allen Browne]

Add a field to tblMembers:
SortOrder Number Lower numbers sort first.
Does not have to be unique: the order is undefined if equal.

You're fairly game in making the assumption that all the household members
at an address will have the same surname?

Anyway, the function to return a concatenated list of first names in the
order specified for a particular address would look something like this
(aircode):

Function GetFirstNames(varAddressID)
Dim strSQL As String
Dim rs As DAO.Recordset
Dim strOut As Variant 'Output string to build.
Dim lngLen As Long 'Length of string.
Const conSep = ", " 'Separator between names.

If IsNumeric(varAddressID) Then
strSQL = "SELECT FirstName FROM tblMembers WHERE ((AddressID = " &
AddressID & ") AND (FirstName Is Not Null)) ORDER BY SortOrder, MemberID;"
Set rs = dbEngine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
strOut = strOut & rs!FirstName & conSep
rs.MoveNext
Loop
rs.Close
End If

'Return the output string without trailing separator, or Null.
lngLen = Len(strOut) - Len(conSep)
If lngLen > 0 Then
GetFirstName = Left(strOut, lngLen)
Else
GetFirstName = Null
End If
Set rs = Nothing
End Function