Using multiple dates and creating reports

[SueG]

I have an Access 2003 database of staff details. Qualifications for the staff
need to be rechecked every 3 years (from their start date, so they all vary).
Do I need to create a new field for each recheck or is there another way of
detailing that this is their 1st, 2nd, etc recheck?
I then need to create a report showing who is being rechecked this month. I
guess this would involve a query but I'm not sure how to create it?
Any help would be much appreciated.

 

[Ken Snell \(MVP\)]

Adding a new field for each recheck violates database design. Don't do it.

Instead, create a new table that will hold one record for each staff member
for each recheck:

tblStaffRechecks
StaffRecheckID Autonumber; Primary Key
StaffID Foreign key to Staff table
RecheckID A number identifying which recheck this is
< other fields that you may want >

Then you can add a record to the table for each recheck, which makes it very
easy to query the historical status of a staff member and you won't need to
add more and more fields to your Staff table.

 

[SueG]

Thanks Ken.

Adding a new field for each recheck violates database design. Don't do it.

Instead, create a new table that will hold one record for each staff member
for each recheck:

tblStaffRechecks
StaffRecheckID Autonumber; Primary Key
StaffID Foreign key to Staff table
RecheckID A number identifying which recheck this is
< other fields that you may want >

Then you can add a record to the table for each recheck, which makes it very
easy to query the historical status of a staff member and you won't need to
add more and more fields to your Staff table.