Appending unique fields and then showing them on a related form

[harryfraser]

Hi,

Not sure if you can do this but thought I may ask.

I have created a contacts management system and in which there are multiple tables. One table is called contacts in which there are the two fields I want to append *date of contact* and *comments*.

I want to append this information upon clicking a command button - here's an example:

The user gets a telephone call from Mr X and he says that "there is a problem with his order". The details of the problem are typed into the comments box and the date of the comment is automatically recorded. I want this comment to be moved from here to a separate table on another form so that records are created that keep track of individual comments that are left on the system e.g:

Date of comment Comment
26/01/2011_______Mr X had a problem with his order.
27/01/2011_______Mr X called back to say problem had been fixed.
28/01/2011_______Mr X has reported a new different problem.
29/01/2011_______Company has organised for someone to visit on Fri.

Is it possible to create an append query such as this, or would I have to create a new related table called comments? Ultimately I want the comments to be linked to the contact - so that when you view Mr X's accounts you can see the comment history.

Hope that makes sense

Thanks

Harry

 

[CBrighton]

While it's possible in a single table I would highly recommend another table for comments as it's a classic one to many situation.

With multiple tables you could add a subform for the comments (bound to the new table) which you could set to a continuous form to allow the user to both view historic comments and add new comments.

A comments table would meet the need for adding comments & retaining previous comments as well as allowing the user to view the historic comments plus it has the added bonus that no append query is needed.

 

[harryfraser]

Ok, sorry iff this sounds a bit daft, i'm having one of those days...

How would I go about creating my new table? I assume that I would need something along the following:

*Comment ID (PK)
*Employee ID (FK)
*Comment
*Date of comment

That about right?

H

 

[CBrighton]

Bang on.

You may want an extra field to record who added the comment if it's a multi-user database. But what you have is all fine.