MS Access - Create table linked to a Record in another table

[RabbieBurns]

I have an asset database which is pretty flat in design. It has a record for "Comments", and at the minute these just get overwritten next time something else is to be recorded.

 

I was trying to think of a way that I could record everyting that is entered in the comments record, to buid a history of that asset. But I cannot think how i would go about it. Im thinking another table, linked to the asset, but how to go about implementing it I'm not too sure.

[SYNACK]

Heres how I would do it, you have your assets table obviously with a uid to identify them and act as the primary key. You then have a seporate comments table which has its own uid to id comments and a AssetUID which is the UID of the asset the comment relates to, you want a comment field obviously and probably a date field to.

 

You then set up a relationship between the UID of the Asset and the ASSetUID field of the comments table. This will be a one to many relationship as there will be one asset to possibly many comments.

 

To retrive the comments for each field you could use a subform in access which linked to the comments table which will filter based on the relationship or use a query if you are grabbing the data for an external app like a web page.

[RabbieBurns]

Cheers, will give it a go tomorrw. Not too sure about forms - my weakness. But will see what I can do.