updateable query with max()

[ElRudi]

Hey all,

I'm having a problem with a query. I'll try to condense it to the basics.

There's a table A with following fields
ID (autonumber, unique)
Selected (boolean)
Notes (text)

and a table B with following fields
ID (integer, not-unique)
score (integer)

The tables need to be joined on their ID fields; all records from A need to be shown, from table B the maximum score needs to be sought for each given ID.

On its face this is not a very difficult query; right now I've solved it with

SELECT A.ID, A.Selected, A.Notes, C.MaxOfScore


FROM A LEFT JOIN

(SELECT ID, Max(Score) AS MaxOfScore
FROM B
GROUP BY ID) AS C

ON A.ID = C.ID

However, this query is not updatable, and therefore not usable for the application I have in mind.

Is there a way to create a similar query in which (at least) the Selected field *is* updateable?

Thanks!

[RuralGuy]

See if this link helps at all: http://allenbrowne.com/ser-61.html

[ElRudi]

See if this link helps at all: http://allenbrowne.com/ser-61.html

Hey yes thanks for replying, I actually did already see that, and it made it clear why my alternative solution (using distinct) wasn't working either. I couldn't figure out a way that *does*work, though...

[RuralGuy]

I'm sorry but my brain cells cannot come up with an alternative for you.

[ElRudi]

Thanks anyways!

Maybe someone else...?

[RuralGuy]

You might try smaller queries that are not ambiguous and then use them as the source of your current quest. Often time when you break down the problem into smaller chunks you eliminate any ambiguity and Access can handle it.

[ElRudi]

I'm not sure if I understand correctly - you mean saving the inner query separately and referencing it like so:

qryHelp:
SELECT ID, Max(Score) AS MaxOfScore
FROM B
GROUP BY ID;

and

SELECT A.ID, A.Selected, A.Notes, qryHelp.MaxOfScore
FROM A LEFT JOIN qryHelp
ON A.ID = qryHelp.ID;

?

That I had already tried too, to no avail. (I think Access just replaces the 'qryHelp' instances with the real thing.)

But keep those ideas roling!

[RuralGuy]

I don't see the need for a Join. Make sure your 1st query has all of the fields you need and then create another query based on the 1st quesy with differenf selection criteria.

[ElRudi]

I'm not following, sorry.

Could you give me an example? I need to be able to update only the field values of table A.

[RuralGuy]

I'm sorry but I have to go back to my Post #4.

[orange]

However, this query is not updatable, and therefore not usable for the application I have in mind.

Could you describe the application you have in mind?

[ElRudi]

Thanks orange and RuralGuy for your help.

I want to use the query as the recordsource for a continuous form, on which the user can see all of the records and change its property (e.g. the notes in a textbox, or the 'selected' property as a checkbox). I use the value of MaxOfScore to colour the background of the continuous form via conditional formatting, so that entries that need special attention are immediately identified by the user.

Of course the value of MaxOfScore does not need to be changeable, but the values of the fields that are neither grouped nor calculated (i.e., those from table A) do need to be.

Thanks!