473,322 Members | 1,538 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Keeping historical Records based on the changes one field

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?!
Jul 15 '16 #1

✓ answered by 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_InstrumentSystems], 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_EquipmentInventory], 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_Date]
[T_EventHistory]![FK_InstrumentSystems]
[T_EventHistory]![FK_EquipmentInventory]
[T_EventHistory]![FK_StandardComments]
[T_EventHistory]![EventHistory_EventDetails]
(keep in mind, PK == Primary Key and FK == Foreign Key)

So the form defaults the current date for the [EventHistory_Date], there are comboboxes that show the human readable text for: [FK_EquipmentInventory], [FK_InstrumentSystems], FK_StandardComments] 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_EventDetails] 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.

2 871
zmbd
5,501 Expert Mod 4TB
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_InstrumentSystems], 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_EquipmentInventory], 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_Date]
[T_EventHistory]![FK_InstrumentSystems]
[T_EventHistory]![FK_EquipmentInventory]
[T_EventHistory]![FK_StandardComments]
[T_EventHistory]![EventHistory_EventDetails]
(keep in mind, PK == Primary Key and FK == Foreign Key)

So the form defaults the current date for the [EventHistory_Date], there are comboboxes that show the human readable text for: [FK_EquipmentInventory], [FK_InstrumentSystems], FK_StandardComments] 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_EventDetails] 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.
Jul 16 '16 #2
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
Jul 17 '16 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: SomeDude | last post by:
Lo group, I would like to know if it is possible to a (string) replace on existing records based on a given pattern. Let's say I have a table containing the following records (strings):...
11
by: Matt | last post by:
Hi everyone, still pretty new to MySQL. I was wondering if there is a way to automatically filter records based on a mysql userlogin name?? I have serveral databases that I want to combine in...
1
by: arthur-e | last post by:
How can you select records based on more than one combo box - I have a combobox that selects records based on name (I'm sure this has been asked a thousand times - web site answer/link could be...
2
by: metaperl | last post by:
I'm actually taking Microsoft's 2779 and just finished a lab where we kept track of our changes to the database. However, I'm not happy with the scripts interface because it does not tell me the...
3
by: meter_man | last post by:
Is there a way to bring historical data into one field? I have activities A1, A2, A3 ,etc in a history table. I need to check for the presence of that data and bring it back if it exists for a...
4
by: Eugene Anthony | last post by:
I have a table that has a DateTime column which uses a DataTime datatype. How do I retrieve a range of records based on the month and year using ms sql? Eugene Anthony *** Sent via...
1
by: rafnavsun | last post by:
Hi Everybody, I need to concatenate all records on one field as one line. Example: Field 1 has 50 records, record1, record2 to record50. I need to display an output in Text that read as: ...
6
by: BEETHOVEN | last post by:
I have an option group called Issue_Type on my main form F1_Member_Demographics_Main. When I select one of the 3 options on the main form from the option group Issue_Type I want to limit the sub...
8
by: cehlinger | last post by:
I am trying to add multiple records based on a date range. I found some examples and have tried them but am getting errors and I can't seem to figure out what the problem is. I am not a VBA...
3
by: Yoni Hasid | last post by:
Hello, I am struggling with finding a solution on how to assign numerical values to text records based on various criteria. I have a table with survey responses where each column has text records...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.