Help with 'Lookup'....I think!

[Joe]

Hi
I am trying to create a simple database to log phone call messages but
am not sure how to stop duplication of names.

For example, I log 3 calls from 3 different people on day 1; Tom, John
and Fred. I also log the message from each of them.
Tom phones again later and I need to log his next message.
I do not want the database creating a new name if the name already
exists. Would require it to create it if it is a new name else 'look up'
the name if it already exists.
This would then show all the messages from the caller.
Then later I would like the database to show all messages from each
person in a report.
I have one table containing the:
- Contact_ID, Date, Message and Message_ID

A second one containing:
ID (primary key), Name and Phone no.

And a form containing all the information.

Not sure if I have described this too well but can any expert give me
some help in how I go about setting this up please. I am new to writing
Access databases.

 

[John W. Vinson]

Hi
I am trying to create a simple database to log phone call messages but
am not sure how to stop duplication of names.

For example, I log 3 calls from 3 different people on day 1; Tom, John
and Fred. I also log the message from each of them.
Tom phones again later and I need to log his next message.
I do not want the database creating a new name if the name already
exists. Would require it to create it if it is a new name else 'look up'
the name if it already exists.
This would then show all the messages from the caller.
Then later I would like the database to show all messages from each
person in a report.
I have one table containing the:
- Contact_ID, Date, Message and Message_ID

A second one containing:
ID (primary key), Name and Phone no.

And a form containing all the information.

Not sure if I have described this too well but can any expert give me
some help in how I go about setting this up please. I am new to writing
Access databases.

It sounds like you have your tables set up mostly correctly: the second table,
let's call it Contacts, would have one record per person, and the first table
- Messages - one record per message. I presume you have a one-to-many
relationship defined between Contacts.ID and Messages.Contact_ID - if not, use
the Relationships window to create one.

I would suggest using a Form based on Contacts, with a Subform based on
Messages. Use ID as the Master Link Field and Contact_ID as the Child Link
Field to keep the form and subform synchronized. Put a combo box on the
mainform using the Combo Box Wizard, choose the option "use this combo to find
a record"; this will let you navigate to an existing name if it's there.

Some suggestions: don't use Name or Date as fieldnames, they are reserved
words and can cause errors and confusion (what is the Name property of the
Name textbox, or the Name of the Name field...???). Also, you should probably
split people's names into - at least - FirstName and LastName fields, probably
Title, Middlename and Suffix as well. Your combo box can be based on a query
combining these for display.

 

[Joe]

It sounds like you have your tables set up mostly correctly: the second table,
let's call it Contacts, would have one record per person, and the first table
- Messages - one record per message. I presume you have a one-to-many
relationship defined between Contacts.ID and Messages.Contact_ID - if not, use
the Relationships window to create one.

I would suggest using a Form based on Contacts, with a Subform based on
Messages. Use ID as the Master Link Field and Contact_ID as the Child Link
Field to keep the form and subform synchronized. Put a combo box on the
mainform using the Combo Box Wizard, choose the option "use this combo to find
a record"; this will let you navigate to an existing name if it's there.

Some suggestions: don't use Name or Date as fieldnames, they are reserved
words and can cause errors and confusion (what is the Name property of the
Name textbox, or the Name of the Name field...???). Also, you should probably
split people's names into - at least - FirstName and LastName fields, probably
Title, Middlename and Suffix as well. Your combo box can be based on a query
combining these for display.

Many thanks for that John. I shall take youyr advise regarding field
names and creating a form/subform.
I have at present only a one-to-one relationship. I will change that to
a 0ne-to-many.
It was very kind of you to take the trouble and time to answer. Much
appreciated.
Again many, many thanks for this help as I flt I was truely stuck!

Kind regards

Joe