Queiry to return highest value or number in a field

[wheather girl]

Hi

I have a table that has records that are constently superseded, they have
the same ID number, as to recognise that it is the same document but we need
to supersede these documents when we have to add extra data/information in
the document, so hence we give it a new rev (revision) number start at o then
work our way up to 1 when it has superseded No. 0 and go to No. 2 when it has
superseded No. 1 and so on and so on.

What I need to do is a query based on the table, but to only return the
highest number of that record, so it only returns the latest version of that
document. ex there might be a rev 2 of a particulat document, which means
there are 3 versions of that same document, but i only need the query to
return the highest rev of that document, not all the revs. and so on and so
on for each record/document in the table.

Firstly I know I will have to group the query by ID No. then somehow use a
formula for it to return the highest rev no. what formula will recognise the
highest value out of that same group.

any ideas, thanks

 

[vanderghast]

SELECT id, MAX(revisionNo)
FROM tablename
GROUP BY id


in SQL view, or, in graphical view, bring the table, click on the summation
button to get a total query (a total line appear in the grid), drag the id
field from the upper half into the grid, keep the proposed GROUP BY, bring
the revision number field in the grid, change the GROUP BY to MAX.


Vanderghast, Access MVP

 

[wheather girl]

Thankyou...that did the trick....now just another question.
using the same query below, I have an attachment field, that I had added to
the query, the only problem is,is that I only want it to display the
attachment for the highest rev, which we have now accomplished, but instead
it is bringing up all the records to display each attachment. How do i get
it to display only the attachments for the highest rev. I thought that it
would do this automatically once i had accomplished the query of highest rev,
but it wont and i cant group it as it wont accept it, it only accepts it as a
expression. Can you possibly help on this one?

 

[vanderghast]

It can be done, indeed. See four methods at
http://www.mvps.org/access/queries/qry0020.htm

One of the easier method is to make another query (often the case using
Jet). Standard query (not a total query) bring the original table and the
last total query you would have saved. Join the two fields from your last
query to id and revisionNo of your table. Bring all the required fields from
you table in the grid. That's all.


Vanderghast, Access MVP

 

[wheather girl]

Hi Vanderghast

thanks for your input I have looked at the link, but I must say I am not an
expert in SQL, but am slowly gettin the hang of it. But I did try linking
the query that I made to the table as you said and then i was able to add
some more fields with out any thing going wrong, but there must be something
different with the attachment field as this still is not working. Any other
ideas?

thanks

 

[vanderghast]

You mean with the MEMO fields, of other OLE fields? Indeed,they don't work
well with complex query, unfortunately.


Vanderghast, Access MVP

 

[wheather girl]

No not MEMO fields or OLE fields, I am talking about an Attachement field,
or a hyperlink field, which doesnt work anyhow. ok well thanks for your
help, at least now I know that queries dont like 'em.
thanks