Calculation/Date rang problems

[BrokenBiker]

Hey Y'all,

Well...where should I start? This db report has a few calculations in it, so let me explain them real quick. The db tracks work assessments that are categorized as Pass, Fail, or Non-Rated.

The first is an overall percentage rate of all assessments. Non-Rated assessments are not included in this query. It works fine. In the report it's the Group Total.

The second is a "maintenance" percentage, which is calculated from just two types of assessments. This also works and in included in the report in a sub-report. This is the Maintenance Rate.

Our guidance has recently changed, and I'm trying to incorporate these changes. The first one is an addition to the first/overall percentage. The Group Total is

Rate: IIf([Var1]+[Var2]=0,"N/A",[Var1]/([Var1]+[Var2]))

where Var1 is Pass and Var2 is Fail. This works fine for an overall percentage. However, now we need to deduct 0.5 points for each type inspection "TDV", "UCR", "GO", or "DSV." These are some of the assessment types listed as Non-Rated, however, there are other assessment types listed as Non-Rated that are not part of this criteria.

I included this change in a separate query "qryGroupDeductions." In this query, the 0.5 point per specified-assessment type is calculated by grouping and counting these inspections, then multiplying them by 0.5

DeductPoints: [DeductionCount]*0.5

This query counts and calculates properly. In the report there's a bit of conditional formatting that's been working so far. I had problems w/ it, but I think it's working properly now.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

If Me.Text64 <= 1 And Me.Text64 >= 0.95 Then
    Me.Text66 = "Outstanding"
End If

If Me.Text64 <= 0.9499 And Me.Text64 >= 0.9 Then
    Me.Text66 = "Excellent"
End If

If Me.Text64 <= 0.8999 And Me.Text64 >= 0.8 Then
    Me.Text66 = "Satisfactory"
End If

If Me.Text64 <= 0.7999 And Me.Text64 >= 0.7 Then
    Me.Text66 = "Marginal"
End If

If Me.Text64 <= 0.6999 And Me.Text64 >= 0 Then
    Me.Text66 = "Unsatisfactory"
End If

If Me.Text66 = "Outstanding" Then
    Me.Text66.FontBold = True
    Me.Text66.ForeColor = 8388608
End If

If Me.Text66 = "Excellent" Then
    Me.Text66.FontBold = True
    Me.Text66.ForeColor = 6723891
End If

If Me.Text66 = "Satisfactory" Then
    Me.Text66.FontBold = True
    Me.Text66.ForeColor = 0
End If

If Me.Text66 = "Marginal" Then
    Me.Text66.FontBold = True
    Me.Text66.ForeColor = 65535
End If

If Me.Text66 = "Unsatisfactory" Then
    Me.Text66.FontBold = True
    Me.Text66.ForeColor = vbRed
End If

End Sub

The problem I'm running into is when you run the queries/reports for a period longer than a month. The instructions on the new guidance don't take into account quarterly or annual reports. When this report is run for periods longer than a month, the 0.5 point deduction makes a disproportional difference because you are deducting a sum from an average. The larger the time-span in the query, the larger the deduction is.

That, my friends, is the problem. I've made one query (qryQtrGrpDeduction) that "solves" this problem. It's based on the "qryGroupDeductions", and uses a basic calculation to create an average for the months.

QtrAvg: [DeductionCount]/3

and

DeductPoints: [QtrAvg]*0.5

This is very basic, but effective. But using would create a much duplication of queries and reports. I would need one set of reports/sub-report/queries for the month, quarter, and the year. This sample db doesn't show it, but our working database does this same calculation for four different organizational levels....So in using this method, we would need approx 15 queries and reports.

Not cool.

There's gotta be a way to effectively accomplish the requirements w/o creating a multitude of reduncies.

BTY, I'm using Access2003.

-----------------------------------
Key words: calculate, percent, count, report conditional formatting

 

[BrokenBiker]

Nothing?

Me either.

I do like how the charts in Access will take all of your data and group by month (which would solve my original problem), but I just end up running into other problems.

The query that all of this runs from has

Between [Forms]![Request Percent Report Form]![Start Date] And [Forms]![Request Percent Report Form]![End Date]

as criteria. It works well for the report, but when you try to run a chart from this query I get the message that it isn't a valid field name or is not support by this database engine.

So....I thought I'd export that query to a table. Evidently, that's not gonna happen either. I can't seem to get the percentages into a table w/o turning them into either a '1' or a '0' I know that it can be done, I just can't figure it out.

The query exports to Excel w/o any issues whatsoever. But even if I import the Excel file into a table, the percentages (shown as 0.0000 in both the query datasheet view and in Excel) still import as 1s or 0s. I've ensure that the fields in Excel are numbers, and I've tried so many of the different number options in the table that my mind's about gone.

I know that you shouldn't store calculated fields in a table, but I can't find a way around my current problem.

 

[BrokenBiker]

Oh, yeah.

You can keep the numbers as they were, i.e. 0.666667, but only if the field is formatted as text. But you can't use a text field in a chart to compile averages...so you need numbers...but numbers only get imported as 1s and 0s....Maybe if you make a query from the table...Hmm...Nope...The field is still read as text.

You see how trying to avert one problem leads to another, which leads to another....? It's hard to believe that this would be impossible to do or that I'm the first one to encounter this problem. Maybe I'm just stubborn...