Family Address book and keeping track of cards sent

[catluvr]

Hi all.

I make and send cards for birthdays and other occasions - Halloween, Christmas, etc.

I have a database with all the addresses I use so I can print labels as needed.

I started with an address table - each record being Last_Name, Spouse1_First, Spouse2_First, address, phone numbers, notes.

I have an occasions table that has the name of the occasion - Halloween - 2022, Christmas 2023 - Sent, Christmas 2023 - Recv, etc - and the date of the occasion.



Then, I have a linking table that has the PK from each of the above two tables. I also have a field in the linking table with a description of the card sent.

I can run reports that look like:

	Valentine's Day - 2023	Halloween - 2023	Christmas 2023 - Sent
Al & Amy Baker	X	X	X
Bill & Betty Jones	X		X
John & Jill Smith		X	X

That all works fine.

Then I come to the birthdays. I decided that the occasion for all birthdays would be "Birthday - year." The date in the table is set to Jan 1st of that year. This way the birthday occasion is after the just passed Christmas and before Valentine's Day.

Originally, I would select a family - Bill & Betty Jones - add the Birthday occasion and then for the card description I would put "Betty - pink butterfly w/flowers, Bill - tool box fold-out". That works. Sometimes, I just send a card to one spouse - sending to Ginny but not Nick.

But then I decided I wanted a list of all the different birthday cards I sent in 2022.

As expected, I get:

Baker Amy - piano
Jones Betty - pink butterfly w/flowers, Bill - tool box fold-out
Smith John - tool box fold-out, Janice (daughter) purple kitty cupcake

I thought about pulling the individuals to a separate table and link the individual to a family. That way, I could track the birthday cards individually. Doing this would also allow me to add kids. Then I could even see who I've sent a particular card to -- like the tool box - fold-out card. Except, then how would I handle the cards that go to the whole family - like Christmas?

Thoughts? Ideas?


Thanks!


Susie
Kansas

[madpiet]

Sort of depends on whether you send the same letter to more than one person/recipient (or if there are groups of people you send things to).

Person--(1,M)--MailRecipient--(M,1)--Letter

(basically means each Person can receive one or more Letters, and each (form) Letter can go to more than one Person. then you could store the date sent in the MailRecipient table. kind of depends on whether you need that kind of detail or not.

[Micron]

If you had a box of piano cards then it may be comparable to a one to many or many to one situation. If these are one-off creations then I'm thinking that there would only ever be one of any particular design so it's not the same.

A simple solution may be to just have "Smith Family" as a recipient or "Smith" and "Family" in separate fields along with the address. Another record would handle Mary Smith and Joan Smith as individuals. To be better normalized, the recipients would be in one table and addresses in another. FWIW, I would do the latter. Either way, if child Alan Smith finally moves out of his parents basement you only need to change his address in the related table. You might need a separate query to form the label differently for family cards.

[catluvr]

Yes, I do often send the same card to multiple people, though not in the same family. So I might have made 8 piano cards and 4 tool-box cards.

I send the birthday cards to individuals and the other occasion cards (Valentine's, Easter, Halloween, etc.) to the entire family.

Currently, I have some yes/no fields with the addresses to "calculate" different first lines. They are: (these are not the actual field names, it's just how they are in my head)

Send only to Spouse1
Send to Family

Also I have an if/then code to handle different last names. So, the first lines end up like this:

Main Last name	Spouse1 Spouse2	Only to Spouse1	To Family	Different Last Names	Result
Brady	Bill Bonnie		x		The Brady Family
Charger	Chris Callie Jones			(they do)	Chris Charger & Callie Jones
Mason	Melanie Ron Reynolds	x			Melanie Mason
Sanders	Steve Sally				Steve & Sally Sanders

(in Melanie's case, I want to know who she's living with in case I need to invite them somewhere, but it changes every so often and i don't want to accidently put the wrong person)

I definitely see that I can have a Family Address table, an Individual Table, and an Occasion Table.

I can link the Families and Individuals with a linking table. I can also link the Occasions with either the Families or the individuals, but I'm perplexed at how to do both easily.

I know I could create identical linking tables for the family/occasion and the individual/occasion and use a series of union queries.

In order to get the first lines for the cards to the entire family (i.e. Christmas), I'd have to create a cross tab query to take this linking table: (family PK and Individual PK)

Family Code	Individual_First	Individual_Last	Role
29 – Brady	Bill	Brady	Spouse1
29 – Brady	Bonnie	Brady	Spouse2
29 – Brady	Bart	Brady	Child
34 – Charger	Chris	Charger	Spouse1
34 – Charger	Callie	Jones	Spouse2
44 – Mason	Melanie	Mason	Spouse1
44 – Mason	Ron	Reynolds	Spouse2
56 – Sanders	Steve	Sanders	Spouse1
56 – Sanders	Sally	Sanders	Spouse2

The Family option for Brady is “To Family” and for Mason is “Only to Spouse1”


To this Cross Tab Query result:

Family Code	Spouse1 First	Spouse1 Last	Spouse2 First	Spouse2 Last	To Family	Spouse1 only
29 – Brady	Bill	Brady	Bonnie	Brady	x
34 - Charger	Chris	Charger	Callie	Jones
44 – Mason	Melanie	Mason	Ron	Reynolds		x
56 – Sanders	Steve	Sanders	Sally	Sanders

Then the “First line Query” would give the first line desired.

I know how to do all of that. No problem.

I'm just stuck on recording something sent to one person versus something sent to the whole family.

Thanks for tolerating my musings!


Susie
Kansas

[Micron]

Don't know if this helps but I see it like this

tblRecipients
RecipientIDpk	AddressIDfk	Lname	Fname	Etc
1	1	Smith	Joe
2	1	Smith	Mary
3	1	Smith	Cindy
4	1	Family	Smith
5	2	Jones	Alan
6	2	Jones	David
7	3	Jones	Peter
8	2	Family	Jones
tblAddresses
AddressIDpk	StreetNum	Street	City	State_Prov	Etc.
1	123	Main St	Anytown	MI
2	555	King St.	Sometown	CO
3	456	Bold St	Toronto	ON
4
5

Note that Peter Jones would have had a value of 2 but he moved to Toronto to go to school so his address is now id 3 in the address table. At least for the time being. If I want to get a list of family records, my query filters on "Family". The reason for that order is because I imagine the envelope address would have first name (and perhaps a salutation field as well) then last name, as in

Mr. Peter Jones
address info next

so when it's addressed to a family you could use the same query and the field order would remain the same. The salutation might be "The":

The Jones Family
address info next

EDIT- you could reverse the table fields if it seems more attractive. I usually put last name field first since I read left to right and would want to first see the last name column as groups of common last names, same as it was in phone books.

[catluvr]

Micron ...

Great idea to have an "individual" family record! That will work, I never would have thought of that.

One question, how would you handle a single person living alone? Should there be two "individuals"? So, for Yvette Younger, there would be:

tblAddresses

AddressIDpk	StreetNum	Street	City	State_Prov	Etc.
4	789	Oak St	Thattown	KS

And:

tblRecipients

RecipientIDpk	AddressIDfk	Lname	Fname	Etc
9	4	Younger	Yvette

But, would you also include:

tblRecipients

RecipientIDpk	AddressIDfk	Lname	Fname	Etc
10	4	Family	Younger

Since Yvette is single, I wouldn't send anything to "The Younger Family" so would I need that recipient?

Hummm ...

Obviously I would connect Yvette's birthday card to her individual record (Yvette Younger), but what about a Christmas Card? Would that be connected to her individual record or to a Younger Family record?


Thank you so much for thinking through this with me!


Susie
Kansas

[Micron]

I don't see why you'd have a family record for someone who lives alone. I think you could benefit from reviewing db normalization, keeping in mind the whole entity (noun or thing = table) and the attributes (adjectives, characteristics = table field) of the entity. IF your table is about recipients then you only need records for those entities that actually exist. You wouldn't need a family record for a single person until they had a family, and when that comes to be, it would work the same as every other situation. Don't think that the id field has any significance in this otherwise you will create a sort of mental roadblock. In other words, it does not matter if the record id for Yvette is 55 and later she marries a man with children (just to make the transition easier to illustrate) and that family record id is 200. If that is a new concept you also need to better understand auto number id's.