update group by query

[patweb]

strSQL = "SELECT [T-02-CONTACT].PWDON, [T-02-CONTACT].PWDFRAME, [T-02-CONTACT].FRMNAME FROM [T-02-CONTACT]  " _
& "GROUP BY [T-02-CONTACT].PWDON, [T-02-CONTACT].PWDFRAME, [T-02-CONTACT].FRMNAME " _
& "HAVING ((([T-02-CONTACT].PWDON)=Yes) and ([T-02-CONTACT].PWDFRAME)) " _
& "ORDER BY [T-02-CONTACT].PWDFRAME DESC;"

With rst
.Edit
![FRMNAME] = FRAMENAME
.Update
End With[/code]

I need to update a column in a table. I try to do this via a group by SQL, otherwise he will update to much lines. Is there a way still using an group by to select the data and afterwards to update the table ? Now he is refusing this, because you can't update via a group by SQL.

pat

 

[PHV]

An aggregate query is not updatable, dot.

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

 

[GhostWolf]

Assuming that you're wanting to change the "frame name" if PWDON=Yes and PWDFRAME=[some specified value] - I'd suggest not even executing the "select" query.

Just do an "update" query, with a "where" clause to limit what gets updated, (i.e. "where PWDON=Yes and PWDFRAME<>...").

 

[patweb]

I found a solution. If someone in the (near) future should discover the same problem.

First you can't update an group by (aggregate) query = OK
Second : I solved this by creating a temporaty table based on the values of the group by.
Third : With an Update SQL I joined the two tables and I got the result.

This is theory, I know with some practical data it would be more easier to see what I mean. But first the id and secondly the practice.

regards, Pat.