Calculating a category based on the totals

[Guest]

I hope I am posting to the correct group for this, I have assumed that I need
a query to solve this problem.

I have a database of buildings, and elements within each building.
I have given each element a condition rating and a repair cost.
My boss wants an overall condition rating which is calculated from:

For each building; If the total cost for all condition rated "C" items is =>
£5,000 the overall rating will be C, or else the rating will be B.

The overall condition should automatically input this figure and since it
will be the same for all elements in a building it should appear in a parent
table.
I don't know how to get access to consider the items for each building,
especially since the ratings are alphabetical.

I will also need to qualify this with a statement which consider A ratings
and D ratings, but that is my next hurdle.

 

[Guest]

D,

The first thing you need to do is create a query that identifies the total
number of items and cost, by category, within each building. Since you
didn't give us your data structure, I'll assume that tbl_Buildings contains a
Building_ID and a Building_Name, and that your tbl_Elements contains the
Building_ID, Condition, and Repair_Cost, or something similiar to this. The
first query I would create would look like:

SELECT B.Building_ID, B.Building_Name, E.Condition, Count(E.Building_ID) as
CountOfCondition, Sum(E.Repair_Cost) as Tot_Repair_Cost
FROM tbl_Building B inner join tbl_Elements E
ON B.Building_ID = E.Building_ID
GROUP BY B.Building_ID, B.Building_Name, E.Condition

From there, you have to figure out a way to assess the overall building
condition based on the number and cost of each condition.

HTH
Dale

 

[Guest]

Your table structure is accurate.

I have a table for site location and date of survey. I wonder if the best
way to achieve this woud be to add a field here for overall condition
category and create some sort of forulae in the input Form.