I need to use the following query to fill a subform in Access and I
want to to limit the records returned to the latest 'version_no'. It
was all working fine until I added the 'Policy.unique_record_number'
column to the query, after which it began to return all records. How
can I prevent this from happening?

SELECT DISTINCT Policy.unique_record_number, Policy.policy_no,
Max(Policy.version_no) AS MaxOfversion_no, Policy.insured_id,
Organisation.organisation_name, Organisation.organisation_alias FROM
Policy INNER JOIN Organisation ON Policy.insured_id =
Organisation.organisation_id GROUP BY Policy.unique_record_number,
Policy.policy_no, Policy.insured_id, Organisation.organisation_name,
Organisation.organisation_alias;

FireyColin wrote:
By removing the 'Policy.unique_record_number' from the query of course! It's
kind of like the "Doctor, it hurts when I lift my arm" joke ...
;-) ;-)

DISTINCT guarantees the rows will be different, not specific columns in the
rows. To do this, it compares ALL the columns in each row to the columns in
the rest of the rows.

What you really want is the unique_record_number from the row containing the
MaxOfversion_no. To do this will require joining the Policy table to the
results from the query you had before adding 'Policy.unique_record_number'.
The cleanest way to do this is to save that original query, calling it
something like MaxVersionPerInsured or something like that and creating a
new query for your subform like this
SELECT p.unique_record_number, p.policy_no,
MaxOfversion_no, p.insured_id, organisation_name,
organisation_alias FROM Policy AS p JOIN
MaxVersionPerInsured AS q on p.policy_no=q.policy_no
AND p.version_no=MaxOfversion_no AND
p.insured_id=q.insured_id

Many Thanks Bob

It's so easy when you know how isn't it?

Colin

FireyColin wrote:
Posing the question correctly is the first (and usually hardest) part of the
process.