query table

[Peter]

Hi,

I am using Access 2000. I have a query table which links a two data tables
together, "Employee_Name" and "Time_In".

"Employee_Name" has the following fields:

StaffName, CardNo

"Time_In" has the following fields:
CardNo, TimeIn, Date

I link the CardNo fields together in my query table to get the result of

StaffName , TimeIn, Date

But I was wondering what if a staff resigns and a new staff uses the old
staff card. How can I tell the database that the new staff will start using
a card from a specific date onwards without affecting the past records.

Thanks.

Peter

 

[Ken Snell]

If all ACCESS has in its "knowledge" is a CardNo value, how can it possibly
know that all records with the primary or foreign key of CardNo are now to
be ignored in favor of new records with the primary or foreign key of
CardNo. In other words, you can't....unless you remove the previous records
from the table so that they "don't exist" any more.

Otherwise, you will need to expand/change what is "unique" for identifying a
staff member. Obviously, CardNo is not unique if it can be reused.

One idea: add a primary key to the Employee_Name table (name if EmpPK) and
make it an autonumber format. Then use that primary key as the linking field
to the Time_In table, instead of using CardNo field.