Adding new information to a field containing existing info

[Guest]

I have 2 tables. One called ITEM and one TRANSACTION. The item table holds information about particlaur items (of course), and the transaction table records the rental and returning of these items
Lets say, I have 2 items in my Item table (TV and Laptop). Each item has a field called Condition (which is basically set to Brand New when originally entered into the DB table assuming all the items are purchased Brand New)
So lets say that 1 person rents the item using the Transaction Table and returns it with no damage, so the condition of the Laptop stays the same
But, the next time it is rented, the Laptop is returned with 5 missing Keyboard buttons. Using the Transaction table (and a form based on this form), I would like to append that current condition so that it gets placed into the Item's condition field in the Item table without overwriting the old and existing conditions. That way a HISTORY of the Condition can be kept (also can record any improvements or installation of new parts)

Example: Brand New (original condition), THEN Missing 5 Keys, THEN 5 Keys Replaced, etc

Then the condition of that item in the Item table could be: (1)Brand New, (2) Missing 5 Keys, (3) Replaced 5 Missing Keys, (4) ETC...

Thanks,

 

[Nikos Yannacopoulos]

I would suggest a different design to cope with this. You could either:
(a) move the condition field from table ITEMS to table TRANSACTIONS, or
(b) make a third table CONDITION bound on item to the first one (itemID,
Condition, Date)

Option (a) assumes that condition changes upon transactions only.
Option (b) is my preferred one in that (1) it allows for condition changes
independent of transactions (an item can be repaired between rentals), and
(2) it eliminates data duplication where condition of an item stays
unchanged for several consecutive transactions (a common case, I would
guess).

HTH,
Nikos

I have 2 tables. One called ITEM and one TRANSACTION. The item table holds

information about particlaur items (of course), and the transaction table
records the rental and returning of these items.

Lets say, I have 2 items in my Item table (TV and Laptop). Each item has a

field called Condition (which is basically set to Brand New when originally
entered into the DB table assuming all the items are purchased Brand New).

So lets say that 1 person rents the item using the Transaction Table and

returns it with no damage, so the condition of the Laptop stays the same.

But, the next time it is rented, the Laptop is returned with 5 missing

Keyboard buttons. Using the Transaction table (and a form based on this
form), I would like to append that current condition so that it gets placed
into the Item's condition field in the Item table without overwriting the
old and existing conditions. That way a HISTORY of the Condition can be kept
(also can record any improvements or installation of new parts).

Example: Brand New (original condition), THEN Missing 5 Keys, THEN 5 Keys Replaced, etc.

Then the condition of that item in the Item table could be: (1)Brand New,

(2) Missing 5 Keys, (3) Replaced 5 Missing Keys, (4) ETC....