Need to add a table, I think

[Stephm]

Hi. Requirements have cropped up (of course!) that make
me think I need a new table, or two. I'm going to list
the requirements and hope someone can tell me how to make
it work!

Currently I have Contacts table- it holds pertinent
contact stuff: LastName, FirstName, Address, City, State,
EmailName, PhoneNumber, Member(Yes)

I'm competing against Address Book program:
1) I need to be able to link spouses who are members
(many of whom have different last names) so we don't send
mail to Jane Doe and John Deer (2 mailings); rather Jane
Doe and John Deer (1 mailing).

2) How to deal with name changes- if someone gets
married/divorced and changes name (I learned the hard way-
never change your name!), we still want the "link" to
the old name, since we can never remember the new one.

3)Some lucky folks live here part of the year, and there
part of the year. How to deal with 2 addresses for
someone. Address Book allows you to set a date field:
between these 2 dates, mail to Address1, else mail to
Address2.

4) Members who both belong can pay the "family
membership". How to track that Jane Doe paid and that
covers John Deer as well.

5) A little off topic: but we have people we want to mail
stuff to that aren't "members", such as to the media to
cover our events. Should I set up an entirely different
table for them?

I hope I can be salvaged. Thanks, Steph

 

[Tim Ferguson]

I'm competing against Address Book program:

Errrm: if you have something that does everything already, why _are_ you
competing?

1) I need to be able to link spouses who are members
(many of whom have different last names) so we don't send
mail to Jane Doe and John Deer (2 mailings); rather Jane
Doe and John Deer (1 mailing).

A Mailings table, and a FK in each Member record to point to the correct
Mailing record. Straightforward 1:M relationship, one mailing to many
members.

2) How to deal with name changes- if someone gets
married/divorced and changes name (I learned the hard way-
never change your name!), we still want the "link" to
the old name, since we can never remember the new one.

The "correct" answer to this is to create a new table of names with a 1:m
relationship as above. This can lead to horrid UI manouevres, however, so
if it's a rare problem, you might prefer a pragmatic workround. Try
creating a new field called OldNames, and put the old names in there.
Then searches can use a criterion like

WHERE LastName = "Doe" OR OldName LIKE "*Doe*"

and so on.

3)Some lucky folks live here part of the year, and there
part of the year. How to deal with 2 addresses for
someone. Address Book allows you to set a date field:
between these 2 dates, mail to Address1, else mail to
Address2.

Not too hard: it's an Addresses table and a LivingAt table to manage the
many-to-many relationship. Put date stuff in the LivingAt table too, but
think quite carefully about how you are going to deal with overlapping
dates, periods with gaps in them, etc. You can make this extremely
sophisticated or simple, pretty much according to taste.

4) Members who both belong can pay the "family
membership". How to track that Jane Doe paid and that
covers John Deer as well.

Payments table tracks the sums that come in; ContributesTo records how
much goes to each PaymentDue record. Systems like SQL Server can make
sure that all the ContributesTo records add up to the same as the
Payments record, but it's harder in Access (but not impossible).

5) A little off topic: but we have people we want to mail
stuff to that aren't "members", such as to the media to
cover our events. Should I set up an entirely different
table for them?

Up to you. If they are ever going to be membership prospects, I would
keep them in the same People table. If they are different entities,
appear in different queries, have different attributes (fields) then make
them separate. You _can_ use UNION queries to stick different things
together, although it's ugly and slow. If you find yourself doing a lot
of that, then it's better to have a single table and loads of WHERE
clauses!

Hope that helps


Tim F

 

[Stephm]

See inserts.

Errrm: if you have something that does everything already, why _are_ you
competing?

Address book was made to handle 250 records. We have
1700. We're trying to move to Access and it would be
less painful if they keep the functionality they have
(and get so much more!)
(many of whom have different last names) so we don't send
mail to Jane Doe and John Deer (2 mailings); rather Jane
Doe and John Deer (1 mailing).

A Mailings table, and a FK in each Member record to point to the correct
Mailing record. Straightforward 1:M relationship, one mailing to many
members.

OK, I built the Mailings table and added MailingsID to
the Contacts table. What fields would you expect to see
in the Mailings table? And embarrassingly, I don't know
what to do next. Do I add some field in my frmContacts
and somehow link two Contacts to one Mailing?
way- never change your name!), we still want the "link"
to the old name, since we can never remember the new one.

The "correct" answer to this is to create a new table of

names with a 1:m relationship as above. This can lead to
horrid UI manouevres, however, so if it's a rare problem,
you might prefer a pragmatic workround. Try creating a
new field called OldNames, and put the old names in
there. Then searches can use a criterion like
WHERE LastName = "Doe" OR OldName LIKE "*Doe*"
and so on.

Good on me- I figured this out. Thanks.
there part of the year. How to deal with 2 addresses for
someone. Address Book allows you to set a date field:
between these 2 dates, mail to Address1, else mail to
Address2.

Not too hard: it's an Addresses table and a LivingAt

table to manage the many-to-many relationship. Put date
stuff in the LivingAt table too, but think quite
carefully about how you are going to deal with
overlapping dates, periods with gaps in them, etc. You
can make this extremely sophisticated or simple, pretty
much according to taste.

hmm. I currently have Address fields in Contacts. Would
I just need to create one new table? I don't think I've
created a many-to-many, usually have an in-between-table.
So, sadly- what would the table structure look like and
once established, how would I handle the fields (sort of
my reply to your suggestion in #1, where I admit that I
don't know what I'm doing).
membership". How to track that Jane Doe paid and that
covers John Deer as well.

Payments table tracks the sums that come in;

ContributesTo records how much goes to each PaymentDue
record. Systems like SQL Server can make sure that all
the ContributesTo records add up to the same as the
Payments record, but it's harder in Access (but not
impossible).

I have a tblMembershipType that contains
AmountOfDuesPaid, DateDuesPaid. So I'd set up a
ContributesTo table by having ContributesToID in
tblMembershipType? Confusion is that each spouse is set
up as a family membership, showing that they owe $45
each. When the $45 comes in, I'm not sure how to handle
it.mail stuff to that aren't "members", such as to the media
to cover our events. Should I set up an entirely
different table for them?

Up to you. If they are ever going to be membership

prospects, I would keep them in the same People table. If
they are different entities, appear in different queries,
have different attributes (fields) then make
them separate. You _can_ use UNION queries to stick
different things together, although it's ugly and slow.
If you find yourself doing a lot of that, then it's
better to have a single table and loads of WHERE clauses!

Thanks- I think I'll keep them in the same table and give
them a different "classification" like "others" rather
than "member". Maybe build a form that defaults/shows
just "others" - that way if we change their
classification, they will show up on the member form.

Hope that helps

Helps. Wish I were you.
Thanks, Steph

 

[Tim Ferguson]

Address book was made to handle 250 records. We have
1700.

There are any number of contact managers and address books out there.
Outlook springs to mind...

We're trying to move to Access and it would be
less painful

Just remember that debugging and beta testing a new application can be
extremely painful too. Does the business rely on this database? Who can
you sue for compensation if it all blows up?

OK, I built the Mailings table and added MailingsID to
the Contacts table. What fields would you expect to see
in the Mailings table?

Umm: AddressToSendTheThingTo (FK references Addresses)
DateOfFirstSending
PersonalLogoToPutOnTheOutsideOfTheEnvelope

The first one is probably the only important one -- otherwise you won't
know which Member's address to use. You might think they are the same,
but they don't have to be... On the other hand, you might want to point
it to the Member that will appear on the envelope.

And embarrassingly, I don't know
what to do next. Do I add some field in my frmContacts
and somehow link two Contacts to one Mailing?

Not embarassing at all. The User Interface can indeed be very tricky:
essentially they'll want a button that says "Send a newsletter to this
member alone" and one that says "Choose another member who will share
this member's newsletter". The first one just creates a new Mailing
record and points the Contacts.MailTo value to it. The second one
displays a list of all members with their MailTo values (hidden) so the
user can pick one, and that mailTo value is copied to the current
record. Or something like that!

you might prefer a pragmatic workround. Try creating a

Good on me- I figured this out. Thanks.

Good on you. KISS = "Keep It Simple, Stoopid!"..

hmm. I currently have Address fields in Contacts. Would
I just need to create one new table?

This lets you point more than one address to each Member; but you have
already indicated that often an Address is shared between more than one
Member. This makes it a many-to-many.

I don't think I've
created a many-to-many, usually have an in-between-table.

That is right: strictly there is no such thing as a many-to-many
relationship; it's always a pair of one-many relationships and a table
in the middle.

what would the table structure look like and
once established, how would I handle the fields

Members --< LivingAt >-- Addresses

The LivingAt table looks like this

*MemberID *Addresses *MonthFrom
Eric 1034 Jan
Sam 1049 Jan
Sam 1982 May
Sam 1049 Nov

Which means that Sam lives at 1982 from May to September, and 1049 for
the rest of the year. What makes the UI tricky here is letting the user
know that making changes to address number 1099 affects all the people
who receive mailings there, when they may have wanted actually to create
a new address. Deduplicating the Addresses table can be hard too, as
users are likely just to create a new record instead of looking to see
if it already there. Your interface needs to make it very clear that
they are "Updating the details of this address" or "Moving the address
for this member". Without them ever being aware of these magic numbers,
of course!!

I have a tblMembershipType that contains
AmountOfDuesPaid, DateDuesPaid.

Quibble: MembershipTypes are Full, Junior, Life, Family etc. You need a
table like this to control current charges and so on. You also need a
table to track what individual people owe you: call it Dues or FeesOwed
or something.

If you _never_ accept partial payments, then you can have a one to many
relationship between FeesOwed and PaymentsReceived, so that one or more
FeesOwed.Payment points at each PaymentsReceived record. If you even
consider accepting more than one PaymentReceived for a particular
FeesOwed, then you are back into many-to-many territory, with a between-
table called ContributesTo.

Confusion is that each spouse is set
up as a family membership, showing that they owe $45
each. When the $45 comes in, I'm not sure how to handle
it.

I may have misunderstood you here, but if you don't absolutely
understand the business, you are never going to model it correctly!

Thanks- I think I'll keep them in the same table and give
them a different "classification" like "others" rather
than "member".

If it comes to that: they are members if they have a current FeesOwed
record and they aren't if they don't. You could even say they are only
members if they have a current FeesOwed record and a non-null
FeesOwed.Payment field.

Helps. Wish I were you.

Oh no you don't <g>

All the best


Tim F

 

[Stephm]

Tim,
I haven't slept well since your last reply. Why wouldn't
I want to be you. No, actaully, it was Who would we
sue?! Home-grown non-profit project. Hopefully not me.

When I added the Mailings table, the Contacts table
wasn't happy unless it had a MailingsID -so I deleted the
field ;-). Now I'm ready to redo the link- Do I need to
empty out all of the test Contacts or can I just have the
MailingsID going forward? I think I'd integrate the
Address field so that the user wouldn't know that I'm
dealing with 2 different tables and then have a combo box
to select "shared" Contacts to link. Do I add MailingsID
(hidden) to Contacts? So would I have code (really good
at that to) to move the address to the "shared" contact
if shared contact is not null? Then, how do I make sure
the "flip-side" contact got linked. (Mary is linked to
John, is John linked to Mary?).

I'll try to get Members --< LivingAt >-- Addresses going.

On payments, point taken. Another tables it is. Because
I'm so good at tables.
We are allowed to "cash-in" bucks (earned by
volunteering) to the tune of $35 per individual and $45
per family. So say a $75 fee (family) with $22 cashed in
for John and $5 for Mary = (math don't fail me now) $48
owed. So $48 will fulfill the requirement for both Mary
and John together. So perhaps I do need 2 tables:
Payments and ContributesTo?

I understand the business. Just not how to model it. I
rather understand how to model and then figure out the
business! I didn't mean "confusion"- how about
convolution.

Thanks, Steph

 

[Tim Ferguson]

When I added the Mailings table, the Contacts table
wasn't happy unless it had a MailingsID -so I deleted the
field ;-). Now I'm ready to redo the link- Do I need to
empty out all of the test Contacts or can I just have the
MailingsID going forward?

You don't have to have a MailingID value in a Contacts record -- if it's
NULL though, that member won't get a newsletter. Make sure that the
Required property is False, and the DefaultValue is blank or NULL (Access
unhelpfully puts a zero in the default value box, which is really stupid
and has killed more than one database before). Then the ref integrity
will be fine.

You shouldn't have to empty out any contacts records.

I think I'd integrate the
Address field so that the user wouldn't know that I'm
dealing with 2 different tables and then have a combo box
to select "shared" Contacts to link. Do I add MailingsID
(hidden) to Contacts?

I cannot really visualise what you are getting at here, except to comment
that hidden fields often mean that you are getting into trouble

So would I have code (really good
at that to) to move the address to the "shared" contact
if shared contact is not null?

If "moving" records means you are holding the same stuff in separate
tables, then you are definitely into the Bad Lands.

Then, how do I make sure
the "flip-side" contact got linked. (Mary is linked to
John, is John linked to Mary?).

If Mary's stuff is going to John's address, then that fact does not have
to be recorded in John's record at all -- or vice versa. It's easy to run
a query that gathers up who is using whose-else's addresses.

We are allowed to "cash-in" bucks (earned by
volunteering) to the tune of $35 per individual and $45
per family. So say a $75 fee (family) with $22 cashed in
for John and $5 for Mary = (math don't fail me now) $48
owed. So $48 will fulfill the requirement for both Mary
and John together. So perhaps I do need 2 tables:
Payments and ContributesTo?

Sounds like yes.


All the best


Tim F

 

[Stephm]

Thanks for all the help. Steph

-----Original Message-----


You don't have to have a MailingID value in a Contacts record -- if it's
NULL though, that member won't get a newsletter. Make sure that the
Required property is False, and the DefaultValue is blank or NULL (Access
unhelpfully puts a zero in the default value box, which is really stupid
and has killed more than one database before). Then the ref integrity
will be fine.

You shouldn't have to empty out any contacts records.


I cannot really visualise what you are getting at here, except to comment
that hidden fields often mean that you are getting into trouble


If "moving" records means you are holding the same stuff in separate
tables, then you are definitely into the Bad Lands.


If Mary's stuff is going to John's address, then that fact does not have
to be recorded in John's record at all -- or vice versa. It's easy to run
a query that gathers up who is using whose-else's addresses.

Sounds like yes.


All the best


Tim F
.

 

[Dirk Vermeire]

Thanks for all the help. Steph