Making Inactive records across tables

[Urban]

Is it possible to deactivate a record in one table and have all corresponding records deactivate in he database? For instance, in table a, can i deactivate a record record X, and have it deactivate in tables b and c as well?

[CJ_London]

if records in table b and c are child tables of table a, then you just deactivate the record in table a - records in tables b and c will then be deactivated by association

[Urban]

Is there a way to use format a check box in a table to do this function? and if so, could checking it yes, then no, then yes make the record activate, deactivate, then activate again?

[Micron]

Is there a way to use format a check box in a table to do this function? and if so, could checking it yes, then no, then yes make the record activate, deactivate, then activate again?

Yes and yes. Any form or report would be based on a query that includes the check field with criteria as T or F as required. YN fields provide minimal information. An alternative is a date/time field name such as Obsolete or Archived or such. If there is a date, it's true or false depending on what the field is all about. Just food for thought.

[xps35]

Active and inactive are not formal concepts. You will therefore have to explain what you mean by this and what the consequences are if a record is not active, for example. If you create a yes/no field in your table (as suggested), this does not mean that Access will automatically do something with it.


It's not clear to me what you mean by deactivating a record in multiple tables. A record can only exist in one table. When it comes to related records in a one-to-many relationship, the question is again what the deactivation means for the related records. Depending on that question, you will also have to take measures for the related tables. Access does not do that automatically either.

[June7]

A record is 'deactivated' if a field value indicates so and then a query criteria excludes records with that value (or lack of value as in a date/time). If parent record is not retrieved in query joining tables then the related child records will not retrieve. However, nothing prevents opening any table independently and viewing all records. Depends on your design and how you manage user interaction with db objects.

So if you 'deactivate' an employee (separation by whatever reason) with value in a date/time field and you query the Employees table for only 'active' employees (field IS NULL), then that's what you get - along with any related records in dependent tables included in query.

[NoellaG]

In our application we have 2 bit fields IsActive and IsDeleted. The latter because we don't allow physical deletions. Inactive records are not shown in actual queries (for example inactive customers are not shown when creating a new sales line) but are shown in historical overviews. Deleted records are never shown.