This page contains some hints and tips regarding Microsoft Access in the context of the Term project GenClue. The content is compiled mostly based on student questions. So, please let me know if you think that there are some other interesting issues that are not covered by the Access Manual and which are needed for the project.
The sign “%” that is used in SQL to denote zero or more characters is replaced in Access with “*”. This can be important if you use SQL statements that work with strings, e.g., LIKE.
Actually there are no ISA relationships in the Access implementation because they exist only on the level of an Entity-Relationship model (ERM). By the translation to the Relational Model (RM) they are transformed into corresponding tables. It is characteristic for those tables though that they are related via 1-to-1 relations. The direction of the relation is from (the table that corresponds to) the super-entity to (the table that corresponds to the) the sub-entity. For instance, for the entity pair Publication-Article the relation is from Publication to Article. In the Edit relationships window the super-entity (Publication) is on the left-hand side and the sub-entity (Article) on the right-hand side, as a related table.
One should be consistent with this because giving a wrong direction can cause problems if one uses referential integrity. For instance, if in our example we have a pair Publication-Thesis and the relation is from Thesis to publication instead of the other way around, then thesis becomes in fact a super entity of publication and indirectly also of article. If now the referential integrity is used Access displays an error message if we try to enter an ID number for a new article which is not already an ID number of a thesis. This is because Thesis is considered as a super-entity of Article.
With the Wizard you can create forms that are based on more than one table. Also forms for entering data for the tables that correspond to the ISA entities can be made in this way. For instance, if we want to enter data about a Publication, then, besides the common attributes which are in Publication, we have to enter also the attributes which are specific to each of the sub-entities, e.g. Article. In that case one solution could be to design separate forms for each sub-entity, i.e. publication type. Such a form would contain all attributes from Publication plus the specific attributes for the sub-entities. Only the Publication ID does not appear twice. If the referential integrity is required for the relation between Publication and the sub-entity, then in both in the Publication table and the sub-entity table the Publication ID field is automatically recorded. In other words, Access “knows” that this is the same field.
With the Wizard it is quite easy to create forms that are based on three or more tables. However, this is often not so effective. If you want to involve a third table it is often better to use a combination of the Wizard and the Design view. For instance, if you want to have in the form for the Article the possibility to enter genes which are mentioned in the article then you need a form which is based on three tables: Article, Publication and Gene_Publication. In that case you can first make a form based on Article and Publication. Then in Design View you can add a sub-form which is based on Gene_Publication. If you have referential integrity and cascade updates on for the relations between those three tables, then the Publication ID is automatically listed in the sub-form for Gene_Publication, so one needs to add only the corresponding Gene IDs.
The application can have several levels of switchboards, i.e., not only the main (default) one. The switchboards of the lower levels can be created in the same way as the main one. Such a hierarchy of switchboards is useful to implement menu levels. For instance, in the main switchboard you can have the option to choose to enter data for a publication. Choosing this option can activate another switchboard when you can select the type of publication. Each option of this switchboard can be coupled with the corresponding form for the publication type.
Sometimes it can happen that you have made a switchboard using the Switchboard manager from the Tools option in the main menu, but nevertheless the switchboard does not appear among the Forms, as should normally be the case. The remedy is to delete the switchboard items table from Tables.