Solved Use query with subquery, to run an update query? (1 Viewer)

[FoolzRailer]

I'm trying to update a tables in access database column based on a query that I've shown below. I keep getting an error saying "Operation must use an updatable query." I have one standard select query before the one shown below, which I believe is the culprit. The subquery that counts and gives a sequence based on each LedningID and Length, is based on what I can read the issue. Is my assumption correct? And how can I fix this, can I do a separate query and remove the subquery in order for the updatequery to work?

SELECT KoordinaterStik.OpstrømsHovedKnude, KoordinaterStik.OpstrømsHX, KoordinaterStik.OpstrømsHY, KoordinaterStik.NedstrømsHovedKnude, KoordinaterStik.NedstrømsHX, KoordinaterStik.NedstrømsHY, KoordinaterStik.LedningID, KoordinaterStik.StikX, KoordinaterStik.StikY, KoordinaterStik.KnudeID, KoordinaterStik.LængdeFraOpstrøms, KoordinaterStik.Knudenavn,
(SELECT COUNT(*)
FROM KoordinaterStik AS t2
     WHERE t2.LængdeFraOpstrøms <= KoordinaterStik.LængdeFraOpstrøms
       AND t2.LedningID = KoordinaterStik.LedningID) AS Sequence
FROM KoordinaterStik
ORDER BY KoordinaterStik.LængdeFraOpstrøms;

Example of sequenced data output:

 

[isladogs]

See this article by Allen Browne http://allenbrowne.com/ser-61.html.

 

[FoolzRailer]

See this article by Allen Browne http://allenbrowne.com/ser-61.html.

Appreciate the response, I tried a different approach but still get the same error.

Basically I have two very standard select queries, that I put together with a simple Join One-To-One. After that I have a new query (which was previously with a subquery, now with the VBA below). The only function of that query is to give an ascending number for each LedningID, based on the LængdeFraOpstrøms. As you can see in the highlighted rows on the screenshot for LedningID 737. When the Sequence is given, I need to update the KnudeNavn with and update query:

UPDATE StikKnudeNavn INNER JOIN Knude ON StikKnudeNavn.KnudeID = Knude.ID SET Knude.Knudenavn = [StikKnudeNavn].[NytStikKnudenavn];

My new VBA that I call before the update query, so I can calculate the sequence, this replaced my previous SubQuery.

Public Function GetSequence(LedningID As Long, LængdeFraOpstrøms As Double) As Long
    Dim rs As DAO.Recordset
    Dim sql As String
    sql = "SELECT COUNT(*) AS Sequence FROM KoordinaterStik WHERE LedningID = " & LedningID & " AND LængdeFraOpstrøms <= " & Replace(LængdeFraOpstrøms, ",", ".")
    Set rs = CurrentDb.OpenRecordset(sql)
    GetSequence = rs("Sequence")
    rs.Close
End Function

Where am I going wrong with this?

 

[FoolzRailer]

I fixed it, but doing a work around with a temporary table and updating using that.

 

[ebs17]

update query

Where is there one?

SELECT
   K.OpstrømsHovedKnude,
   K.OpstrømsHX,
   K.OpstrømsHY,
   K.NedstrømsHovedKnude,
   K.NedstrømsHX,
   K.NedstrømsHY,
   K.LedningID,
   K.StikX,
   K.StikY,
   K.KnudeID,
   K.LængdeFraOpstrøms,
   K.Knudenavn,
   DCount("*", "KoordinaterStik", "LængdeFraOpstrøms < " & K.LængdeFraOpstrøms & " AND
         LedningID = " & K.LedningID) + 1 AS Sequence
FROM
   KoordinaterStik
ORDER BY
   K.LængdeFraOpstrøms

Replacing the subquery with DCount makes the query updateable, which also makes the query significantly faster.