Keeping historical Records based on the changes one field

[omaraux]

Hi,
I am back again.

Background: Working on Access 2010

I have a Job table with several fields, however, main fields are

-Property (This is where the jobs are done)
-Service Date( This runs periodically in the same property)
-Completion date (Each Service date has a Completion date)
-Engineer (An engineer could work on any service date on any property)
-Job Sheet (Each service date has a job sheet)

Question.

Given an example of Job on Property "A" being done 4 times in a year, what is the best way to save a record based on each Service date change so that it duplicates the Job id and save the row as unique record?
More info- That means, at any point in the year i can run a report on any property and see the previous details of service done.

Any help?!

[zmbd]

You might start here with: Database Normalization and Table Structures

This One way to do this:

What I do is keep a table called t_events, for example in my lab I have instrument systems comprised of multiple pieces of equipment and consumables so I have a table that lists the systems, [T_InstrumentSys tems], this has a lot of other related information such a the type of system then there is another tale that keeps track of the inventory, [T_EquipmentInve ntory], this also has a lot of other related fields that point to the manufacture table etc...

Then there is the [T_EventHistory]
[T_EventHistory]
[T_EventHistory]![PK_EventHistory]
[T_EventHistory]![EventHistory_Da te]
[T_EventHistory]![FK_InstrumentSy stems]
[T_EventHistory]![FK_EquipmentInv entory]
[T_EventHistory]![FK_StandardComm ents]
[T_EventHistory]![EventHistory_Ev entDetails]
(keep in mind, PK == Primary Key and FK == Foreign Key)

So the form defaults the current date for the [EventHistory_Da te], there are comboboxes that show the human readable text for: [FK_EquipmentInv entory], [FK_InstrumentSy stems], FK_StandardComm ents] so in the table [T_EventHistory] one would only see a "1" or "2" not "System Location1", "Titration Stand 1", "End of Useable Life" or "Reversed standard operation"; and finally, of course [EventHistory_Ev entDetails] has a 255 character limit for a brief detail of what is going on with the situation.

Now whenever I need to I can pull reports by Instrument System, Individual piece of Equipment, Manufacture, and many other criteria, (I've not listed all of the tables nor their structure), and I can do this by total history or date range. Indeed I have one massive report that pulls by manufacture and then shows each of the manufactures equipment as installed on each system for the entire history.... I usually filter this down - considerably!

Yes there are a few stored queries that join across the tables to allow the reports to be ran; however, these are simple ones and easily built from the query editor. Occasionally, I've had to use the SQL view to build a union query.

So with

-Property (This is where the jobs are done)
-Service Date( This runs periodically in the same property)
-Completion date (Each Service date has a Completion date)
-Engineer (An engineer could work on any service date on any property)
-Job Sheet (Each service date has a job sheet)

One might have:
a table for Properties
a table Engineers
a table JobSheet

Then an event table that relates these three tables and also contains Service_Date and Completion_Date
[t_EventHistory]
[t_EventHistory]![PK_EventHistory]
[t_EventHistory]![FK_Property]
[t_EventHistory]![FK_Enginneers]
[t_EventHistory]![FK_JobSheet]
[t_EventHistory]![Service_Date]
[t_EventHistory]![Completion_date]

Of course, say your JobSheet table has the listing of the engineers assigned then one wouldn't need the [t_EventHistory]![FK_Enginneers] and also say the JobSheet table also has the property information then [t_EventHistory]![FK_Property] might also be better assigned to this table instead of the history table. You'll have to carefully consider what information you have and how to organize it.

[omaraux]

Excellent Starting point,

Thanks Zmbd,
Yes, I have Engineers and Properties Tables, The Job Table would work similarly as the Events table in your case.

From the Package and Job table, I derived a query called QryJobs.

By following your elaborate scenario, I can see a good correlation with my situation.

Let me go through it.

Regards
Denis El