Output individual records as PDF files

Thanks Scott,

In my view,  30 hours is a lot of time when one has a full time job. But I digress. The reason my knowledge and experience results in thought processes that are different from what's needed to use Access is that I don't have an advanced degree in computer science and information technology.  If I am going to invest time in building a relational data base, do you think learning Visual Basic Studio in order to code a tool from the ground up will be a better approach. Or should I invest the time in learning VBA?

DC

First 30 hours is hardly a drop in the bucket. Second, there is no "convoluted logic"! The problem is that your knowledge and experience results in thought processes that are different from those needed to understand relational databases. I have often said that I am lucky in that my thought processes are conducive to working with databases.

I don't know a lot about other tutorials. I've never had much need for them.

But what you are describing is similar to a questionnaire. You have a series of questions that require answers. What else would call it?

The structure I suggested will work fine for you. Your might need to change respondent to patient. Your problem would seem to be that you built a table with a field for each question and that doesn't work.

Do I still need the workflow you posted or is there a less complicated way to tackle this?

The image of the relationship window, despite a superficial similarity, is not a flow chart.  It is similar to an 'entity relationship diagram', which is a tool used as a preliminary stage in designing a relational database. Before examining what this means we should briefly look at some of the fundamental concepts of the database relational model.

A relational database is a model of that part of the real world with which it is concerned in terms of its entity types.  An entity type can represent physical or abstract types, e.g. in the model I posted Respondents is a physical type (real people) whereas Responses is an abstract type.  Each entity type is represented by a table.

Some entity types are also relationship types, and Responses is a case in point, being a relationship type between ResponseSessions and QuestionnaireAnswers.  It is a many-to-many relationship type, as each answer can be a response in one or more sessions, and each session allows for multiple answers.  More precisely it is a binaryrelationship type as it models an relationship type between two entity types  A many-to-many type like this is always modelled by a table, which resolves the relationship type into two one-to-many, or unary, relationship types.  A unary relationship type is modelled by a foreign key in one table referencing the primary key in another table.  A  key can comprise one or more columns.

When we draw up an entity relationship diagram, those entity types which are relationship types are shown differently in the diagram.  When we translate this into a diagram of the physical model, as n the relationship window, all tables are shown in the same way whether they are solely modelling an entity type, or a relationship type.

As each 'reality' being modelled will differ, both in terms of its entity types and relationship types, the model must accurately reflect the real world entity types and relationship types.  My demo illustrates one generic example, and while it will cope with many scenarios, it should not be taken as prescriptive for the design of a questionnaire databases in all circumstances.  What determines the complexity of the model used is the nature of the reality, so the first step is an analysis of the reality to determine the entity types and relationship types which need to be modelled by tables.

While a table models an entity type, which might or might not also be a relationship type, each row in a table represents an entity which is a legitimate member of the entity type modelled by the table.

Each column in a table represents an attribute of the entity type.  Here we come into contact with a very important process in relational database design, that of Normalization.  This is a formal process of decomposition of a table into a set of related tables to eliminate redundancy, and more importantly, the update anomalies which can arise from redundancy.  Let's take a very simple example of address as rows in an Addresses table:

Address                        Town        County

43 Greengate Street    Stafford    Staffordshire

66 Eastgate Street       Stafford    Staffordshire

This table is not correctly normalized.  The reason it is not correctly normalized is that we are told twice that Stafford is in Staffordshire.  This redundancy leaves the table at risk of update anomalies.  We might add a row to it:

Address                        Town        County

43 Greengate Street    Stafford    Staffordshire

66 Eastgate Street       Stafford    Staffordshire

14 Gaolgate Street      Stafford     Shropshire

We are now told that Stafford is also in Shropshire.  So which is correct?  Obviously the first two rows, but that's only because Stafford is the county town, and its name is part of the county name.  If we had the following:

Address                        Town        County

43 Greengate Street    Lichfield    Staffordshire

66 Eastgate Street       Lichfield    Staffordshire

14 Gaolgate Street      Lichfield    Shropshire

Unless we are familiar with the geography of the English West Midlands region we have no way of knowing which is correct.  In fact none are.  So we decompose the table into two tables like this:

Address                        Town

43 Greengate Street    Stafford

66 Eastgate Street       Stafford

14 Gaolgate Street      Stafford

Town        County

Stafford    Staffordshire

Lichfield    Staffordshire

Neither table now contains any redundancy, so there is no risk of update anomalies.  In the formal language of normalization, all non-key columns are functionally determined solely by the whole of the primary key of the table.  In reality we would not use town names as a key, as these can legitimately be duplicated, but would use a numeric CityID 'surrogate' key.

I've only scratched the surface here of course.  There is a huge volume of academic literature on the database relational model stretching back to 1970 when Codd first introduced the model in his seminal paper on the subject.  While it is not necessary to be fully conversant with this vast body of work, it is important that anyone attempting to design relational databases has a sound understanding of the basic principles of the model.  To assist newcomers to the subject I have posted a number of introductory files to my public databases folder on OneDrive at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

You might like to take a look at the following files:

1.  DatabaseBasics.zip:  As its name suggests this is a general introduction to designing relational databases in Access.

2.  Relationships.zip:  This builds on what is said in the DatabaseBasics demo and illustrates how relationships are built up across a database to achieve the final model.  It uses a very simple medical prescriptions application as its example.

3.  Normalization.zip:  This provides as simple as possible, but no more so, introduction to the subject of normalization by decomposition.

4.  Boolean.zip:  This is an illustration of the use of Boolean logic in querying a database, using Northwind data as its example.