Calculation problem

[gillespiea23]

I have a table with participants, whether they completed training, what the
pre test score is, and what the post test score is for five different
trainings. My query and subsequent report needs to tell me the avg. pre test
and post test score for all attendees at a training, but my problem lies with
specifically averaging the pre test score for ONLY those who completed
training. I use a checkbox called Completed1 to show if someone has completed
or not.

Currently, I have one query that calculates the general avg for pre and post
tests for each training and I have another query that only calculates the pre
test of those who have a "Yes" value in the Completed1 checkbox (I set that
up in the Criteria portion of the query). When I created a report combining
the data from the two queries, I ended up with some strange duplications. 3
trainings were reported perfectly. Beckley1 appeared 4 times with identical
data except for the Completers Pre Test Avg, which showed 2 records with 22.
45 and 2 with 22.81 (the correct avg). Beckley2 also appeared 4 times with
identical data and showed the same Completers Pre Test Avg results (22.45 is
the correct avg for this training). When the queries are separate, there is
no odd duplication, but when they are merged into the report, only the
Beckley trainings duplicate unexpectedly. I've gone through the original
table but I can find nothing out of the ordinary in the Beckley records. Help?

 

[Jeff Boyce]

I can't be sure from your description, but it sounds like your table has
"repeating fields" (e.g., Training1, Training2, ...). If so, you have ... a
spreadsheet!

You won't get the best use of Access' relationally-oriented features and
functions if you feed it 'sheet data.

Since everything in Access starts with the data, please post a more complete
description of your table structure. For example, if you table was about
"persons", you might have:

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[gillespiea23]

It's a large table, but I think the relevant fields are:
First Name
Last Name
Completed1 (checkbox)
PreTestScore1
PostTestScore1
Round
Location (lookup field)
Trainer (lookup field)
Co Trainer (lookup field)
Start Date
End Date

The data in the table right now is from Round 6, with 4 different training
locations: Beckley, Princeton, Sutton, Lewisburg. There are 2 trainings in
Beckley, both have the same start date, but they have different end dates.

I can't be sure from your description, but it sounds like your table has
"repeating fields" (e.g., Training1, Training2, ...). If so, you have ... a
spreadsheet!

You won't get the best use of Access' relationally-oriented features and
functions if you feed it 'sheet data.

Since everything in Access starts with the data, please post a more complete
description of your table structure. For example, if you table was about
"persons", you might have:

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with participants, whether they completed training, what the
pre test score is, and what the post test score is for five different

[quoted text clipped - 30 lines]

table but I can find nothing out of the ordinary in the Beckley records.
Help?

 

[Jeff Boyce]

Please post the SQL statement your query uses. Since you describe your data
as have two "Beckley" sessions, is there any other mechanism for identifying
the sessions than the Start & End dates?

And I still have questions about the manner in which your data is
structured. Having "five different" trainings and having fieldnames ending
in "1" (Completed1, PreTestScore1, PostTestScore1) make me strongly suspect
that your data is NOT well-normalized.

You will not get the best use of Access' relationally-oriented
features/functions if you feed it 'sheet data...

Regards

Jeff Boyce
Microsoft Office/Access MVP

It's a large table, but I think the relevant fields are:
First Name
Last Name
Completed1 (checkbox)
PreTestScore1
PostTestScore1
Round
Location (lookup field)
Trainer (lookup field)
Co Trainer (lookup field)
Start Date
End Date

The data in the table right now is from Round 6, with 4 different training
locations: Beckley, Princeton, Sutton, Lewisburg. There are 2 trainings in
Beckley, both have the same start date, but they have different end dates.

I can't be sure from your description, but it sounds like your table has
"repeating fields" (e.g., Training1, Training2, ...). If so, you have ...
a
spreadsheet!

You won't get the best use of Access' relationally-oriented features and
functions if you feed it 'sheet data.

Since everything in Access starts with the data, please post a more
complete
description of your table structure. For example, if you table was about
"persons", you might have:

tblPerson
PersonID
FirstName
MiddleName
LastName
DateOfBirth

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with participants, whether they completed training, what
the
pre test score is, and what the post test score is for five different

[quoted text clipped - 30 lines]

table but I can find nothing out of the ordinary in the Beckley records.
Help?