Data Placement Main table or SubTable

[kvest]

Hello,
I have a database with a main table (tblparticipants) that contains general information about program participants (name, address, phone#'s etc.). I have a one to many table that is used to track home or work visits to these participants as they occur (date and employee and location is entered).

A request has come to me about adding a field(2) to the database that shows whether the participants paper file has been checked out by someone going to do a field visit. By entering this information, it would save alot of staff time in looking for it only to learn that it is in the field. I will not need to track any history of when/who took the file out other than the current time. When it is returned, I would like to clear those field via a check box on the existing field visit form.

My question is, should I place these two new columns (who and when checked out) in the participants table or in the subtable? By putting it in the subtable, I would end up with alot of useless data stored there (dates and who took the file) whereas the other way it would be overwriten and reused as needed.

I am leaning toward putting it in the participants table so it can be overwritten. Is there something I am missing? Also the form where the field visits are entered is based ont the FieldVisit table. How would I go about getting a check box on that form to change or delete the values in the primary table?

Thanks

 

[Remou]

I am always reluctant to lose data, I have often been glad I had some information that seemed useless at the time (but that's paranoia for you). It seems to me that if taking the file is the standard, you can use the visit table to provide the information you need; the person who took the file is the person carrying out the visit.

Alternatively, you could use the after update event of the checkbox to update the relevant table with a date. Maybe:
If Me.chkFileOut Then
Me.HiddenDateOut = InputBox("Enter date")
Else
Me.HiddenPersonOut=""
End If

I hope I have not missed your point.

 

[kvest]

Thanks....I have decided to put it in the subtable. Also, thanks for the suggested code. I am very new at that, so I will take any examples possible.

 

[BobRodes]

<I will not need to track any history of when/who took the file out other than the current time.

Famous last words....