Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17

    Question Unanswered: Creating a History Table

    Hi!

    I am still new with access

    I would like to create a history table, lets call it (table B) based on another (table A). Everytime a record is changed in table A, I would like that record to be added to table B as a new record.

    Is this possible?

    Thanks,

    Owen

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi

    is there a particular reason for duplicating data in this way(if i understand you that is)

    else you could set the record to have its constant data copied and add the adjustment to the records required. In this way you will have all the information regarding that record in the same table so when called it could produce the full history for you with out having to worry about secondary tables and tracing from 2 seperate tables when an enquiery takes place


    gareth

  3. #3
    Join Date
    Feb 2004
    Posts
    142
    If the back end is Access then you will have to add the code in whatever process you are using to save the record. Transaction processing could be a bit of a pain on a bound form. Access has no triggers on the tables like some high end databases.

    Transaction processing is making sure that both tables get updated or the insert/update gets rolled back on both tables.

    On a bound form you could put the code in the after update event of the form. But be sure to test for the record in table A to be sure the changes were commited there first before writing to table B.

    If you are using code to do the insert/update then use BeginTrans, EndTrans before and after the two updates. and trap errors and execute Rollback. Much easier.
    KC

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17
    Originally posted by garethfx
    Hi

    is there a particular reason for duplicating data in this way(if i understand you that is)

    else you could set the record to have its constant data copied and add the adjustment to the records required. In this way you will have all the information regarding that record in the same table so when called it could produce the full history for you with out having to worry about secondary tables and tracing from 2 seperate tables when an enquiery takes place


    gareth
    The reason is I work in an aerodynamics laboratory and I have data of calibrated instruments in the tables. I have calibration dates with the coefficients in this table for certain transducers and we do not want to overwrite them everytime we calibrate, so we can always roll back to an earlier date or if an engineer needs those calibrations from the past. That is why I need a History table.

    Thanks for your help

    Owen

  5. #5
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Tried to do exactly what you asked for...

    Here is a dbase written in Access 2000, it has 2 tables, A and B.

    Table A has machine details (serial number, location)... and 4 specs, table B has only a key and the 4 specs, and a date field.

    There are 2 forms, frm_update_KH, which you should open. the other form is a sub report of this.

    The update form has a listbox, this displays only 2 columns although all the others are hidden. (You can add more).

    When you select a machine, the afterupdate on the listbox, takes the details from the listboxes hidden columns and populates all the fields on the form (except the sub_form of course!)

    The fields next to the listbox are 'current', they are locked, the fields on the right can be updated.

    When you update, it uses access 'docmd' to run a sql string. One for update, to update Table A and one to Insert (append) to Table B.

    It then refreshes all the forms data..

    Now the icing on the cake, the sub form shows historical data in chronological order..

    Ta-daaaaa

    Not quite how I would do it, but thats what you asked for.

    Regards

    Ken.
    Attached Files Attached Files

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17
    Hi! First I want to thank you for helping me on this, as I am a beginner, I only know basics in access. This is great, it's exactly what I have been looking for, but I get an error when I apply the update, here is a screenshot of the error message, can you help me?

    Owen

    Originally posted by Ken_Hart
    Tried to do exactly what you asked for...

    Here is a dbase written in Access 2000, it has 2 tables, A and B.

    Table A has machine details (serial number, location)... and 4 specs, table B has only a key and the 4 specs, and a date field.

    There are 2 forms, frm_update_KH, which you should open. the other form is a sub report of this.

    The update form has a listbox, this displays only 2 columns although all the others are hidden. (You can add more).

    When you select a machine, the afterupdate on the listbox, takes the details from the listboxes hidden columns and populates all the fields on the form (except the sub_form of course!)

    The fields next to the listbox are 'current', they are locked, the fields on the right can be updated.

    When you update, it uses access 'docmd' to run a sql string. One for update, to update Table A and one to Insert (append) to Table B.

    It then refreshes all the forms data..

    Now the icing on the cake, the sub form shows historical data in chronological order..

    Ta-daaaaa

    Not quite how I would do it, but thats what you asked for.

    Regards

    Ken.
    Attached Thumbnails Attached Thumbnails error.jpg  

  7. #7
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Yep I know what that is.

    You are using comma's.

    I thought measurments would be full stops.

    Using commas in a field will think that there are more fields than expected.

    Do you use commas ? If so I can go and re-write it.

  8. #8
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17
    I think I got it working, I tried the database at home, didn't work, I am at work now and works fine, I guess everything is well.

    Thanks a bunch!!!!!

    Owen

    Originally posted by Ken_Hart
    Yep I know what that is.

    You are using comma's.

    I thought measurments would be full stops.

    Using commas in a field will think that there are more fields than expected.

    Do you use commas ? If so I can go and re-write it.

  9. #9
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Good to hear,

    If you do use commas, we/I could alter the sql string by making the values into text

    it would be a case of simply putting in a few more " into the sql string.

    If I can help anymore, let me know.

    ~Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •