Tilføjelse af linienumre

First you need to add an extra column to your table with type "Autonumber" this will give each record a unique number.

Then you can use something like this


SELECT tblTst.Gruppe, tblTst.ID
FROM tblTst
GROUP BY tblTst.Gruppe, tblTst.ID
ORDER BY tblTst.Gruppe, tblTst.ID;

1. Du kan f.eks tilføje et autonummerfelt
2. Gruppering kan du lave i en forespørgsel i menuen Vis > Grupper efter
3. Du kan sortere efter linienr, men det fordrer jo at numrerne er fortløbende indenfor grupperne.

Straks anderledes er det i en rapport. Her kan du tage en ubundet tekstboks fra værktøjslinien og give den postkilden =1 og løbende sum over grupper. Grupperingen lasver du ved at finde ikonet for gruppering og sortering på menulinien.

Jeg glemte at nævne at tabellen allerede er et resultat af en gruppering af <tal>
dvs. en forespørgelse

Det var lidt dårligt beskrevet mit spørgsmål. Her kommer det mere præcist.

Jeg har en tabel <id>, <navn>, <tal>, <gruppe1>, <gruppe2>

Det primære er at:
  1) Inddele i undergrupper af <gruppe1>
  2) Tildele hver undergruppe "linienumre" (hver undergruppe starter med 1).

Og derefter:
  3) Gruppere efter <gruppe2>
  4) Sortere efter linienumre

pkt.1,3 og 4 har jeg en idé om hvordan jeg gør, men hvordan tildeler jeg en forespørgelse et linienummer.

pkt.1 og 2. kan jeg få udført ved brug af rapport, men hvis jeg skal arbejde videre med det skal det hele vel ske i en forespørgelse?

Vi kan også bare begrænse spørgsmålet til:
Hvordan tilføjer man et linienr. til en sorteret forespørgelse?

sorry wired, been rather busy :o)

Adding a line number to a query isnt that easy, unless you have some way placing the records in order, for example using the autonumber field. If you start grouping on other fields then this will very likely put your records (autonumber) out of order.

But as long as you can count the number of records which fall before each record then you can.

Here is a simple example

SELECT Emp1.ID, Emp1.LastName, Emp1.HireDate, (Select Count (*) from Employees Where [ID] < [Emp1].[ID])+1 AS LineNumber
FROM Employees AS Emp1
ORDER BY Emp1.ID;

I have just tried your example, but it doesn't seems to work.
I just get a lot of 1's

well that is very likely becaus ethere are NO records before (0+1)= 1

You MUST have some way of plaing the records in oreder, and then in the sub select you have to count the number (+1) to get the line number

ain't impossible to before (0+1)=1. The records are all in ordre.
I have tried:

SELECT Emp1.ID, Emp1.LastName, Emp1.HireDate, (Select Count (*) from Employees Where [ID]+1 < [Emp1].[ID])+2 AS LineNumber
FROM Employees AS Emp1
ORDER BY Emp1.ID;

But that just give a lot of 2's

What I ment was:
isn't impossible to be before (0+1)=1 ?

Make a table with the name tblTst an dwith two fields, ID and navn. The ID can be an autonumber but it isnt necessary in this example. If you do use autonumberthen you will need to delete the record with ID = 3 so that you have the follolwing data in the table.


ID    navn
1    aaa
2    bbb
4    ddd


Now make a query like this
SELECT T.ID, T.navn, (Select count(*) From tblTst WHERE tblTst.ID < T.ID)+1 AS LineNumber
FROM tblTst AS T;

which WILL give yu the following result.

ID    navn    LineNumber
1    aaa    1
2    bbb    2
4    ddd    3

as you can see the LineNumber is not the same as the ID

does this help at all wired?

I would have to control the autonumbered ID, and that I'm not able to either.

But I did try you example and it works, thou the ID numbers must be 1,2,4,5,6,7 etc. and that is a bit tricky as well I think.

It doesn't seems to solve the problem. Instead I think I will try to ADO program me out of it, thou it's a bit tricky as well, but will - over time - make the job.
For now I can't see it done but a query, thou it would be the most easy way.

the example I gave was to show you how you can give each record a line number. It is NOT necessary to use an ID column, but it IS neceesary to have some way of placing the records in order. For example, if it was a date time field, you would alter the

WHERE tblTst.ID < T.ID

to

WHERE tblTst.YourDateTimeField < T.YourDateTimeField

and then
Order add

OrderBy T.YourDateTimeField

and you can include as many fields as youlike in the WHERE and ORDER BY

I would still have to manuelly plan the first records in some kind of field, as I see it. And that would just me the problem, but I really appreciated the help. It did lead me in a direction to solve the problem.
Is it ok if we split the points? mug 20, you 80 and me 20

me = move

You decide who gets the points, just hope you can use our help

mvh og god weekend
terry

Ok... Og tak for hjælpen.