Database and Relationship Advice

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

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






"Claire" wrote in message
...
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

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" wrote in message
...
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

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


"Steve" wrote:

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






"Claire" wrote in message
...
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

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.

"Claire" wrote in message
...
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


"Steve" wrote:

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






"Claire" wrote in message
...
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

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



"Claire" wrote in message
...
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


"Steve" wrote:

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






"Claire" wrote in message
...
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

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.

"Steve" wrote in message
ink.net...
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



"Claire" wrote in message
...
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


"Steve" wrote:

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






"Claire" wrote in message
...
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

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.

"BruceM" wrote:

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.

"Steve" wrote in message
ink.net...
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



"Claire" wrote in message
...
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


"Steve" wrote:

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






"Claire" wrote in message
...
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

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




"BruceM" wrote in message
...
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.

"Steve" wrote in message
ink.net...
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



"Claire" wrote in message
...
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


"Steve" wrote:

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






"Claire" wrote in message
...
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

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).

"Steve" wrote in message
nk.net...
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




"BruceM" wrote in message
...
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.

"Steve" wrote in message
ink.net...
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



"Claire" wrote in message
...
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


"Steve" wrote:

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






"Claire" wrote in message
...
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