databasedev.co.uk - database solutions and downloads for microsoft access

Avoid multiple database fields that contain similar data in Access:

When designing tables in Microsoft Access, you should avoid creating multiple fields that repeat the same kind of data. If a table stores the same kind of data in more than one field, it's hard to search for a particular piece of data because it could be in any of the fields. In addition, it's difficult to sort data and do calculations because you might need to include data from several fields to get accurate results.

For example, if you are designing a questionnaire database, you might eventually find this table design difficult to use.

RespondentID	Question 1	Question 2	Question 3
001	Yes	Yes	No
002	No	Yes	No
003	Yes	No	Yes

It's difficult to create a query that returns only Yes responses, because response data is stored in three different fields. However, it's easy to create a query that returns this information if you use a table design that stores each type of data in only one field, such as the following table design:

RespondentID	QuestionID	Response
001	1	Yes
001	2	Yes
001	3	No
002	1	No
002	2	Yes
002	3	No
003	1	Yes
003	2	No
003	3	Yes

Because each type of information is contained in only one field, searching, sorting, and calculating data is much easier.

For example, you can easily create a query that will return only those records where the Response to a question was No:

SELECT [tblQuestionnaire].RespondentID, [tblQuestionnaire].QuestionID
FROM tblQuestionnaire
WHERE ((([tblQuestionnaire].Response)=No));

For further information on creating Queries, take a look at Database Solutions for Microsoft Access Queries