Opdatering af tællerværdier

Maybe something like this

!

SELECT tblKabin.CenterID, tblKabin.KabinNo, tblKabin.UgeNr, [tblKabin]![Total]-(SELECT TOP 1 Total from TblKabin K WHERE K![CenterID] = [tblKabin]![CenterID] AND K![KabinNo] = [tblKabin]![KabinNo] AND K![UgeNr] < [tblKabin]![UgeNr] ORDER BY K![CenterID] DESC, K![KabinNo] DESC, K![UgeNr] DESC) AS tællerværdier
FROM tblKabin;

Det fungerer jo perfekt. Har dog modificeret terry's en smule:

SELECT tblKabine.centerid, tblKabine.kabinenr AS Udtryk1, tblKabine.ugenr, tblKabine.total, [tblKabine]![Total]-(SELECT TOP 1 Total from TblKabine K WHERE K![CenterID] = [tblKabine]![CenterID] AND K![KabineNr] = [tblKabine]![KabineNr] AND K![UgeNr] < [tblKabine]![UgeNr] ORDER BY K![CenterID] DESC, K![KabineNr] DESC, K![UgeNr] DESC) AS tællerværdier
FROM tblKabine
ORDER BY tblKabine.kabinenr, tblKabine.ugenr;

Har prøvet begge forslag, men de tager simpelthe for lang tid. Og jeg har 8 felter i tabellen jeg skal finde differencen for.
Måske skal jeg ud i en midlertidig tabel og så noget VBA til at oprette records.

(native tabeller ligger på en MySQL server)

Well as far as I can see mugs example is the same as mine but with a sort, so there isnt going to be much perfomance difference between them.

"Og jeg har 8 felter i tabellen jeg skal finde differencen for"
So the differemce isnt just total?

"native tabeller ligger på en MySQL server"
Do you mean the tables are on a MySQL srever? If so then (know very little about MySQL) I would suggest that making a view (query) (if thats possible) on the MySQL Server would go much faster and then make a link to the view from Access.


"Måske skal jeg ud i en midlertidig tabel og så noget VBA til at oprette records."
I'll be very surprised if this will be faster. You would (I think) need to loop through a recordset and for each record find the previous week (for center+cabin) and then insert data into a temp table, but you could give it a try.

Indexs on fields may help.

You could also try making a passthrough query, thats if the tables are on a server (MySQL), but the syntax (SQL) must be MySQL SQL not Access.

In my example there is only one 'Total' field but in real life I have 11 fields that I have to calculate the difference from last week to current week
So that is going to be quite a task I know :-)

Terry I have mailed you an image of the structure of the table.

Mugs> hvis du vil se strukturen, så skal jeg bruge en email.

Tetty > Korrekt, at jeg blot har indført en sortering.
Hugo > Ikke nødvendigt, dette ovetstiger mit kendskab til SQL.

Jeg tror jeg fik den kringlet nu. Det er heldigvis sådan at det er mig selv der har programmeret Delphi programmet der danner data på MySQL (det er et program der konverteret ca. 550 paradox tabeller til MySQL - 3 filer fra hvert af vore solcentre + 8 fælles = 20.000.000 records alt i alt) og derfor var det ikke noget problem at lave et par ekstra felter der indeholder previous week og år (lidt overhead i tabellerne men hastigheden er bedre) og så lave 1 query der bygger på 2 subqueries og resultatet er noget jeg kan bruge i en rapport.
Så tak til Terry for 'showing me the way' once again :-)

Slev tak, og god weekend Hugo og mugs

selv!

Tak i lige måde til Jer begge.