Showing highest value in query

[knighty03]

I have created a report which gets its data from a query. I have inputted around 100 records of which some of them have the same reference number, but the revision is different. In this report I would like it to show only one copy of the records with the same reference number, but it has to be the highest revision. Any help would be appreciated.

 

[PaulBricker]

Depends on how much info you have in the query but if you have just two field, RefNo and Revision then you would make the query a Totals query. On the menu bar go to View...Totals. Then on the Total line, Group By RefNo and set the Revision to Last, or Max depending on how the info is stored. Start there and post back with any questions.

Paul

 

[knighty03]

I have roughly around 10 fields in this query, showing criteria for a selected month on loading. The total group just comes back with an error saying I can't have "*" in the query, but I never inserted it in. Any other ideas?

 

[MakeItSo]

You could add a grouping to your query.
Group by Reference number and select the
Max. function in the grouping of the revision number.
This should do it.

Cheers,
MakeItSo

 

[PaulBricker]

First, you should create the query that I explain in my fist post. This will give you an idea as to whether you are pulling up the records you want. If you have problems, post the SQL from this query so we can see it.
Then you can create a second query. Use your Table and the first Query. Join the RefNo and then add all the fields from the table and query that you need to complete your info.

Paul

 

[p27br]

when running an aggregation query such as a groupby query, you must drag and drop each field separately in the design view window, it will not work if you select the * symbol.
so instead of SELECT * FROM you need SELECT Field1,Field2...FROM