Database and Relationship Advice

[Claire]

PLEASE HELP

I am trying to produce a database that will allow me to store my client
information and also log events such as telephone calls and and emails when
they arrise. I have produced to tables so far a customer info table and a
event log table but cannot get the information to transfer between the two.
i.e When i look at a customer file then in a form, then click add new event I
add it but then the same entry appears no matter what client I am on. Please
can someone help me I have included the fields that I would like to use but
need a way to link them.

Customer Table and Events Table

Customer ID is currently being linked to Event ID in the event table with a
one to one relationship.

If anyone can take a look at these tables and suggest a better way to
separate and link I would much appreciate this and would be happy to email
you this direct. I do feel that the one table is too large and would like to
separate it, but am having enough problems linking two tables, let alone more.

PLEASE YOUR HELP IS MUCH APPRECIATED

 

[Steve]

TblCustomer
CustomerID
CustomerName
....
....


TblEvent
EventID
CustomerID
EventTypeID
EventDate
EventNote
....
....

Join CustomerID in TblCustomer to CustomerID in TblEvent.

Use a form/subform where the form is based on TblCustomer and the subform is
based on TblEvent.


PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[BruceM]

To add a little to the other response, each customer may have many events,
but an event is associated with a single customer. Therefore the
relationship between customer and event is one-to-many. Two tables is
definitely the way to go. The question of what table structure to use is
guided by the data, not the size of the tables. There is I think a 2 GB
limit to the file size of an Access 2003 database file, but that probably
won't affect you. In you case, when a customer's address changes you need
to decide if you want past records to reflect the change. If you ship
something to a particular address, you may want to save that information.
On the other hand, if a phone number changes you probably do not want to
save the old number in any records.

 

[Claire]

Thanks for both the replies.

Is it necessary to add in

EventID
EventTypeID

????

What will be the data type in these? Will i need to include these on a form
or are they for linking purposes.

I had one relationship before and that was customer ID - Customer ID, do I
have to link the other above also?

Kind Regards

Claire

 

[BruceM]

I'm not sure the purpose of EventTypeID in the example, but I expect the
idea is that there is a related table of event types that is linked to the
Event table. For now you could just think of EventID as a word that
explains the event type (Call, Email, or whatever). CustomerID to
CustomerID is correct, for reasons I hope I explained in my previous
posting. There is no need based on the suggested table structure to link
EventID or EventTypeID to anything.
The Relationships window should show a single link between the two
CustomerID fields. There should be an infinity symbol on the Many side of
the relationship (tblEvent) and a 0 on the other side. If you right click
the join line and look at the join properties (it's a little tricky to click
in just the right place, so if you don't see Join Properties, try again),
the Enforce Referential Integrity box should be checked.

 

[Steve]

In TblCustomer, make CustomerID Autonumber. In TblEvent, make EventID
Autonumber, CustomerID Long Integer and EventTypeID Long Integer. I
inadvertently omitted a table you also need, TblEventTypeID, for recording
the types of events (call, email, etc.). Looks like this:
TblEventTypeID
EventTypeID
EventType
Make EventTypeID Autonumber. Join EventTypeID to EventTypeID in TblEvent.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[BruceM]

Why not just store the EventType in the Event table, rather than linking? A
combo box based on tblEventTypeID could be used to make the selection on the
EventType form, but each event will be of a single type. That is, one event
will not include a phone call and an e-mail, since those would be two
separate events. At least, that's how I understand the OP.

 

[Claire]

Sorry to be a pest. I have have managed to get the relationships up and
working and are well under way with the forms. However, there is something
else i need to record and I am not sure how to do it.

Although I have an events log, i would also like to log requests for letters
from customers. I.e the event log keeps records of emails, calls and advice
requests etc, but when I also need to keep a log of the amount of
documentation i send as the more i send the more the monthly payment
increases. At first I thought I would have to set up a new table but didnt
know how to link it with the other two. Then I thought could I add these
fields to the attendance notes field and do a subform containing just these
fields. Would this work? Or do I have to do a separate table? If so what
field would I need to link and by what relationship?

Kind Regards - your replies have been a fantastic help.

 

[Steve]

TblEventType allows the OP to easily add new event types to her application!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[BruceM]

Add, yes, but why link? Open a form bound to tblEventType (or an input box)
to add a new type of event. Thereafter that new event will be on the combo
box list (although a requery may be needed first).

 

[BruceM]

I'm not quite sure I follow you. Are you saying that sending documentation
is something other than an event? Are you trying to keep track of the
documentation you send in response to a particular event? Is an event
something initiated by you, the customer, or both?
If you got the relationships sorted out for the first part of the question,
this new development is not going to be difficult to implement. The
question is whether it is related directly to the customer as an event is,
or is it related to an event, or is it a type of event. If you can provide
an example or two it would probably help.

 

[Steve]

If you don't link, any record can be deleted in TblEventType with no warning
message and all of that event type in TblEvent will become orphan records.
For example, in TblEvent there could be 5 records with EventTypeID = 2.
EventTypeID 2 could be deleted in TblEventType and afterwards the five
records would have no clue as to what EventTypeID = 2 is.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[BruceM]

My point is that one can save the event itself rather than the EventTypeID.
Since only one field is being saved, I would just let that field be the
actual text. I would use linking when the design calls for it rather than
as a means to prevent deletions. As a developer I would assure that the
user has no ready access to a means of deleting any records unless there is
a specific reason to allow it. To protect data integrity I would use
security measures, not the design.

 

[Steve]

Suppose after the database has been used for a while the OP discovers the
name of an eventtype was either misnamed or misspelled. If the name was
saved all along, now the OP needs to create a new query, find all the
records where the erroneous event name was recorded and do an update to
correct the error. Don't you think it would be much simpler to just go to a
event type table and correct the error there?

Further, what do you think the OP would choose as simpler - create a table
or implement security measures?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[BruceM]

By that reasoning, address information such as street and city should be
stored as numbers, in case somebody misspelled a street or something. There
are arguments to be made for that approach, but it appears that the OP is a
relative beginner, so any such suggestion should be accompanied by a way of
implementing it. But you are going to do it your way, and I am going to do
it my way, so that's that.

 

[Steve]

<<By that reasoning, address information such as street and city should be
stored as numbers, in case somebody misspelled a street or something>>

If the data contains many records of the same street and/or many records of
the same city, then that is the correct way to do it. At least for streets,
address data ususally contains numerous streets with very few repeats sp
street names are typed in for each record. For cities, many times you will
see records where the state, county and city are recorded. Each will have
their own table. Users then can narrow down the county selection list by
first selecting the state then they can narrow down the city selection list
in like manner to only cities in the selected county.

I once did a database for a realestate agent whose clientele was only in a
section of a large metropolitan city. In this database in was important to
accurately record street names so the database contained a table of all the
street names in his section of town. Streets were then selected from a
dropdown list.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[BruceM]

If you're going to toss something like a linked table at a new user you need
to explain what you mean, not just say that another linked table is needed.
I know the reasons for selecting a number rather than the text, but it is
difficult for me to see the advantage of storing a number rather than the
text "Phone Call", assuming that "Phone Call" is spelled correctly in the
first place. In any case, it is a design nuance beyond the OPs question.
I've said all I have to say on the subject.

 

[Steve]

<< it is difficult for me to see the advantage of storing a number rather
than the text "Phone Call", >>

Reread my responses in this thread!!

<<assuming that "Phone Call" is spelled correctly >>

Check out the definition of "Assume"

<<I've said all I have to say on the subject>>

Sure-eeee! Here's what you said in your previous post --

<<and I am going to do it my way, so that's that.



PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)