Copying Data to a History Table

[Mike3411]

Hello,
I'm working on a database for a small library system. The user selects a book from a list, and then enters checkout information including who checked it out, their email, the date, etc. I have a second table called transactionHistory, and every time that a book is checked out, I want to make a copy of all that data in a new entry in the transactionHistory table. I'm not really sure how to do this, and specifically how to add this function to the forms I've already created without upsetting their function. Unfortunately I don't have much experience with Access, so any help would be greatly appreciated.
Thanks,
Mike

 

[FoFa]

Sounds more like a design problem to me. I would think you would have a table of Books, a table of members, and a transaction table that ties a book to a member and the type of transaction (check out, check in, shushed for talking, etc). History would be contained in your transaction table automatically then. You would have to ask for checkout information if that person did not exist in the members table then. If the same person checks out three books, is the Who, Email, etc have to be entered 3 times for one checkout?

 

[Mike3411]

The system you described would work too, but I'm trying to keep the system very simple becuase the librarian users do not want to learn a difficult new system. Because the system is for a very small library within the career center at my school, most users will only check out one or two books, ever. So having them all in the database isn't really a time saver. The only reason we keep a transaction history at all is to collect some minor statistics, such as number of times a book is checked out, which departments check out which books, etc. So just copying the data into another table will work fine, but I can't figure out how to add this functionality without starting the form over from scratch, and even then I'm not sure which type of form to use, and how to reference the transaction history table when the form is based around the book list table. Do you have any idea how I should do this?
Thanks,
Mike

 

[sportsguy]

Simple way

the easiest way is to have a Yes/No field, default No, call it Archived, in the main table, and after everytime a new record is posted, a query runs to append all records (1) where Archived = No to the history table. Follow that query with a second query to Update Archived = Yes Where Archived = No.

sportsguy