Update query select first record for each client

[Juddy58]

Hi, I have a table tblClientDisciplineRelationshipOrganisation which contains the following fields
fldClientID fldRecordOrder fldOrganisationID fldMainOrg
1 10 24 false
1 20 29 false
1 30 21 false
2 10 3 false

Basically this table is saying that one client can be associated with a number of different organisations.
What im trying to do is set up an update query that will set fldMainOrg to true for the first record (by fldrecordOrder)for each client so in the above example:
fldClientID fldRecordOrder fldOrganisationID fldMainOrg
1 10 24 true
1 20 29 false
1 30 21 false
2 10 3 true

I have managed to create a query that produces the results I want, but it isn't updateable as the query is to complex (as i have done it by using group by query and then basing another query on it). I have started to try to use a nested query but cant get what im after as my sql isn't as good as it should be!
Any help would be greatly appreciated.
Thanks
Justin

 

[PHV]

Perhaps this (SQL code) ?
UPDATE tblClientDisciplineRelationshipOrganisation
SET fldMainOrg = True
WHERE fldRecordOrder = DMin("fldRecordOrder", "tblClientDisciplineRelationshipOrganisation", "fldClientID=" & [fldClientID])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886