Aggregate Queries and date ranges

[ArffMarine]

Let me apologize in advance for the long code but I always see posts that ask for it so I decided to include it from the jump.

Here is my vision:

We have 2 tests that are administered to students at our fire academy, the midblock exam and the end-of-block exam. Each exam has 2 versions to prevent test compromise. These versions are 3341A and 3342B for the midblcok and 3381A and 3382B for the end of block.

At the end of each quarter we analyze the test results to verify the validity of the tests.

There are 13 key sections in the midblock and 9 on the end of block.

We want to use access to store the number of questions missed in each section for each class on each version and have access spit an overall total missed, total students tested, and total missed in each section for the quarter.

I have a form that accepts a date input from the user and runs a query to return the results of all the tests administered during the period specified. I have an aggregate query that runs this code:

SELECT TestAnalysis.MidBlockVersion, Count(TestAnalysis.MidBlockVersion) AS CountOfMidBlockVersion, Sum(TestAnalysis.Mid2) AS SumOfMidNumberofQuestionsMissedInSection2, Sum(TestAnalysis.Mid3) AS SumOfMidNumberofQuestionsMissedInSection3, Sum(TestAnalysis.Mid13) AS SumOfMidNumberofQuestionsMissedInSection4, Sum(TestAnalysis.Mid4) AS SumOfMidNumberofQuestionsMissedInSection5, Sum(TestAnalysis.Mid5) AS SumOfMidNumberofQuestionsMissedInSection6, Sum(TestAnalysis.Mid6) AS SumOfMidNumberofQuestionsMissedInSection7, Sum(TestAnalysis.Mid7) AS SumOfMidNumberofQuestionsMissedInSection8, Sum(TestAnalysis.Mid8) AS SumOfMidNumberofQuestionsMissedInSection9, Sum(TestAnalysis.Mid9) AS SumOfMidNumberofQuestionsMissedInSection10, Sum(TestAnalysis.Mid10) AS SumOfMidNumberofQuestionsMissedInSection11, Sum(TestAnalysis.Mid11) AS SumOfMidNumberofQuestionsMissedInSection12, Sum(TestAnalysis.Mid12) AS SumOfMidNumberofQuestionsMissedInSection13, TestAnalysis.EndBlockVersion
FROM TestAnalysis
GROUP BY TestAnalysis.MidBlockVersion, TestAnalysis.EndBlockVersion, TestAnalysis.MidBlockDate
HAVING ((([TestAnalysis].[DateMidBlocked]) Between [forms]![quarterlytestanalysis]![start] And [forms]![quarterlytestanalysis]![end]))
ORDER BY TestAnalysis.MidBlockVersion, TestAnalysis.EndBlockVersion;

The table structure is
Class# primary key
Midblockversion

MidBlockDate
StudentsTestedatMidBlock
MidNumberofQuestionsMissedInSection1
MidNumberofQuestionsMissedInSection2
and so on til
MidNumberofQuestionsMissedInSection13

The problem is in the date field. Having the date is needed to run the query to retrieve the tests needed for analysis but it prevents the sums and counts from running properly.

Any ideas?

Thanks

 

[BobJacksonNCI]

There are some fine folks reading your post that I am CONFIDENT can solve your problem ... once it is clearly stated.

All that can be ascertained from your post is that a date range is needed for data selection but that same date range is causing some unstated problem.

Please state specifically what IS happening AND what you WANT to happen.

Bob

 

[ArffMarine]

I think I got too involved with not making one of those posts that makes no sense due to lack of infomation.



What is happeining is the total missed will total, the total students tested, and the total of each version administered will sum without the date field.

Once you add the date field, it groups by the date and will not properly total.

I wan to know how to make the query look up the tests by the date administered and then disregard the date field to allow the rest of the field to run.

 

[BobJacksonNCI]

Actually, posting the SQL was a good thing.

And I sort of understand ... and suspect your goal is beyond my skill level. But NOT beyond the talented folks here!

To encourage more participation in this thread, please post the table names and fields involved, and a handful of data examples from each table.

That may be enough for someone to jump in with an answer.



HTH,
Bob