Linking/Associating one record to multiple records with another table?

[ferindojr]

I have an Access 2000 table. This table has a field within the table to capture User comments. However, each user comment cannot be saved as a separate record. I am trying to design a form for this table so that a User can add a comments which is then linked to one individual records within the table and other users will be able to cummulatively add more commnent records that are linked to one individual record. I suspect that I'll need to tables to do this but I have no idea how to set it up or How the procedures to do to design the form.

 

[jrbarnett]

Hi ferindojr,

If I understand you correctly...

One table for comments

tblUser
fields: username text 20, real name text 40
password text 30, etc. add whatever other data you need to store about any particular user.
Set username as primary key.

tblComments
ID primary key type autonumber
Timestamp DateTime
Username text 20
comment memo

Go to relationships add tblUser and tblComments.

Drag line from Username field in tblUser to username field in tblComments to establish referential integrity at 1:Many type.

As for setting up a form, there are two approaches (based on the above design):

Either:
Set up an autoform based on tblUser and add tblComments as a subform linked via the Username field in each.

Or
Set up a form based on tblComments and have username as a drop down list (combobox) on it. It depends on how you want to set up your user interface.

Either way you can set the text control bound to the timestamp field with a default value of =Now() to get a timestamp of when the record was saved and can produce a sorted report quite easily.

John

 

[ferindojr]

Thanks jrbarnett for your help. OK, I don't think I explained what I'm trying to do very well.

I have created a table to capture various User data (for this explanation I'll call it Table1. I then created a second table to capture the Users':
- name
- their comment
- and a time stamp

Currently, these two tables have an indeterminate relationship based on the "Comment" field...

The end result I'm trying to get in this is:

When the Users input data into a Form based on Table1, they can enter multiple "Comment" records, which when any given record is displayed on the form of Table1, all the comments are show in another area at the bottom of the form which shows all the various comments that have been made regarding that one record in Table1.

 

[jrbarnett]

Hi,

If the two tables have an indeterminate relationship based on the comment field, it means that there must be a comment field in each table, or the comment table is linked to another field in the user table.
If you only have a form based on table1 it means that there can only be one comment per user, which is quite clearly not what you want.

If you don't like the subform suggestion, you could create a list style form based on Table1 with the comments in the detail section and the user info at the top in the form header, but it doesn't look particularly professional.

For the form design, if you don't know how to do it, I would suggest using the Autoform wizards as a basis and carrying on from there. It will give you something to work on and while it may not be exactly what you want, it shouldn't take long to modify and get working the way you want it. It would take too long to explain how to design it manually from scratch so either get yourself a book on Access and read up or press the F1 key and look at the relevant sections of the help system.

John


John Barnett
--
Please consider awarding me a star if you feel my posting has been useful. Thank you.

 

[ppkgoldengun]

Look up in the documentation on setting up forms and subforms. Create a Master Table. Then create a table supporting the comments linked to the master table via a key field. Your main form will have the master table data and the subform will allow multiple comments.

Hope this helps. I use this method on a contract where I have multiple change/history records.