Hello. I'm working my way through the courses (on Access Expert 9.1 today), while also trying to help a nonprofit desperate to get a hold on their membership system, contact management, and sales inventory, which has all been on a very error-prone combination of multiple Excel spreadsheets and physical papers.

I'm trying to get all contact info for the household, and the people within that household, into one report that can be exported into Excel to work with an existing mail merge. I've hit a wall.

I'm not sure if:
1. I've got the initial database architecture wrong, and/or
2. if there's a way to set up my query and report to get what I need. Or
3. maybe I just need to press for the membership letter format to change, which won't happen for months due to an overworked small staff.

I've got a "household" table (everyone within a household gets a member discount, which is why I needed this sort of grouping). I know I'm breaking some database normalization rules here, but I couldn't figure out how else to do it.

HousedholdID: 007
HouseholdFirst: Katie and Jeff
HouseholdLast: Bradshaw
HouseholdAddress: 999 Main St
HouseholdCity: Anywhere
HouseholdState: NE
HouseholdZip: 69311
PrimaryPhone: 555-867-5309
PrimaryEmail: [email protected]

There's a "member payments" table linked to the household:
PaymentID: 001
HouseholdID: 007
PaymentDate: 1/1/22
ExpirationDate: 1/31/23
MembershipLevel: 4

A table to track membership levels and benefits:
MemberLevelID: 4
MemberAmount: $100
MemberDiscount: 10%

Then a "person" table for the people within the household. The plan is to link each person to their volunteer hours, the art they are selling in the gallery, or the classes they are taking, as well as serving as a phone book. The primary phone or email for the account may or may not duplicate the information for individuals (we have land lines, joint email accounts shared by couples, and typically one member of a couple is the main point of contact for our organization).

PersonID: 007
HouseholdID: 007
FirstName: Katie
Lastname: Bradshaw
CellPhone: 555-867-5309
Email: [email protected]

PersonID: 008
HouseholdID: 007
FirstName: Jeff
Lastname: Bradshaw
CellPhone: 555-555-5555
Email: [email protected]

PersonID: 007
HouseholdID: 009
FirstName: Jeff Jr.
Lastname: Bradshaw
CellPhone:
Email:

I created a query to try to pull all the contact info together for a member renewal mailing that also serves as a contact verification.

HouseholdID from HouseholdT
HouholdLast from HouseholdT
HouseholdFirst from HouseholdT
HouseholdAddress from HouseholdT
HouseholdCity from HouseholdT
HouseholdState from HouseholdT
HouseholdZip from HouseholdT
PrimaryPhone from HouseholdT
PrimaryEmail from HouseholdT

PaymentDate from MemberPaymentT
ExpirationDate from MemberPaymentT

MemberAmount from MemberLevelT

CellPhone from PersonT
Email from PersonT

Then created a report to try to get the following column headers in Excel:
HouseholdLast
HouseholdFirst
HouseholdAddress
HouseholdCity
HouseholdState
HouseholdZip
Phone (including all phone numbers in the household, separated by commas)
PaymentDate
ExpirationDate
MemberAmount
Email (including all emails in the household, separated by commas)

What I wind up getting is a report that duplicates the household information for each person in the household.

As I was typing all this, I thought that maybe I need to run a couple of queries to dump all phone and email information for the household into one place, and then put those queries into the membership mailing query.

But, how would I do that when there are multiple phone numbers and email addresses that don't have unique field names like "email 1, email 2", and where the person records might not have additional contact info, such as for single-person households. (Maybe I need to add a yes/no field to the household table to indicate a "SingleHousehold" where the person info would be the same as the household info?)

I'm going in circles and could really use some direction.

Any pointers, thoughts, or references to upcoming Access Expert class numbers would be greatly appreciated!