Updating old record on creation of new record with same value in a field

[jshott]

Howdy!

I have a question that I have tried to approach a few different ways, but none of them seem to work. Long story short I have a table that tracks Assemblies, named tblRevisionSets, which have a SoftwareRevision, HardwareRevision, RevisionSetDate, and Current (I have as a short text field for yes/no, but can use yes/no type if it would help). What I would like to accomplish is that if a new record is created that references the same Assembly number, the new record be set to Yes for current, and any other records with the same Assembly number be switched to No, or Not Current. I'm thinking this would be based on the RevisionSetDate, but my tinkering hasn't produced much success. The ultimate goal in getting this to work is to also have it tie to another table/form which will pull the most current Revision Set for a given assembly automatically when adding inventory. Any insight, suggestions, or advice would be greatly appreciated.

[zmbd]

jshott
Sounds like this is your first database?
If so you would greatly benefit from reading the insights topic on normalization:
[Microsoft Access / VBA Insights Sitemap] > [Database Normalization and Table Structures ]

The yes/no field can be problematic if you have to move data to a larger database (( Why I stopped using Yes/No fields ))

What I don't see in your table [tblRevisionSets] is any reference to the "Assemblies" this would be one step.

Your next issue is the update... I think that you may not have the best database design here... I'll have to think that thru.

[jshott]

This is indeed my first database. I read the article 'Why I stopped using Yes/No fields', which is what inspired me to change it to a short text field. The full list of fields in the tblRevisionSets is as follows, in case it helps: RevSetID (auto), Assembly, SoftwareRevision, HardwareRevision, RevisionSetDate, RevisionNotes, and RevisionSetDate.

[zmbd]

Ok,
If I understand it correctly, then you may not need the "current" entry unless one plans to go back in time :)

so your table

[RevSetID][Assembly][SoftwareRevision][HardwareRevision][RevisionSetDate][RevisionNotes]

Assumeing (yes ;-) ) that [RevSetID] is the primary key and that [Assembly] is the foreign key to your assembly data table. Then with each new record entry one would have a date associated with that entry so you could query against the [Assembly] and then max([RevisionSetDate]) to return your "newest" record entry based on the date. Your only catch here would be if there were two or more entries on the same day for the same [Assembly]...