Microsoft Access how to get a field name to only show with certain entries

Question

My database in Access is listed by classification (e.g. cinema, press (local), publication (local) etc).

For the cinema classification I want to have a column titled 'the number of screens in the cinema'. However, I do not need this column for the other classifications.

How do a create a column that only appears for cinemas? I don't want the column just to be filled with N/As for non cinema classifications.

Answer 1

What you're asking about is called database normalization. This is a design process database designers use to eliminate redundant or inconsistent data from a DB. In your case, having a Number of Screens field for records that don't have screens would not make sense, hence the need for normalization.

To solve your problem you need to add a second table to your database and put the field there. Then you link the main and second table with a unique ID field. Finally you only create a record in the second table if a record in the main table needs the additional detail.

Here's an overview of that process:

1. Add a field to your main table of the type Autonumber. Name it CinemaID. Let Access use this as the table's Primary Key.
2. Create another table. Perhaps name it Cinema_Detail.
3. In the new table add a field of the type Long. Name it CinemaID (use the same name as the primary key field in the first table). Do not let Access make this a primary key. Do tell access the field is Required and values in this field need to be unique (otherwise it would be possible to have multiple records in this table that correspond to a single record in the main table, which is contrary to your request).
4. Add another field for the number of screens.
5. In the Database Relationship Manager, create a 1-to-Many relationship between the CinemaID fields in your two tables.

Now to specify how many screens a cinema has, add a record to the Cinema_Detail table and provide the CinemaID for the corresponding record in the main table along with the value for the number of screens.

More Information

• Description of the database normalization basics

In case you're wondering

It's not possible to do what you're asking directly in the main table. In Access fields either exist in a table, or they don't. There's no middle ground. Access is a database engine and as such it adheres to the rules of proper database design.