How to not overwrite data in a field.

[Bestieboy]

Hi Guys
Apologies for my lack of knowledge in Access. I'm a complete amateur & my title description is probably wrong!
I have a DB (Access 2003) covering Preserved Railways in the UK which also acts as an on-line Search Facility. The latter has been set-up by a friend who owns the website.
http://www.uklocos.com/preservedrail...list/index.asp
Most of the data on here is linked with relational tables to the master table. If you search for a record you'll find in the top right is a Last Seen Date.
The problem here is if you add a new sighting it overwrites the previous one. What I would like to do, if possible, is set this up so the public (member log-in to be added) can add sightings without overwriting previous sightings, with an option to show all previous sightings. I would like to add new fields to this but don't know if these need to go into the master table or a new table.
The Sighted field is current in the master table. New fields to be added - Seen Where, Confirmed ID, Assumed ID, User Name, User Notes.
There is a similar website that appears to have done this with several sightings listed.
http://www.departmentals.com/departmental/971001
Any help much appreciated.
Cheers
Steve

[June7]

What do you mean by 'add a new sighting'? That web page appears to be only a search engine. Where is a new sighting being added?

Need to know more about your data structure.

Just exactly what is being 'sighted' - railway cars, engines, flatbeds?

Do you have a table of equipment info?

Do you have a table of sightings of equipment?

[Bestieboy]

Hi June
Sorry I haven't explained very well. That link is to the Public Search Facility. There are editor pages that are updated on-line which in turn update the public pages. There is a Recent Updates link on the website that displays what is going on. All the records are for Preserved Railway stock in the UK.
The DB has one master table with many other related tables feeding into it.
I've noticed in your post its possible to send the DB but mine zipped is over 5MB. How do I copy an image of my Relationship/Table structure to post on here?
Cheers
Steve

[June7]

The Advanced post editor has tools to manage attachments and to insert image in post. The attachment manager is below the post editor window and the insert image button is on the tool bar above the window.

[Bestieboy]

Hi June
I think I've attached a picture view of my Relationship structure? I had to Zip it as it's a TIF file and wouldn't upload direct.
Relationships for stocklist 260313.zip
The left hand column is the master table where the one to many related tables link to. This contains the 'Sightings' field. My explanations are not good but basically I want to be able to have many sightings for each record rather than overwriting the previous one as its currently set up. Per my opening post I also need to add the following fields - Seen Where, Confirmed ID, Assumed ID, User Name, User Notes. I haven't done this yet as I don't know the best way to go about this.
Cheers
Steve

[June7]

Just as thought, if you want to keep history of sightings will need another table. I have no idea how that will play into a website.

I see Year used as a field name. Year is a reserved word. Should not use reserved words as names. Also, instead of Year as table name use Years. Since not using ID fields (YearID, UpdateID, PhotoID, etc) as PK/FK these fields are not needed.

[Bestieboy]

The extra unused ID fields were not there originally. However the guy who uploaded the DB to his website asked me to add them for that purpose. I don't know why exactly. We had a lot of issues when it first went on-line. Names, spaces between data in some fields. Year hasn't caused a problem so far but Group had to be renamed to Groop.
If I set-up a new table for sighting history how do I relate this to the master table to allow multiple sighting history per record.
Cheers
Steve

[June7]

If ID field in PreservedRailways table is the primary key then this would be the value saved as foreign key in Sightings table.