Cleaning up tables

[Louisa Ray]

We have a physician that is wanting to clean up some database tables and
delete some doctors from a table because these doctors are no longer
associated with the practice. However, if we run a report to view patients he
took care of - that won't generate a report - correct? Should we leave all of
the old doctors listed in the table, or create another table for doctors that
have left the practice? What would be the best way to handle something like
this? We have Office 2007. I just need some help thinking this through.

Thank you,
Louisa

 

[John Spencer]

The best way would be to add a field to the Doctors table that tells you they
have left the practice. The field can be a yes/no field if all you need to
know is that they are no longer active in the practice. Or you can use a date
field that records WHEN they left the practice.

You can then use the field to screen out the doctors in situations where you
no longer need them to appear. If the field is a yes/no then screen them out
based on the field being false, if the field is a date field that records the
departure date then use Field is Null to show the active doctors.

Moving the doctors to another table is a bad design decision and will
complicate life a lot.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Louisa Ray]

Thank you very much.

The best way would be to add a field to the Doctors table that tells you they
have left the practice. The field can be a yes/no field if all you need to
know is that they are no longer active in the practice. Or you can use a date
field that records WHEN they left the practice.

You can then use the field to screen out the doctors in situations where you
no longer need them to appear. If the field is a yes/no then screen them out
based on the field being false, if the field is a date field that records the
departure date then use Field is Null to show the active doctors.

Moving the doctors to another table is a bad design decision and will
complicate life a lot.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County