Inactive Field

[Dee300]

Is there a way to have an inactive employee not show in a related table or at least have it not show on the report? I have a report that shows a vendor record and the employee responsible. If I change the employee responsible to inactive (in the employee form) , the name disappears from the vendor form as it should, but it still shows up in the vendor table, which means it still shows up in the report. If I change the query to not show inactive employees, it will not show the vendor record at all. I still want to see the vendor record but the employee name should not show on the report. For example, I have a vendor record that had “Michael Smith” responsible for that vendor. If I make Michael inactive (in the employee form) his name disappears from the record in the vendor form, but still shows in the vendor table and vendor report.

[orange]

What exactly do you want to /think should happen?
If you were looking at a transaction that occurred in the past, and that record has significance, you wouldn't really want to delete it.
Your employee list or vendor list will have a life cycle so to speak. New employees are hired, other leave or retire. Vendors may come or go etc. But if you are reporting a historical fact, then you are dealing with the situation as it existed at that time.

( I do realize that there is a tendency these days to rewrite some history to make things politically correct, but that doesn't apply to your case)

If you want to identify inactive records, then add a field to your table (ISActive with values Yes/No or True/False ) and in a query supporting a Form or Report select records where IsActive = Yes. You may also want to consider a DateField associated with the IsActive, if that is important to you.

In any event you should not be deleting employees from your Table, if you have significant transactions involving that Employee.

[Dee300]

Thank you so much for your reply. I agree with you but I was not sure if this was normal behavior for an inactive field. The reason this came up is that a client was wondering why an employee that was recently marked inactive was showing on the vendor report. So I am guessing I should tell the client they should just select another "ermployee responsible" for that vendor if they dont want the inactive person's name to show.

[orange]

Well the question now seems to be "Which Employee is responsible for a certain Vendor?
So, if a business rule is
An active Employee is responsible for an active Vendor

So if an Employee is made inactive, another employee must be made active. These steps have to occur completely - no half way measures here.
If that rule is enforced in the application code, then Only Active Employees should show up on Reports.

Another check would be that
- every Vendor must be associated with 1 responsible active Employee

You could run checks like this to ensure nothing is falling thru the cracks.

[SteveH2508]

How about a calculated field in the report (or in the recordsource query) to the effect of
IIf([IsActive]=False,"",[EmployeeName]) where IsActive is the Yes/No field in the Employee table.