Limiting records on a form using code

[Richey]

Hi
I need to create a form showing the following fields

Rating ID
Business Name
isn number
rating
category

The Rating ID, isn number, rating & category fields are taken from a table called 'food_risk_ratings'
The Business name field is taken from a table called 'h_main'

For example, within table 'food_risk_ratings' there may be 3 records with the same isn number but with a different Rating ID. I need to show only that record with the highest Rating ID

The link between the 2 tables is the isn number.
In table h_main there are hundereds of businesses that all have a discrete isn number

Help much appreciated
Donnie

 

[JGillespie]

Base your form on a query that pulls it's data from the 'food_risk_ratings' and 'h_main' tables.

In query design select all the fields you require for your form. Bring up the Totals row for the query (look in help if stuck) and set the RatingID field total to Max.

Should work for you!
Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[Richey]

Yeah I've tried that before Jamie but no go.
My Rating_ID is an autonumber which I think is causing problems but I think I can use the INT function.
If you imagine my records looked like this

RatingID ISN Business Rating
1 10 Wimpys A
2 10 Wimpys B
3 10 Wimpys A
4 11 McDonalds C
5 12 Burger King A
6 12 Burger King B

I only want to show 3 records, namely rows 3, 4 and 6. i.e. where the ISN number is the same only show the record with the highest RatingID.

Cheers

 

[JGillespie]

Try this then.

If you are including the Rating column in your query then it will be being 'grouped by' and so will affect your result set. The answer is to not include it in your main query and design a second query to retreive the Rating field.

The SQL for the two queries is below:

Query1

SELECT Max(Table1.RatingID) AS MaxOfRatingID, Table1.ISN, Table1.Business
FROM Table1
GROUP BY Table1.ISN, Table1.Business;

Query2

SELECT Query1.MaxOfRatingID, Query1.ISN, Query1.Business, Table1.Rating
FROM Query1 INNER JOIN Table1 ON Query1.MaxOfRatingID = Table1.RatingID;

This should work for you. Jamie Gillespie
j-gillespie@s-cheshire.ac.uk