Christmas Card list design

[Ken->]

I have a flat data base pretty much like the mailing list you get with the
wizard. It has Mailing List ID, Prefix, First Name, Middle Name, Last Name
etc. and a Notes field. I have been using the Notes field for things it isn't
intended for like I put an "i" in the field if someone was to get an
invitation to our daughters wedding and an "r" if they were going to be
invited to the rehearsal dinner etc. and then I used a query with =like"*i*"
to get a list of the people we were going to invite. This field has gotten
out of hand and I think the design was flawed to start with. Well 4 years
have passed and I'm still using the names and addresses table and now I want
to note who sent us a Christmas card this year. My initial thought was to
make another table with only the Mailing List ID in it and make a record in
the new table for everyone who send us a card. Is this a good idea? Now if
it is, then what would be a good design to be able find the name in the
names and addresses table and click on something in the form and create
another record in the Christmas Card table.

Assuming that you are still following what I am trying to do, should I have
a separate table for each year or have a year field in the Christmas Card
table and do the query with that field as a criteria?

Thank you for your patients.

Ken

 

[Douglas J. Steele]

You should definitely not have a separate table for each year.

You should have a table of people and their addresses, and another table
that contains the Id from the People table, plus a year field (although it
shouldn't be named Year, since that's a reserved word in Access). As a card
arrives, you determine whether or not you've already got that person on
file. If so, you add a new row to the second table (to indicate that you got
a card from that person this year). If not, you add a record for the person,
then add the row to the second table.

 

[Ken->]

I'm glad you mentioned that Year is a reserved word. The Christmas Card
table has "Mailing List Id" and "year rcvd" now.

How should I go about after I've determined that the person is in the Names
and Addresses table and that there is no entry in the Christmas Card table
for them to automatically create a new record in the Christmas Card table
with the Mailing List ID field filled in. I guess I can set the default
data to "2005" for the year rcvd field unless you have a suggestion that is
easier than that (easier for the user, which will probably be my wife.)
When I say automatically, I really mean that it does it when I click on a
button or something.

Thanks,

Ken

 

[Douglas J. Steele]

Easiest way would probably be to use a form that has the names of the people
in a combobox, unless you've got too many people involved. Set the
combobox's LimitToList property to Yes, then put code in the combobox's
NotInList event to allow you to enter their name if it's not in the list.
(http://www.mvps.org/access/forms/frm0015.htm at "The Access Web" is an
example of one way to do this)

So that you don't have to remember to change the database each year, don't
set the default value to 2005: in your form, insert a row that has a value
of Year(Date()). In fact, if you may be entering details in January, you may
want to use something like "IIf(Month(Date()) = 1, Year(Date()) - 1,
Year(Date()))"

 

[Larry Linson]

Here's another tip... lose the blank spaces in your field names. You'll
thank yourself for it later.

Larry Linson
Microsoft Access MVP

 

[Ken->]

Fortunately, I made a mistrake when I typed the field names in the message.
The field names do not have any blanks, but my table names do have blanks in
them. Will that cause a problem later on?
Ken

 

[Douglas J. Steele]

Fortunately, I made a mistrake when I typed the field names in the
message. The field names do not have any blanks, but my table names do
have blanks in them. Will that cause a problem later on?
Ken

Not if you're careful to always put square brackets around the table names.
However, if you haven't got that far along, I'd suggest correcting the names
now.

You might consider getting a 3rd party tool that'll find all occurrences of
a particular name in your database and let you change it:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/