Mailing List - multiple address - design strategy 2

[kschouest]

Hi everyone -

I am creating a mailing list for a client in MS Access 2007. It will be utilized for a mix of business and personal mailings, so most entries include two addresses, for office and home mailings. Her existing list has almost 2,000 names.

The client also wishes to use the list to track birthdays, anniversaries, and other date-specific events. The idea at the moment is that she will query the database periodically to request, say, all the special events coming up in the next week or month, and create labels, and perhaps a companion report so she will know that, say, she needs to send 4 birthday cards and 3 anniversary cards, and who knows what else.

Currently, the design structure is such that the two addresses are tagged as "business" or "personal," along with another drop-down list that includes one or more categories. So the categories include, among others, judges, attorneys, bar association, clients, friends, family, church.

My current intention is to associate each address with the appropriate category(ies), so that she can generate a list of, say, only attorneys and clients, or only friends and family, or only personal or only business, and the appropriate labels will print.

In theory (haven't started it yet!), I'm thinking that will work fine - feel free to correct me if I'm wrong here! But here's the monkey wrench in the soup:

The contents are an interesting mix. A "best case scenario" contact would be, say, John Smith, who is an attorney but also happens to be a neighbor and friend. So his business label would say, "John A. Smith, Esquire," and his personal label may just say, "John Smith." His birthday card would use his personal label and that would be the end of that.

But then I've got Dave Johnson, who is Dave E. Johnson, Esquire, but his personal mailings go to, "Mr. and Mrs. Dave E. Johnson," to include his wife.

And adding to that lovely mix, I've got The Honorable Frank O. Lowry, but his business mailings go to, "The Honorable Frank O. Lowry & Staff," and his personal mailings go to, "The Honorable and Mrs. Frank O. Lowry."

So now I need to track Dave Johnson's and Judge Lowry's birthdays as well as anniversaries. So far, there is no mention of tracking the spouse's birthday (thank goodness!).

Tracking the birthday is not the hard part. The kink is in how to get the database to know that when I'm sending "personal" mail to Dave Johnson and Judge Lowry, it is addressed to the married couples. And ditto if I'm sending them an anniversary card. But if I just want to track and create labels to mail Dave Johnson and Judge Lowry a personal birthday card, how can I do this without creating a lot of extra tweaking after the fact on the user end?

At first I thought of just creating a special "birthday" label with a single field that included the custom label addressee name. But then it would require an additional field for anniversaries and other odd name combos.

So then I'm wondering, if I did that, how would the database know that of the seven dates I have coming up this month, some need the birthday custom label and others need the anniversary custom label.

I'm hoping that I'm just making this a whole lot more complicated than it needs to be, and I'm just missing the forest for the trees in the solution.

Thanks!

Karen

 

[GingerR]

What is your existing table structure/fields? Seems you need a few one-to-many tables.

I assume that you have a table that stores an ID and a regular name like Dave Johnson. Then you should have a separate ADDRESSES table that stores the PersonID along with address which each have a category (Business, Personal). Also then for the TYPE OF PERSON, that should be in its own table, along with the address label info.

So Judge Smith is a LAWYER and a FRIEND.

PersonID    PersonType    PersonTypeTitle
6             Lawyer        John A. Smith, Esquire
6             Friend       John Smith

Same should be for the ADDRESSES:

PersonID    AddressType    AddressLabel
7            Business      The Honorable Frank O. Lowry & Staff
7             Personal     The Honorable and Mrs. Frank O. Lowry

so you have a coupla diff things going on: occasions, addresses, etc. they are all one-to-many so you have to design that way. Ok?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[kschouest]

Hi, Ginger - Thanks so much for the quick response!

I have not even created the first table yet - I'm still sketching it out on paper, and got stuck with the birthday/anniversary thing, so I came here first.

Yes, that is exactly how I anticipated setting it up, pretty much the way you have illustrated. But in my "pencil and paper version," when I try to envision how the birthday and anniversary things will play out, I run into a snag.

I'm not seeing these as categories as much as they are "one-time" events. If it was only ever birthdays, I could easily just associate another, say, PersonBirthday field or something like that, which included the birthday name info and the personal address. And then when I ask for the birthday labels for, say, June, it knows to pull up all the June birthdays and give me the labels based on that PersonBirthday field.

But there could be anniversaries and other events coming up in June that might need a different kind of label, or maybe they could just use the "personal" (PersonTitle, in your example) label "as is."

So when I say, okay, I'm ready for the June "events," which are a mix of birthdays and anniversaries and who knows what all, how will it know what labels (names/addresses) I need?

I'm probably clear as mud, huh? Does it make sense where the kink is?

Thanks!

Karen

 

[GingerR]

first if i were you i would type out a variety of examples exactly how you would want to see them, in Excel or Word or something, so you have something to aim for. Forget about the database stuff or how to design the thing, just put what the end result should be for all different scenarios. From there you can diagnose it.

it will know what labels (titles + addresses)you need because you will tell it. Sketch out your end product first, then let's go from there.

I think the missing link is having an OCCASION table that defines PersonID, OccasionID, OccasionTitleID, OccasionAddressID

Person       OccasionID    OccasionTitleID OccasionAddressID
6            1 (birthday)     1            2 (home)
6            2 (anniversary)  2            2 (home)

anyhow, i dont know---type out all of the diff scenarios and see where we are after that.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at [URL unfurl="true"]http://r937.com/relational.html[/URL]

 

[kschouest]

Name: John Q. Public

Business:
John Q. Public, Esq.
123 Main Street
Springfield, MO 12345

Personal:
John Q. Public
123 Cherry Blossom Lane
Sleepy Suburbs, MO 00001

Occasion:
Birthday: June 10, 2009
John Q. Public
123 Cherry Blossom Lane
Sleepy Suburbs, MO 00001

-----------------------------------------

Name: Samuel R. Johnson

Business:
Mr. Samuel R. Johnson
345 Avenue A
Chicago, IL 34566

Personal:
Mr. and Mrs. Samuel R. Johnson
464 Wisteria Lane
Chicago, IL 34566

Occasion:
Birthday: June 14, 2009
Mr. Samuel R. Johnson
345 Avenue A
Chicago, IL 34566

Occasion:
Anniversary: June 15, 2009
Mr. and Mrs. Samuel R. Johnson
464 Wisteria Lane
Chicago, IL 34566

-------------------------------------------------------------------------------------------

So, I want to be able to query the database and ask for a report of all the occasions that are coming up this week. The report might look like:

June 10, 2009 John Q. Public Birthday
June 14, 2009 Samuel R. Johnson Birthday
June 15, 2009 Samuel R. Johnson Anniversary

I'm still mulling over whether I need to view the specific address/label info in the above report. For now, it pulls only the main PersonID. That report mainly tells me what types of cards or correspondence I need to send out.

Then in that same query, I want to generate labels, as follows:

John Q. Public
123 Cherry Blossom Lane
Sleepy Suburbs, MO 00001

Mr. Samuel R. Johnson
345 Avenue A
Chicago, IL 34566

Mr. and Mrs. Samuel R. Johnson
464 Wisteria Lane
Chicago, IL 34566

-------------------------

So my goal is to design something that allows me, in a single query, to come up with a list of all the occasions for a given time period, and the appropriate labels that go with those occasions, without having to remember to ask for birthdays, anniversaries, and what not - just "all occasions."

I don't see a problem with having to enter the label-specific information at the time the occasion is entered. For example, knowing that I need to include only the person for birthdays and both the husband and wife for anniversaries. But I'm stumped as to how to structure it so that once I enter it, I can forget about it and only have to ask for the more generic "occasions" and know the right label will pop out.

Thanks, Ginger!

Karen

 

[MajP]

You need an occasion table

tblOccasions
personID
occasionType
dtmOccDate
lblType

1 1/1/1965 Birthday PersonalIndiv
1 2/2/1996 Anniversary PersonalCouple
1 10/3/2010 Retirement Buisness
2 7/7/2009 Promotion Buisness

And addressee table separate from the address table because a person can have many addressee titles but multiple titles could relate back to one address. In this case Sam has 3 addresse Titles relating back to 2 addressess.


tblAddresseeTitle
personID
addresseeTitle
addressID
addresseeTitleType

Addressee AddressID
1 Mr Samuel Johnson 1234 PersonalIdiv
1 Mr and Mrs Samuel Johnson 1234 PersonalCouple
1 Honorable Samuel Johnson 4567 Buisness


tblAddresses
addressID
typeID
personID
street
City
State
zip

1234 1 1313 MockingbirdLane Boston MA
4567 1 11 Center Place Boston MA

 

[kschouest]

Oh, wow, Ginger, you have really nailed it!

Putting this into action may be a little tricky - I'm sort of picturing multiple levels of subforms within the form in order to keep all the linkages together and keep it user-friendly, so that will be interesting getting it all set up! But what you suggested makes perfect sense, in order to accomplish the goal of having the user being able to get what she wants at any point in time with a single query.

And I hope my next question doesn't throw yet another kink into the picture, but I have a feeling you will have a magical solution to that one too!

I won't be actually working on the table/form creation until next week, so I might be back with more questions once I get started.

THANK YOU SO MUCH!

Karen

 

[GingerR]

Well, it was MajP who wrote that last note, sort of summing up what I'd told you earlier, so that was a big help. Yes--subforms would work. Let us know how it goes.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[kschouest]

Ah, Ginger, thanks for pointing that out! I had scrolled up a little too fast and scrolled right past the ID tag on the post to see that someone else had joined our party! So sorry, MajP! Thank you for the help! I'm still scratching my head a bit over my duplicate address issues mentioned in the later post, which MIGHT have a slight affect on how I ultimately deal with the occasion labels, but I think I can still apply this strategy the two of you mentioned in here no matter what I end up doing about the duplicates.

All my "occasion label" light bulbs have clicked on in full force thanks to both of you - I was drawing a complete blank before you came to the rescue! It may be a few weeks before the database is finished, but I'll keep you posted on the end results. Thanks! -- Karen

 

[MajP]

I am a big fan of continuous synchronized subforms instead of nested subforms. Although you cannot use the wizards to do this, it can be done with very little code. So you could have a form with a continous subform of "People" information: First Name, Last Name, ... Then under that another continous "occasion" subform linked by person ID. So if you click on a person in subform "People" it shows all the occassions in subform "Occasions" for the selected person. Under that a continous address subform with address info for the selected person. Then under that subform a final "Addressee" continous subform. You can use tabs to save real estate.

I would use these forms for navigation only and have them locked. However if you double click on a record in a subform an editable popup form opens where you can add, edit, or delete.

 

[kschouest]

I like that approach, MajP. It may be a little tricky to set up the synchronized forms, but I know where to come if I hit a snag. THANK YOU! - Karen

 

[MajP]

I think this example shows some ideas.

http://www.4shared.com/file/97759751/2cc085c4/SubForms.html

 

[kschouest]

Oh, wow - THANK YOU SOOO MUCH! This is going to be VERY helpful! - Karen