Record count? forespørgsel

just answered a similar question
http://www.eksperten.dk/spm/605747

I'm sorry, I don't really get it, explain please :-)

I need the one without filter cause it's pretty straightforward. I just need a query where there's a field that acts like a recordcount so that I can see the record number next to each record.

Could you explain this one:

SELECT tbl1.*, (SELECT count(*) from tbl1 T1 WHERE T1.ID < tbl1.ID)+1 AS Nr
FROM tbl1;

thanks! :-)

Du kan også bruge VBA,
Du skal bruge en skjult formular, hvor autonummer er ordnet stigende (DanNummer).
I din formular åbner du den med:

Private Sub Form_Open(Cancel As Integer)     
        DoCmd.OpenForm "DanNumre", acNormal, , , , acHidden
End Sub

VedÅning i formularen DanNumre:

Public Sidstepost As Long
Function Numre()
       
        Dim SidsteNr As Long
        Dim PostNr As Long
                   
        DoCmd.GoToRecord , , acLast
        SidsteNr = [ID]
        DoCmd.GoToRecord , , acFirst
       
        PostNr = 0
        Do
            PostNr = PostNr + 1
            [POST] = PostNr
            If [ID] = SidsteNr Then Exit Do
            DoCmd.GoToRecord , , acNext
        Loop
        Numre = PostNr
End Function

Private Sub Form_Open(Cancel As Integer)

          Sidstepost = Numre()
          DoCmd.Close
End Sub

Den globale variabel SidstePost kan bruges overalt i basen når der tilføjes eller slettes poster. Hvis du aldrig eller sjældent sletter eller tilføjer poster behøver du ikke at opdatete hver gang basen åbnes, men kan nøjes med at køre opdateringen efter behov.

SELECT tbl1.*, (SELECT count(*) from tbl1 T1 WHERE T1.ID < tbl1.ID)+1 AS Nr
FROM tbl1;



SELECT tbl1.* FROM tbl1


This part returns ALL records from tbl1


the SUB SELECT
(SELECT count(*) from tbl1 T1 WHERE T1.ID < tbl1.ID)
returns the row number starting at 0,but then the +1 resets this to 1



For example:
Lets say we have only one field in tbl1 (ID)

ID = 8     The sub select returns the number of records where the ID field is < 8 which is 0+1 = 1
ID = 111        The sub select returns the number of records where the ID field is < 111 which is 1+1 = 2
ID = 200    The sub select returns the number of records where the ID field is < 200 which is 2+1 = 3
ID = 500    The sub select returns the number of records where the ID field is < 500 which is 3+1 = 3


So the SQL will return
8, 1
111, 2
200, 3
500, 4

But as I mentioned in the previos question you will NOT be able to alter data in the form.

shit...it wont work then, it's a webbased memberlist and it's updated from an ASP page...I must try to do it from that end then...will just leave this question open for a while longer, then I'll give you guys points :-)

I was under the impression that it was in Access you were using the SQL. If its on a WEB page  using ASP then there should be NO PROBLEM

status?

Tror det er opklaret, har oprettet det andet spørgsmål her http://www.eksperten.dk/spm/605885 hvis du har kommentarer modtager jeg gerne :-)