Calculation help!

[Guest]

Okay...here is what I have to work with.
I have a Drill information talble and in it I have scores, and a particular
type of test.

I have created a query that counted the amount for that particular type of
test and another for test scores >=95. How do I make them return a running
ratio.
Example. I have 15 total of this one type of test and only 2 were >= 95.
However, I want it to be a running total becuase over the next year I have to
be able to calculate it continuously. Here are my queries...

Returns count of Drill Type 3
SELECT DISTINCTROW Sum([Drill-Type=3-2006-Count].[Count Of
Drill-Type=3-2006]) AS [Sum Of Count Of Drill-Type=3-2006]
FROM [Drill-Type=3-2006-Count];

Returns Score >=95
SELECT [Drill information].DrillType, [Drill information].Date, [Drill
information].Score
FROM [Drill information]
GROUP BY [Drill information].DrillType, [Drill information].Date, [Drill
information].Score
HAVING ((([Drill information].DrillType)<>"3") AND (([Drill
information].Date) Between #1/1/2006# And #12/31/2006#) AND (([Drill
information].Score)>=95))
WITH OWNERACCESS OPTION;

Is there any way that I can do all of this in one query or report?

Thank you for your help in advance.

 

[Guest]

I can't quite figure out what you want. You talk about a 'running ratio' in
one line but a 'running total' in another. I'm sure it can be done, but
you'll need to give more precise details of just what you want in the result
set.

Ken Sheridan
Stafford, England

 

[Guest]

My apologies...I want a running ratio. In other words I do not want to take
the total count and divided it into 2. I want the calculation to be the
total count for that year divided by all the tests that were >=95 not just
the 2 at this date in time. Does that help? All I need is a calculation.
not sure how to do it though. That is where I need your help. I want it in
a report format, can i do the calculation in the report form or does it have
to be in the query.

I can't quite figure out what you want. You talk about a 'running ratio' in
one line but a 'running total' in another. I'm sure it can be done, but
you'll need to give more precise details of just what you want in the result
set.

Ken Sheridan
Stafford, England

Okay...here is what I have to work with.
I have a Drill information talble and in it I have scores, and a particular
type of test.

I have created a query that counted the amount for that particular type of
test and another for test scores >=95. How do I make them return a running
ratio.
Example. I have 15 total of this one type of test and only 2 were >= 95.
However, I want it to be a running total becuase over the next year I have to
be able to calculate it continuously. Here are my queries...

Returns count of Drill Type 3
SELECT DISTINCTROW Sum([Drill-Type=3-2006-Count].[Count Of
Drill-Type=3-2006]) AS [Sum Of Count Of Drill-Type=3-2006]
FROM [Drill-Type=3-2006-Count];

Returns Score >=95
SELECT [Drill information].DrillType, [Drill information].Date, [Drill
information].Score
FROM [Drill information]
GROUP BY [Drill information].DrillType, [Drill information].Date, [Drill
information].Score
HAVING ((([Drill information].DrillType)<>"3") AND (([Drill
information].Date) Between #1/1/2006# And #12/31/2006#) AND (([Drill
information].Score)>=95))
WITH OWNERACCESS OPTION;

Is there any way that I can do all of this in one query or report?

Thank you for your help in advance.

 

[Guest]

I'm still not absolutely sure what the required divisor and dividend for the
expression are, but you can do it in the query, using either the DCount and
DSum functions or with subqueries. However, its probably more efficient to
do it in the report itself using the two functions. The DCount function
counts rows in a table on the basis of a criteria passed into it as an
argument, and the DSum function sums the value of a field in the table on the
basis of a criterion. Say you want to count all the rows in the table for
2006 the current DrillType where the Score was >=95 the expression used as a
text box's ControlSource would be:

=DCount("*", "[Drill Information]", "[DrillType] = " & [DrillType] & " And
Year([Date]) = 2006")

To sum the scores for the same criterion:

=DSum("[Score]", "[Drill Information]", "[DrillType] = " & [DrillType] & "
And Year([Date]) = 2006")

By varying the criteria you should be able to get the values for whatever
the required divisor and dividend are for your final expression. The
ControlSource for a text box to give the result would then simply be two
expressions of the above type with the / division operator between them.

BTW I'd caution against the use of Date as a field name. It could be
confused with the built in Date function in some circumstances and give
unexpected results. Wrapping it in square brackets as I've done above should
avoid this, but its always best to avoid keywords as object names.

Ken Sheridan
Stafford, England

My apologies...I want a running ratio. In other words I do not want to take
the total count and divided it into 2. I want the calculation to be the
total count for that year divided by all the tests that were >=95 not just
the 2 at this date in time. Does that help? All I need is a calculation.
not sure how to do it though. That is where I need your help. I want it in
a report format, can i do the calculation in the report form or does it have
to be in the query.

I can't quite figure out what you want. You talk about a 'running ratio' in
one line but a 'running total' in another. I'm sure it can be done, but
you'll need to give more precise details of just what you want in the result
set.

Ken Sheridan
Stafford, England

Okay...here is what I have to work with.
I have a Drill information talble and in it I have scores, and a particular
type of test.

I have created a query that counted the amount for that particular type of
test and another for test scores >=95. How do I make them return a running
ratio.
Example. I have 15 total of this one type of test and only 2 were >= 95.
However, I want it to be a running total becuase over the next year I have to
be able to calculate it continuously. Here are my queries...

Returns count of Drill Type 3
SELECT DISTINCTROW Sum([Drill-Type=3-2006-Count].[Count Of
Drill-Type=3-2006]) AS [Sum Of Count Of Drill-Type=3-2006]
FROM [Drill-Type=3-2006-Count];

Returns Score >=95
SELECT [Drill information].DrillType, [Drill information].Date, [Drill
information].Score
FROM [Drill information]
GROUP BY [Drill information].DrillType, [Drill information].Date, [Drill
information].Score
HAVING ((([Drill information].DrillType)<>"3") AND (([Drill
information].Date) Between #1/1/2006# And #12/31/2006#) AND (([Drill
information].Score)>=95))
WITH OWNERACCESS OPTION;

Is there any way that I can do all of this in one query or report?

Thank you for your help in advance.