Too many fields for reports seen to handle

A

AmandaH

To any one that can help,

I have this report that was created through PDF, which is then
downloaded into my Access 2003 database. All this works great. However
this report has two columns for each question, seven sections with
about 10 questions per section. I think that you many be able to see
where I am going with this.

What I have been asked to do is create ONE form that has all the
questions and fields. I tried to do it all in one query, which of
course is over 255 columns, I tried doing sub forms for each section,
however what I get is all of section one followed by all of section 2
etc, instead of ONE single flowing form for each record.

I am not sure how to solve this problem; any help would be gratefully
received.

Thanks,
~Amanda~
 
J

Jeff Boyce

Amanda

Perhaps I have a mistaken impression of PDF, but I thought that it was
largely an image. I don't understand how you are getting the data depicted
in the PDF to load specific fields in an Access table.

How does a query fit into this?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
A

AmandaH

How I am importing the data is not really the question. It is how I am
able to review the data such that all of my many many fields can be
shown on one continuous report.
 
P

Pete D.

How are your tables arranged? Do you have the questions, sections and other
questions in different tables or all one table?
Pete D.
 
A

AmandaH

In total there are 4 tables,
1) Pre Section 1-6
2) Post Section 1 -6
3) Pre Section 7
4) Post Section 7

We did it this way because all of the questions would not fit into one
table. We then have querys that pulled in the Pre and post of each
section. Example: QuerySection1 contains all Pre and Post questison.
 
J

Jeff Boyce

If I don't understand how your data is organized, I can't offer meaningful,
specific suggestions. Perhaps one of the other newsgroup readers can offer
an approach that works for you.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

Pat Hartman \(MVP\)

You are thinking spreadsheet instead of relational database. Your idea of
one question per column is exactly how you would solve the problem with a
spreadsheet. However, that is not how you should solve the problem with a
relational database. I will describe a very simple questionnaire structure.
You may need a more complex one. The database is broken down into two
parts - the part that defines the survey and the part that records the
survey responses.

DEFINE SURVEY SECTION:

tblSurvey
SurveyID
SurveyName
..... - this table allows you to define multiple surveys and let them share
questions.
tblQuestion
QuestionID
QuestionName
..... - this table allows you to define each question as a separate ROW

tblSurveyQuestion
SurveyQuestionID
SurveyID
QuestionID
QuestionSeq
..... - this table allows you to define which questions go to which surveys
and the order in which they should appear.

COLLECT SURVEY RESPONSES SECTION:
tblPerson:
PersonID
LastName
FirstName
.... - this table lets you define the people who will respond to the survey.

tblPersonSurvey:
PersonSurveyID:
SurveyID
PersonID
DateTaken
..... - this table lets you define which people took a particular survey and
when

tblSurveyAnswers:
PersonSurveyID
SurveyQuestionID
SurveyAnswer
.... - this is the answer given by a particular person to a particular
question of a particular survey.

Your survey may be more complex. For example, you may need to define an
acceptable range for an answer if the survey is multiple choice. You may
need to define a list of potential answers for each question and also the
correct answer if you are using this structure to compose tests.

If you want to separate the questions so that you don't have just one long
list, you can add a SectionName to the tblSurveyQuestion that you can use to
create breaks. You can then create a tabbed form and the subform on each
tab can select only the questions for a particular section.

Moving from a spreadsheet mentality to a database one presents some
challenges. In a spreadsheet, the presentation layer and data layer are
combined but in a relational database they are separate. How you store the
data is not necessarily how you will view it. You have many more view
options since they are separated from the data storage structure. But it is
very important to not let "presentation" dictate "structure" or you will end
up with a coding nightmare beyond belief. You will hate Access and think
that it is the most stupid program ever invented. So take a little time now
to absorb my suggested structure before you move on.
 
P

Pete D.

That was excellent, Thanks Pete D.

--
Pete D.

Pat Hartman (MVP) said:
You are thinking spreadsheet instead of relational database. Your idea of
one question per column is exactly how you would solve the problem with a
spreadsheet. However, that is not how you should solve the problem with a
relational database. I will describe a very simple questionnaire
structure. You may need a more complex one. The database is broken down
into two parts - the part that defines the survey and the part that
records the survey responses.

DEFINE SURVEY SECTION:

tblSurvey
SurveyID
SurveyName
.... - this table allows you to define multiple surveys and let them share
questions.
tblQuestion
QuestionID
QuestionName
.... - this table allows you to define each question as a separate ROW

tblSurveyQuestion
SurveyQuestionID
SurveyID
QuestionID
QuestionSeq
.... - this table allows you to define which questions go to which surveys
and the order in which they should appear.

COLLECT SURVEY RESPONSES SECTION:
tblPerson:
PersonID
LastName
FirstName
... - this table lets you define the people who will respond to the
survey.

tblPersonSurvey:
PersonSurveyID:
SurveyID
PersonID
DateTaken
.... - this table lets you define which people took a particular survey
and when

tblSurveyAnswers:
PersonSurveyID
SurveyQuestionID
SurveyAnswer
... - this is the answer given by a particular person to a particular
question of a particular survey.

Your survey may be more complex. For example, you may need to define an
acceptable range for an answer if the survey is multiple choice. You may
need to define a list of potential answers for each question and also the
correct answer if you are using this structure to compose tests.

If you want to separate the questions so that you don't have just one long
list, you can add a SectionName to the tblSurveyQuestion that you can use
to create breaks. You can then create a tabbed form and the subform on
each tab can select only the questions for a particular section.

Moving from a spreadsheet mentality to a database one presents some
challenges. In a spreadsheet, the presentation layer and data layer are
combined but in a relational database they are separate. How you store
the data is not necessarily how you will view it. You have many more view
options since they are separated from the data storage structure. But it
is very important to not let "presentation" dictate "structure" or you
will end up with a coding nightmare beyond belief. You will hate Access
and think that it is the most stupid program ever invented. So take a
little time now to absorb my suggested structure before you move on.
Click to expand...
 
Top