Poor table design?

[Lisa Reber]

Like S Jackson in DB Normalization post below, I combine
ignorance and lack of training. My main Contacts DB has -
count'em - 75 fields! They cover usual contact
information, plus work address/#, some payment records,
Y/N fields for "Do we send this guy this mailer?" and on
and on. I have recently added a payments table that shows
latest contribution, how much money is membership and how
much is support, etc. Many questions arise - here are my
top 2:
1 - Have linked the payments to contacts successfully, but
now have a problem with pulling out the most recent
renewal date (membership date + 365). A member has many
payments, thus many renewal dates, and my reminder notice
works off a query prompting for a range of renewal dates.
As I re-read this, it sounds like it should work, but
doesn't.
2 - should I just start over?

Thanks all! -Lisa

 

[John Vinson]

Like S Jackson in DB Normalization post below, I combine
ignorance and lack of training.

Don't feel alone! That's where we all start.

My main Contacts DB has -
count'em - 75 fields! They cover usual contact
information, plus work address/#, some payment records,
Y/N fields for "Do we send this guy this mailer?" and on
and on. I have recently added a payments table that shows
latest contribution, how much money is membership and how
much is support, etc. Many questions arise - here are my
top 2:
1 - Have linked the payments to contacts successfully, but
now have a problem with pulling out the most recent
renewal date (membership date + 365). A member has many
payments, thus many renewal dates, and my reminder notice
works off a query prompting for a range of renewal dates.
As I re-read this, it sounds like it should work, but
doesn't.

A Query with a criterion on the membership date of

=DMax("[MembershipDate]", "[PaymentTable]", "[MemberID] = " &
[MemberID])

will select only the most recent membership record.

2 - should I just start over?

Nah. Getting the data in once (even into a non-normalized design) is a
substantial investment of work; it's easy enough to create append
queries and update queries to migrate the data into normalized tables.
For example: you might want to consider a one-to-many relationship to
an Address table, so if someone has a home address, work address, and
summer address you can just have three records; two Append queries
(from the home address fields and the work address fields
respectively) can fill this table with the existing data, and you can
then delete the contacts table address fields. (you *did* make a
backup... right!?) Similarly, you may want to move your y/n fields out
to two related tables: one with fields drawn from your current yes/no
fieldnames ("Sent Solicitation Mailer" as a text field for instance),
and a many-to-many table with the ID of this table and the contactID
for each True entry.

You're going in the right direction - don't turn back! <g>

 

[Lisa Reber]

Hi - as you'll see below, couldn't read your reply. I have
been looking at some of the other resources referred to in
other posts, like mvps.org/access and google groups. Like
this bb, it seems like there is a lot of good information
out there, but finding what I want gets frustrating. For
example, I looked up 'normalization' and got a load of
answers, most of which seemed *way* more complex than I
was after. Anyway, any thoughts are appreciated - Lisa

 

[Rolls]

If you want to understandnormalization, which is a must for SQL databases,
you'll need to work through examples of table and forms design.

The Entity-Relationship model describes two types of tables. State and City
are two entities, which require two tables, tblState & tblCity.
tblState-City is a relationship table which describes the parent : child
relationship between the two entity tables.

Similarly you probably have tblPeople in your database, and tblPhone, and
tblPeople-Phone, as many people have many phones so you need this "resolver
table" to resolve the ambiguity about which person has which phone. But
supposing that one person has two phones, a home phone and a work phone.
Now you need tblPhoneType, which would contain "Home", "Work", "Fax",
"Cellular", etc.

Each time you run across a situation within a table where you do not have a
unique choice (one person has more than one phone) you need a separate
table.

The first step is to design your table structure in this manner. Then
design forms to add, change, and delete data. Parent : child table
combinations use a form : subform combination. Ex.: one person is selected
on a main form. All the associated phone numbers, with phone type, will be
displayed on the related subform.

Instead of having one (unworkable) table with 70+ fields, you'll probably
have multiple tables with just a few fields each. It would still be
possible to recreate the denormalized table via a query reult that combines
everything.

You probably have tblPeople. This is the one place where you store fields
that are in a 1 : 1 relationship with that unique person. If her name is
Mary Smith (single) and she marries and changes her last name to Rajneesh,
then one UPDATE of her LName field will therefore change all references to
her last name throughout the database. If the tables aren't normalized,
this won't happen.

 

[John Vinson]

Hi - as you'll see below, couldn't read your reply. I have
been looking at some of the other resources referred to in
other posts, like mvps.org/access and google groups. Like
this bb, it seems like there is a lot of good information
out there, but finding what I want gets frustrating. For
example, I looked up 'normalization' and got a load of
answers, most of which seemed *way* more complex than I
was after. Anyway, any thoughts are appreciated - Lisa

The Web interface seems to be the problem. Since you didn't post a
(spam-blocked) email address I can't copy this to EMail. If you do
read this, try connecting your newsreader (Outlook Express) to
msnews.microsoft.com rather than using the lately very flaky web page.
If you're not getting this through the web page... then I have no way
to get in touch with you!

Here's the message again just in case it's back up:

Like S Jackson in DB Normalization post below, I combine
ignorance and lack of training.

Don't feel alone! That's where we all start.

My main Contacts DB has -
count'em - 75 fields! They cover usual contact
information, plus work address/#, some payment records,
Y/N fields for "Do we send this guy this mailer?" and on
and on. I have recently added a payments table that shows
latest contribution, how much money is membership and how
much is support, etc. Many questions arise - here are my
top 2:
1 - Have linked the payments to contacts successfully, but
now have a problem with pulling out the most recent
renewal date (membership date + 365). A member has many
payments, thus many renewal dates, and my reminder notice
works off a query prompting for a range of renewal dates.
As I re-read this, it sounds like it should work, but
doesn't.

A Query with a criterion on the membership date of

=DMax("[MembershipDate]", "[PaymentTable]", "[MemberID] = " &
[MemberID])

will select only the most recent membership record.

2 - should I just start over?

Nah. Getting the data in once (even into a non-normalized design) is a
substantial investment of work; it's easy enough to create append
queries and update queries to migrate the data into normalized tables.
For example: you might want to consider a one-to-many relationship to
an Address table, so if someone has a home address, work address, and
summer address you can just have three records; two Append queries
(from the home address fields and the work address fields
respectively) can fill this table with the existing data, and you can
then delete the contacts table address fields. (you *did* make a
backup... right!?) Similarly, you may want to move your y/n fields out
to two related tables: one with fields drawn from your current yes/no
fieldnames ("Sent Solicitation Mailer" as a text field for instance),
and a many-to-many table with the ID of this table and the contactID
for each True entry.

You're going in the right direction - don't turn back! <g>

 

[Lisa Reber]

Dear Mr. Vinson - thanks for the great reply. Haven't
scouted 'round DMax yet . . . Of course it will be
Tuesday 'til I'm back at work to give it a try. Did find
good stuff on normalization basics and table design, plus
part of my brain has been working on the db design whilst
working on other stuff. One other thing that concerns me:
I can't enforce ref. integ. on the relationship between
the contactId field in contacts and payments. I manually
checked the payments, because I had someone else do the
entry (and if I had done it myself, of course it would
have been *perfect* - grin). Anyway, the data is correct
and tied to the right donor - how much should I worry about
the integrity? Thanks!

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

Like S Jackson in DB Normalization post below, I combine
ignorance and lack of training.

Don't feel alone! That's where we all start.

My main Contacts DB has -
count'em - 75 fields! They cover usual contact
information, plus work address/#, some payment records,
Y/N fields for "Do we send this guy this mailer?" and on
and on. I have recently added a payments table that shows
latest contribution, how much money is membership and how
much is support, etc. Many questions arise - here are my
top 2:
1 - Have linked the payments to contacts successfully, but
now have a problem with pulling out the most recent
renewal date (membership date + 365). A member has many
payments, thus many renewal dates, and my reminder notice
works off a query prompting for a range of renewal dates.
As I re-read this, it sounds like it should work, but
doesn't.

A Query with a criterion on the membership date of

=DMax("[MembershipDate]", "[PaymentTable]", "[MemberID] = " &
[MemberID])

will select only the most recent membership record.

2 - should I just start over?

Nah. Getting the data in once (even into a non-normalized design) is a
substantial investment of work; it's easy enough to create append
queries and update queries to migrate the data into normalized tables.
For example: you might want to consider a one-to-many relationship to
an Address table, so if someone has a home address, work address, and
summer address you can just have three records; two Append queries
(from the home address fields and the work address fields
respectively) can fill this table with the existing data, and you can
then delete the contacts table address fields. (you *did* make a
backup... right!?) Similarly, you may want to move your y/n fields out
to two related tables: one with fields drawn from your current yes/no
fieldnames ("Sent Solicitation Mailer" as a text field for instance),
and a many-to-many table with the ID of this table and the contactID
for each True entry.

You're going in the right direction - don't turn back! <g>


.

 

[John Vinson]

Dear Mr. Vinson - thanks for the great reply. Haven't
scouted 'round DMax yet . . . Of course it will be
Tuesday 'til I'm back at work to give it a try. Did find
good stuff on normalization basics and table design, plus
part of my brain has been working on the db design whilst
working on other stuff. One other thing that concerns me:
I can't enforce ref. integ. on the relationship between
the contactId field in contacts and payments. I manually
checked the payments, because I had someone else do the
entry (and if I had done it myself, of course it would
have been *perfect* - grin). Anyway, the data is correct
and tied to the right donor - how much should I worry about
the integrity? Thanks!

If you're manually checking it - you need to worry.

Why can't you enforce RI? Perhaps the ContactID isn't the correct
linking field - what are the tables, and how are they related? If a
Payment is tied to a Contact, why can't you use ContactID as a foreign
key in the Payments table, enforcing RI?

 

[Lisa Reber]

about the integrity? Thanks!

If you're manually checking it - you need to worry.

Why can't you enforce RI? Perhaps the ContactID isn't the correct
linking field - what are the tables, and how are they related? If a
Payment is tied to a Contact, why can't you use ContactID as a foreign
key in the Payments table, enforcing RI?

Thanks John - without having the database to hand, here's
how I have it set up:
Tables are Contacts and Payments and ContactID is the only
linking field. (Contacts has name/address data, etc., and
Payments has date paid, amt, ck#, etc.) Data entry is done
through a subform payments that is locked (don't remember
the right term here) to the contacts main form via
ContactID. The ContactID is a foreign key, I think; it is
not manually entered when the payment is entered.
The problem I had is an error message when I go into the
relationships screen and try to edit the relationship
between ContactID in Contacts and in Payments - the
enforce ref. integ. box is cleared - if I check it, I get
the error message. When I checked it out, it *looked* like
I did everything correctly, so I didn't understand why the
error message.
If this isn't enough information, sorry - I'll reply again
when I have the tables & error msg text in front of me.
Thanks so much for your help!

 

[John Vinson]

The problem I had is an error message when I go into the
relationships screen and try to edit the relationship
between ContactID in Contacts and in Payments - the
enforce ref. integ. box is cleared - if I check it, I get
the error message.

Well, I'll *probably* need to know the error message - but most
commonly if you get such a message when the tables already have data
at the time you're adding a record it means either:

- There is no Primary Key on the "one" side ContactID, e.g. because it
isn't the Primary Key or it has duplicates.
- There is already data in the Payments table with ContactID values
which don't correspond to any ContactID in the contacts table.
- There are records in the Contacts table with NULL ContactID's.

 

[Lisa Reber]

John - I've put replys between your comments. Thanks again!

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


Well, I'll *probably* need to know the error message - but most
commonly if you get such a message when the tables already have data
at the time you're adding a record it means either:

Error message is: "Data in the table 'Payments' violated
ref. integ. rules. Edit the data so that records in the
l;rimary table exist for all related records." It doesn't
appear when adding data, because I didn't set up the
relationship correctly originally. More below . . .

- There is no Primary Key on the "one" side ContactID, e.g. because it
isn't the Primary Key or it has duplicates.

ContactsID is the primary key / autonumber in contacts,
and has field size long integer. There are no duplicates
or nulls. ContactID in payments type is number and also
has field size long integer. Also both tables are in the
same database.

- There is already data in the Payments table with ContactID values
which don't correspond to any ContactID in the contacts

table.

This is what I checked manually. The payments are entered
in a subform with properties LinkChild / LinkMaster =
ContactID, and contactID can't be edited in either the
main or subform (it's not present at all on the subform.)

- There are records in the Contacts table with NULL ContactID's.

No nulls.

.

So do I check the (395) entries again? It looks to my
myopic view like that's the most likely culprit,
especially if I entered something into the table directly,
which I don't *believe* I did. I tried deleting the
relationship (in the Copy) and re-creating it -same error.
(Maybe that deserves a DUH!) Anyhow, tia - I'm going to go
back to my original question and try DMAX.

 

[John Vinson]

So do I check the (395) entries again?

yes - but let the computer help; it doesn't get bored as easily <g>

Use the Query Wizard to create an Unmatched Values query to see if
there are values in the child table which don't match the ID in the
main table.

 

[Lisa Reber]

WOW!!! Thank you so much - this was very easy, fast and
exactly what I needed. Now the tables have Referential
integrity enforced and life is good <g>.
Now I just have to figure out syntax on DMax, but not
today. Doubtless you'll hear about it if I get stuck.
Thanks again!

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


yes - but let the computer help; it doesn't get bored as

 

[John Vinson]

WOW!!! Thank you so much - this was very easy, fast and
exactly what I needed. Now the tables have Referential
integrity enforced and life is good <g>.
Now I just have to figure out syntax on DMax, but not
today. Doubtless you'll hear about it if I get stuck.
Thanks again!

You're Welcome!

DMax("[field name]", "[table name]", "<optional criteria>")

 

[Guest]

DMax("[field name]", "[table name]", "<optional criteria>")



.

Not there yet, tho' it's on my mind. So does it need all
the paren's quotes and brackets? thanks one mo' time!

 

[John Vinson]

DMax("[field name]", "[table name]", "<optional criteria>")



.

Not there yet, tho' it's on my mind. So does it need all
the paren's quotes and brackets? thanks one mo' time!

It needs two parentheses: any Function call is followed by a pair of
parentheses containing its arguments. So you need

DMax( <some stuff> )

The "stuff" in this case consists of three text strings. They can be
literal text strings in quotation marks or they can be string
variables; but the first of them must be the name of a Field (the
field you're finding the maximum value *of*); the second of them must
be the name of a Table or a Query (the domain, it's called; where to
find this field).

The third argument is optional; it's a valid SQL WHERE clause without
the word WHERE that selects which records to consider in searching for
the maximum value. It's the trickiest because it's very often built up
piecewise from string constants and VBA variables or form referernces.
For instance to fing the maximum (most recent) date for a particular
customerID in a table of sales to customers, you might have

DMax("[SaleDate]", "[tblSales]", "[CustomerID] = " &
Me![txtCustomerID])

The square brackets around fieldnames and tablenames or control names
are optional *unless* those names contain blanks or special
characters, in which case they are obligatory - I habitually include
them, just as a visual reminder to me that I'm talking about a table
or form object rather than a VBA variable.

 

[Lisa Reber]

Hi John - finally got to (had to) get this done. In your

A Query with a criterion on the membership date of
=DMax("[MembershipDate]", "[PaymentTable]", "[MemberID] = " & [MemberID])
will select only the most recent membership record.

Why does this work? I ran it the first time without any
memberID parameters, and got the highest membership date
in all member$ payments, which makes sense. Adding
the "MemberID" optional criteria above gave me exactly
what I wanted (THANKS!) each member's most recent
membership payment, and I'm curious. I'd also like to be
able to re-create it myself in the future! Thanks.

Second, the way I have this set up is:
Contacts Table
Payments Table
Query: "Contacts with Payment info" looking at both tbls
qryMaxMemberDate looks at above
query: "Renew or Second Letter" (with membership
date prompt to extract only a range of dates) looking at
qryMaxMemberDate. This third-generation query seems to be
cumbersome, not in itself, but in that it has to drill
down (or up?) to produce the desired results. More a
policy question than procedure, I guess, but it would be
good to do stuff correctly henceforth, after almost three
years of blind guesses.
Regards, Lisa

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

DMax("[field name]", "[table name]", "<optional criteria>")



.

Not there yet, tho' it's on my mind. So does it need all
the paren's quotes and brackets? thanks one mo' time!

It needs two parentheses: any Function call is followed by a pair of
parentheses containing its arguments. So you need

DMax( <some stuff> )

The "stuff" in this case consists of three text strings. They can be
literal text strings in quotation marks or they can be string
variables; but the first of them must be the name of a Field (the
field you're finding the maximum value *of*); the second of them must
be the name of a Table or a Query (the domain, it's called; where to
find this field).

The third argument is optional; it's a valid SQL WHERE clause without
the word WHERE that selects which records to consider in searching for
the maximum value. It's the trickiest because it's very often built up
piecewise from string constants and VBA variables or form referernces.
For instance to fing the maximum (most recent) date for a particular
customerID in a table of sales to customers, you might have

DMax("[SaleDate]", "[tblSales]", "[CustomerID] = " &
Me![txtCustomerID])

The square brackets around fieldnames and tablenames or control names
are optional *unless* those names contain blanks or special
characters, in which case they are obligatory - I habitually include
them, just as a visual reminder to me that I'm talking about a table
or form object rather than a VBA variable.


.

 

[John Vinson]

Hi John - finally got to (had to) get this done. In your

A Query with a criterion on the membership date of
=DMax("[MembershipDate]", "[PaymentTable]", "[MemberID] = " & [MemberID])
will select only the most recent membership record.

Why does this work? I ran it the first time without any
memberID parameters, and got the highest membership date
in all member$ payments, which makes sense. Adding
the "MemberID" optional criteria above gave me exactly
what I wanted (THANKS!) each member's most recent
membership payment, and I'm curious. I'd also like to be
able to re-create it myself in the future! Thanks.

It's simple enough. The DMax() function takes three arguments: the
name of a field; the name of the table or query containing that field;
and a search criterion, in the form of a valid SQL WHERE clause
(without the word WHERE). It will return the largest (maximum) value
of the field which satisfies that criterion.

In this case, the field is MembershipDate, and the table your
PaymentTable; the criterion selects the MemberID's in PaymentTable
which are equal to the MemberID in the current record. That is... the
most recent PaymentDate for this member.

Second, the way I have this set up is:
Contacts Table
Payments Table
Query: "Contacts with Payment info" looking at both tbls
qryMaxMemberDate looks at above
query: "Renew or Second Letter" (with membership
date prompt to extract only a range of dates) looking at
qryMaxMemberDate. This third-generation query seems to be
cumbersome, not in itself, but in that it has to drill
down (or up?) to produce the desired results. More a
policy question than procedure, I guess, but it would be
good to do stuff correctly henceforth, after almost three
years of blind guesses.
Regards, Lisa

You should be able to put this DMax() criterion into [Contacts with
Payment info] directly, if you're not using that query in other
contexts, and you can put additional criteria such as a date range
into qryMaxMemberDate if you wish. Cascading queries are often needed
but they aren't as efficient as a single query.