Find forrige værdi

Jeg ved godt, at det egentlig er en forespørgsel du ønsker, men SQL er nu engang ikke velegnet til at håndtere mere end en post ad gangen, så når jeg har haft et tilsvarende problem, har jeg løst det ved at danne en midlertidig tabel med VBA.
I dit tilfælde kan det f.eks. se sådan ud:

Sub DanTblForrigeVærdier()
  Dim DB As Database
  Dim rsIn As Recordset
  Dim rsOut As Recordset
  Dim OldID As Long, OldVærdi As Long
 
  Set DB = CurrentDb
  Set rsIn = DB.OpenRecordset(\"tblVærdier\")
  Set rsOut = DB.OpenRecordset(\"tblForrigeVærdier\")
 
  OldID = -9999
  While Not rsIn.EOF
    rsOut.AddNew
    rsOut.Fields(\"EntitetID\").Value = rsIn.Fields(\"EntitetID\").Value
    rsOut.Fields(\"Værdi\").Value = rsIn.Fields(\"Værdi\").Value
    If rsIn.Fields(\"EntitetID\").Value <> OldID Then
      rsOut.Fields(\"ForrigeVærdi\").Value = Null
    Else
      rsOut.Fields(\"ForrigeVærdi\").Value = OldVærdi
    End If
    OldID = rsIn.Fields(\"EntitetID\").Value
    OldVærdi = rsIn.Fields(\"Værdi\").Value
    rsOut.Update
    rsIn.MoveNext
  Wend
  rsIn.Close
  rsOut.Close
End Sub

mvh/Jens-Peter

Jeg glemte at sige, at jeg i ovenstående har forudsat at den midlertidige tabel \"tblForrigeVærdier\" er oprettet i forvejen, og har felterne \"EntitetID\", \"Værdi\" og \"ForrigeVærdi\". Hvorvidt man vil oprette tabellen i forvejen eller lade det indgå som en del af VBA-proceduren er selvfølgelig en smagssag.

mvh/Jens-Peter

Hej Jens-Peter,

Din kode virker selvfølgelig, men som jeg skrev, så ser jeg helst at man undgår temporære tabeller (da disse i høj grad er med til at databasen vokser uhensigtsmæssigt - og min erfaring er at kunderne/brugerne sjældent husker/har mulighed for at komprimere.)

Men din kode er selvfølgelig hurtigere end at bruge DLookup og andet godt fra havet - især hvis der er store datamængder involveret.

Jeg lader spørgsmålet stå åbent nogle dage endnu i håb om en ren SQL-løsning....

mvh
Thomas

Hej Thomas

Her er mit bud: Opret en function i et modul

Option Compare Database
Option Explicit
Private varold As Variant

Public Function Forrige(var As Variant) As Variant

    Forrige = varold
    varold = var

End Function

Herefter kaldes funktionen i SQL således:

SELECT Tabel1.entitetid, Tabel1.værdi, Tabel1.dato, forrige([dato]) AS sidste
FROM Tabel1;

Der er et problem med første record, her returneres den værdi der gemt i \'varold\' fra sidste functionskald.

Jeg synes selv denne metode er ret hurtig, idet VBA koden er relativ simpel - Jo mere man bygger på jo langsommere bliver den.

Niels

Man skal forresten være varsom med den slags beregninger direkte i en tabel. rækkefølgen af poster i en tabel er pr. definition tilfældig.


Niels

Niels, din kode opfylder ikke helt kravene, men jeg indrømmer, at den er ret smart, og ved små ændringer får man det ønskede resultat:

Private varold As Variant
Private OldId As Variant

Public Function Forrige(ID As Variant, var As Variant) As Variant
    If ID = OldId Then
      Forrige = varold
    Else
      Forrige = Null
    End If
    varold = var
    OldId = ID
End Function


SELECT EntitetID, Værdi, Dato, Forrige([EntitetID],[Værdi]) AS Sidste
FROM Tabel1;

Du har selvfølgelig ret i, at den bliver lidt langsommere, men normalt vil du også slippe for problemet med første record (medmindre EntitetID for første post er samme som for sidste record ved sidste funtionskald).

mvh/Jens-Peter

Jens Peter

Din kontrol af ID kan lige så godt ligge i SQL\'en. Jeg bruger den bl.a. i et lille program til energimåling: el, vand, varme osv. her må forbruget selvfølgelig ikke være negativ:
iif([forbrug]>=0; [beregn]; 0).

men det er jo op til ham der udvikler databasen, hvordan han vil tjekke sine data.

Niels

Niels

Du er nødt til at have ID med i VBA funktionen, for hvis du ser på den oprindelige opgave, så skal værdien af \"Forrige værdi\" sættes forskelligt afhængig af om ID for den aktuelle record er anderledes end for den foregående record, og det kan man ikke direkte i SQL.

mvh/Jens-Peter

OK du får ret.

Jeg havde ikke nærlæst Thomas\'s tabel. Jeg troede det hang sammen med datoen og det var forskellen til sidste dato han var ude efter.

\'læs og forstå\' hed en skolebog for laaaang tid siden - måske skulle jeg kigge i den igen ;o)

Niels

Hej Thomas, jeg ved du er mand for at decode en SQL-sætning (hvor upædagogisk den end serveres), so herre it is:

SELECT T1.EntitetID, T1.Værdi, T1.Dato, Null AS Forrige FROM Tabel1 AS T1 WHERE Dato=(SELECT Min(Dato) FROM Tabel1 WHERE EntitetID=T1.EntitetID) UNION SELECT T1.EntitetID, T1.Værdi, T1.Dato, T2.Værdi AS Forrige FROM Tabel1 AS T1 LEFT JOIN Tabel1 AS T2 ON T1.EntitetID = T2.EntitetID WHERE (((T2.Dato)=(SELECT Max(Dato) FROM Tabel1 WHERE EntitetID=T1.EntitetID AND Dato<T1.Dato)));

/ProAccess

Hej alle 3,

Tak for de rigtig gode svar. Det er faktisk lidt svært at give point, men her er begrundelsen for pointgivningen:

Niels-> Dit svar er rimelig smart og simpelt. Jeg har tidligere leget lidt med den metode (men havde egentlig glemt det). However, så synes jeg ikke den er særlig stabil, da den er alt for afhængig af sorteringsrækkefølge (dvs, hvis brugeren efterfølgende vælger at sortere forespørgslen på en ny måde, så får han også et nyt resultat). Det duer ikke! Ligeledes lykkedes det mig heller ikke at få det helt rigtige resultat - der var visse Entiteter, som ikke havde en blank i starten - og det skal de jo alle.
Jeg har alligevel valgt at give dig 50 point, da det er rart at se nogen tænker simpelt frem for at gøre det alt for avanceret.

Jens-Peter-> Du trodsede mine krav og ønsker og gav mig en løsning som indebar brug af temp-tabel. I Starten var jeg skeptisk, men da jeg oplevede at dit løsningsforslag reducerede kørselstiden til omkring 1/20 af DLookup-metoden (og det inklusiv generering af temp-tabel), så var jeg solgt (og ligeledes var min kunde :o)
Så kan jeg godt acceptere at databasen vokser lidt hurtigere! Du får derfor 350 point. Tak for det!

Poul-Erik-> Jeg må indrømme at jeg var lidt imponeret, da jeg så dit løsningsforslag - jeg havde selv bakset lidt med det og kunne ikke få det til at spille helt. Min umiddelbare tanke var at skrive: \"...and the Oscar goes to...PROACCESS!!!\", men besluttede mig lige for at afprøve det på \'live\'-data. Og desværre, så viste det sig at din query ikke var væsentlig hurtigere end DLookup-metoden (måske 20-30 % hurtigere).
Da spørgsmålet gik på at finde den optimale løsning, kvalificerer din query sig desværre ikke helt. Jeg synes dog alligevel at du fortjener point for din imponerende SQL-kunnen, så jeg opretter 200p i nyt spørgsmål til dig. Er det ok?

Endnu engang tak for svarene :o)

/Thomas :~)

Jeg vil med glæde modtage points ;-)

Og jeg giver dig ret i din begrundelse af point-deling... Jeg ville bare give dig det i en enkelt SQL.

Men som du selv pointerer, så er SUB-SELECTS ikke verdens hurtigste at have med at gøre.

/ProAccess

Hej Thomas

Takker for pts. Jeg mener dog at min metode kan bruges i mange tilfælde, hvor man har brug for en værdi i forrige post. Det er jo ikke meningen at brugeren skal sortere forespørgslen i alle situationer.
Jeg tog spørgsmålet som et generelt problem, og havde desværre ikke gennemtænkt den konkrete case.

Glad for at kunne \'opdatere\' din samling af guldkorn.

Niels