Query help needed

[MattCollins]

I have two tables, a Property table and a System table. They are linked so that there are many Systems linked to one Property.

Each System has a risk score and these vary within each property. My Property table has a Property risk score field and in here i would like the maximum System risk score to go.

I'm just wondering how i would do this using a query? All the system risk scores have been entered already.

Any information would be great!!

Matt

 

[Len Boorman]

Now it is probable that System risk score will change... therefore potentially max risk for a property could change....therefore do not store the value but derive it every time you want to see the max score

Use a query to select max risk value

len

 

[MattCollins]

Thank you very much Len...much appreciated!

 

[MattCollins]

Sorry, but how would i go about doing this?

 

[MrsGorilla]

Create a query with the System table, select the risk value field. On the View menu, select Totals, and that adds extra criteria called Total to your query. From the drop-down box, select Max. That will calculate the max number in that field. You will have to add criteria to select for a certain property, etc.

Alternatively, if you were wanting to display this on a form or a report instead, you could check into the DMax() function.