- database solutions and downloads for microsoft access

Designer for Microsoft Access
Create complex MS Access databases without being an expert in relational database design! Designer for Microsoft Access asks you plain-language questions about what you want to manage with your database, and creates the tables and relationships automatically. Free trial available

Database Design

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