Write record details to another table as history

[Thomman]

Good morning.

I have a vehicle database in MS Access. Some of tables are VehicleMaster, SectionHistory, DriverHistory etc…

Main fields in VehicleMaster:
VehicleID, Description, RegDate, RegExp, DriverID, and Status etc…
Main fields in DriverHistory: VehicleId, DriverID,StartDate,EndDate.
Main fields in SectionHistory : VehicleID, SectionID,StartDate,EndDate

My object is; If there is a Change in Driver ID in VehicleMaster form, it should ask for the confirmation then if 'YES' it should ask for the Start Date. Then these details should pass to DriverHistory table. The StartDate of the Current Driver should be the EndDate of the previous Driver.


The same logic should be applied for SectionID also. How can I achieve this?

James

[jzwp11]

If a vehicle can be assigned to many drivers over time (but only 1 at a specific time), you have a one-to-many relationship. As such, your vehicle table is not structured properly. You should not have the driverID in the vehicle table. I assume that you also have the sectionID in the vehicle table as well (not shown in your original post). That would also be incorrect following the same logic.

You can use a subform in your main form to display the driver history and another subform to display the section history. To change drivers, you would add an enddate to the current driver (shown in the subform) and then add a new record for the new driver. You would follow the same approach for the section. If you would rather not use subforms and display only the current driver/section, you can use the DLookup() function in an unbound control on the form (you would need separate ones for the driver and section). Then you would also need some code that would do an update query (for the current driver/section) and an append query to handle the new driver/section.

Out of curiosity, is a driver related to a section?

[Thomman]

Hi,
Yes, Driver is related to a Section. Vechile can be assinged to 1 driver at a time. This history file is important to find out incase of any traffic violations. So StartDate & EndDate also very significant. My logic to do this is: If DriverID change i.e. If OldValue of DriverID <> current value of DriverID in the form then Open the DriverHistory file and replace the EndDate of the VehicleID with the StartDate then insert a new record with VehicleID, StartDate, Comments etc..
As I am not an expert in MS Access, I cannot achieve this..

Thomman

[jzwp11]

This is not an Access issue but rather a table structure/design issue. If a vehicle can be driven by multiple people over time then you have a one-to-many relationship which requries a separate but related table (i.e. your driver history table), but if you store the drivers in the history table it would violate normalization rules to also have the driver in the vehicle table. (The same would be true of the section reference) You might want to take a look at this site for more on the rules of normalization.

There are ways to display the current driver in a form that shows the vehicle and queries can be used to update the current driver's end date and create a new record for a new driver.

When you have had a chance to review normalization, please post back and we'll help you with the form & queries you will need.