Counting

[Guest]

Hello again,

I am trying to report on patient surveys. I would like my report to
summerize data based on the qtr. I can do this just fine for the scores,
(aprox twenty questions grade one to five) The problem I have is I would also
like to summarize the count of which cities we responded to. I would like a
count of each of the six cities. I can not seem to come up with a way to do
this. Any ideas on which direction I should go. Thank you Jason

 

[Jeff Boyce]

Jason

Coming up with a design for a query will depend on how your data is
structured. Is there a way, via a query, for you to join your score
information with your city information?

 

[Guest]

Thank you here is the text of the Qry that convert the table data to usable
numbers for scoring. The form I enter data into is based on the scores tbl.
This table qry contains all the data I want to work with.

SELECT SCORES.Index, SCORES.[Date of Service], SCORES.City, SCORES.[First
Time Using Rockingham], SCORES.[Patients Age], SCORES.[Patients Gender],
SCORES.[B1 Intake Method], (SCORES!B2-1)*25 AS B2, (SCORES!B3-1)*25 AS B3,
(SCORES!B4-1)*25 AS B4, SCORES.[B SECTION COMMENTS], SCORES.C1, SCORES.C1A,
(SCORES!C2-1)*25 AS C2, (SCORES!C3-1)*25 AS C3, SCORES.[C SECTION COMMENTS],
(SCORES!D1-1)*25 AS D1, (SCORES!D2-1)*25 AS D2, (SCORES!D3-1)*25 AS D3,
(SCORES!D4-1)*25 AS D4, (SCORES!D5-1)*25 AS D5, (SCORES!D6-1)*25 AS D6,
SCORES.[D SECTION COMMENTS], (SCORES!E1-1)*25 AS E1, (SCORES!E2-1)*25 AS E2,
(SCORES!E3-1)*25 AS E3, SCORES.[E SECTION COMMENTS], SCORES.[CONTACT
BILLING], (SCORES!F1-1)*25 AS F1, (SCORES!F2-1)*25 AS F2, SCORES.[F SECTION
COMMENTS], (SCORES!G1-1)*25 AS G1, (SCORES!G2-1)*25 AS G2, (SCORES!G3-1)*25
AS G3, (SCORES!G4-1)*25 AS G4, SCORES.[G SECTION COMMENTS], SCORES.[MAY WE
CONTACT], SCORES.[CONTACT NAME], SCORES.[PHONE NUMBER]
FROM SCORES;

 

[Jeff Boyce]

Jason

At first glance, your SQL statement seems to be pointing to a number of
"repeating fields" (e.g., B2-1, B3-1, C2-1, ...). While this is a necessary
design for ... a spreadsheet, it is an unnecessary evil for a relational
database table.

Before you try handling multiple repeating fields, consider spending a bit
more time normalizing your data structure. It will pay off big time, since
Access' functions and features are designed to work with
relational/normalized data -- trying to work with a spreadsheet design will
only cause you and Access headaches.

By the way, you also may wish to reconsider using [PatientAge] as a field.
You will have to continuously keep this updated if you are keeping current
records. It's much easier to keep [PatientDOB] and be able to use a query
or a simple function to calculate [CurrentAge], based on DOB.

 

[Guest]

Jason

At first glance, your SQL statement seems to be pointing to a number of
"repeating fields" (e.g., B2-1, B3-1, C2-1, ...). While this is a necessary
design for ... a spreadsheet, it is an unnecessary evil for a relational
database table.

B2 b3 c1 c2 etc refer to question on my surveys. I enter the score for each
question on a scale of one to 5 the c1-1*25 converts the score to a
percentage value I can use to grade that question for all records. Is there a
better way to do this?

Before you try handling multiple repeating fields, consider spending a bit
more time normalizing your data structure. It will pay off big time, since
Access' functions and features are designed to work with
relational/normalized data -- trying to work with a spreadsheet design will
only cause you and Access headaches.

By the way, you also may wish to reconsider using [PatientAge] as a field.
You will have to continuously keep this updated if you are keeping current
records. It's much easier to keep [PatientDOB] and be able to use a query
or a simple function to calculate [CurrentAge], based on DOB.

This data is one time only and does not need to be updated. It is only used
to get an average pt age at the time the surveys were completed. Thank you
for your thoughts.

 

[Jeff Boyce]

Jason

One-time use, no problem with [PatientAge].

For a "better" design for surveys/questionnaires/tests, see:

Take a look at Duane Hookom's AtYourSurvey as a model for data structure.

See:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Regards

Jeff Boyce
<Office/Access MVP>

Jason

At first glance, your SQL statement seems to be pointing to a number of
"repeating fields" (e.g., B2-1, B3-1, C2-1, ...). While this is a
necessary
design for ... a spreadsheet, it is an unnecessary evil for a relational
database table.

B2 b3 c1 c2 etc refer to question on my surveys. I enter the score for
each
question on a scale of one to 5 the c1-1*25 converts the score to a
percentage value I can use to grade that question for all records. Is
there a
better way to do this?

Before you try handling multiple repeating fields, consider spending a
bit
more time normalizing your data structure. It will pay off big time,
since
Access' functions and features are designed to work with
relational/normalized data -- trying to work with a spreadsheet design
will
only cause you and Access headaches.

By the way, you also may wish to reconsider using [PatientAge] as a
field.
You will have to continuously keep this updated if you are keeping
current
records. It's much easier to keep [PatientDOB] and be able to use a
query
or a simple function to calculate [CurrentAge], based on DOB.

This data is one time only and does not need to be updated. It is only
used
to get an average pt age at the time the surveys were completed. Thank you
for your thoughts.

--
Regards

Jeff Boyce
<Office/Access MVP>