Autoincrement i forespørgsel

Hvad vil du opnå med det?

you cant create an autoincrement field in a select query but you could maybe do it with a calculated field. This will require that you have for example an autonumber column (unique).

With the following data as an example in table1

ID    FELT1
1    1
2    1
3    1
4    2
5    3
6    3
7    4
8    4
9    4
10    4

SELECT T.ID, T.FELT1, (SELECT count(*) FROM Table1 T2 WHERE T2.FELT1 = T.FELT1 AND T2.ID < T.ID)+1 AS FELT2
FROM Table1 AS T;

Jeg har lavet en funktion jeg kan bruge i min forespørgsel:

Global IncVar As Long
Function IncrementValues(i) As Long
IncVar = IncVar + 1
IncrementValues = IncVar
End Function

Men som sagt opstår der problemer fordi jeg ønsker den starter forfra ved skift i FELT1

Hvis du tilføjer FELT2 i din tabel, så kan du bruge denne funktion:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim a, b, c
Set db = CurrentDb()
Set rst = db.OpenRecordset("DinTabel")
With rst
.MoveFirst
a = 1
b = !felt1
Do
c = !felt1
If b = c Then
.Edit
!felt2 = a
.Update
a = a + 1
Else
.Edit
!felt2 = 1
.Update
b = !felt1
a = 2
End If
.MoveNext
Loop Until .EOF
.Close
End With

Mugs (og andre) hvis i skal have hele historien, så kommer den her.
Jeg har en tabel der indeholder følgende information:

ID, AKTIVITET, DATO, KLOKKESLET
1, START, 01/10/2007, 01:57:13
1, SLUT, 01/10/2007, 06:53:19
1, START, 01/10/2007, 09:00:15
1, SLUT, 01/10/2007, 11:16:22

Det jeg ønsker at finde ud af er for hvert ID, hvor lang tid der er gået mellem START og SLUT. Der kan være mange START og SLUT for hvert ID, men der skal altid være et SLUT for hvert START.

Jeg forsøgte mig med 2 forespørgsler, 1 der grupperer START og 1 der grupperer SLUT, for derefter at linke dem sammen på ID feltet og et efterfølgende autonumerings felt.
Det går pt. ikke så godt hehe, så nu får i hele ihistorien i håb om at i kender en bedre løsning.

Der er intet link mellem START og SLUT linierne, og det er ikke muligt at ændre tabellerne til at omfatte det, det er altsammen data jeg henter fra en backend server.

I'm finding it difficult to see what the original question has to do with your comment 04/02-2008 10:40:05

Mugs spurgte hvad formålet med den oprindelige forespørgsel var, og jeg skrev hele problemstillingen.

Jeg har forsøgt at løse problemstillingen ved hjælp af autoincrement, men hvis der er andre løsninger tager jeg dem gerne i betragtning, da autoincrement måske er lidt ustabilt set i bakspejlet.

"Mugs spurgte hvad formålet med den oprindelige forespørgsel var, og jeg skrev hele problemstillingen."

But if he hadnt we would still be trying to give an answer to the question you gave not the real problem.

The answer I gave 04/02-2008 10:13:47 is a possible solution to the original question but its miles out to the comment you gave 04/02-2008 10:40:05

The answer I gave 04/02-2008 10:13:47 can be modified  to give you a calculated 'autoincrermnet' as in the example but what you are realy after is the time difference between each start/slut for a specific ID isnt it?

Yep terry, that's correct

is it possible for you to have an autonumber (ID) field to the table and maybe have the data and time in one field?

This is using the fields which you have and the result is in seconds

SELECT T1.ID, T1.AKTIVITET, T1.DATO, T1.KLOKKESLET, (SELECT Max(DATO) FROM tabel1 T2 WHERE T2.ID = T1.ID AND ((T2.DATO < T1.DATO AND T2.KlOKKESLET >= T1.KLOKKESLET) OR (T2.DATO = T1.DATO AND T2.KlOKKESLET < T1.KLOKKESLET))) AS LASTDATO, (SELECT Max(KLOKKESLET) FROM tabel1 T2 WHERE T2.ID = T1.ID AND ((T2.DATO < T1.DATO AND T2.KlOKKESLET >= T1.KLOKKESLET) OR (T2.DATO = T1.DATO AND T2.KlOKKESLET < T1.KLOKKESLET))) AS LASTKLOKKESLET, CDate([LASTDATO] & " " & [LASTKLOKKESLET]) AS LASTTID, DateDiff("s",CDate([LASTDATO] & " " & [LASTKLOKKESLET]),CDate([DATO] & " " & [KLOKKESLET])) AS DiffSec
FROM Tabel1 AS T1;

Hi Terry

If you instead of using DATO and KLOKKESLET use MODIFY_DATETIME (which is time in seconds since 1/1/1990 00:00:00), then th equery should get much simpler right? :)

But also Terry, your query is assuming there is only two occurrences os START and SLUT, but there can be many, I've IDs that have over 20 START and SLUT rows :-)

05/02-2008 13:27:44
Access doesnt understand MODIFY_DATETIME but if you had the data and time in one field then that would make things more simpler.

05/02-2008 13:31:26
There can be as many STARTs and STOPs as you like for each ID.
The query calculates the differnece in seconds between the previous AKTIVITET which means that you can also see the difference between a STOP and the next START.

The first record for each ID will give an error because there is no previous record.

Have you tried it with your data? I used the example you gave and was assuming that it was what you were using.

ID, AKTIVITET, DATO, KLOKKESLET
1, START, 01/10/2007, 01:57:13
1, SLUT, 01/10/2007, 06:53:19
1, START, 01/10/2007, 09:00:15
1, SLUT, 01/10/2007, 11:16:22

I get that Access doesn't understand MODIFY_DATETIME as a valid DATE field, but it's a perfectly valid numeric field which should be usable in your query aswell.

I tried your query, but modified it a bit to use MODIFY_DATETIME instead, but the result was a bit weird :-)

Also, I just want the query to group each instance of START and SLUT, so the end result looks a bit like this:

ID, MODIFY_DATETIME_START, MODIFY_DATETIME_SLUT
1, 43654644, 43852611
1, 43854944, 43884644

Above is just random numbers, but I hope you get the idea :-)

Well if you put a number in the the fields instead of a dat then its just a case of subtracting them form each other.

When you say "group each instance..." do you mean sort?

is it possible for you to send an example of teh data you are using so that I can see exactly what table/field names you are using and so that I have some data which I can test.

ekspertenATsanthell.dk

AT = @

Thanks for your help Terry :-)

selv tak