Recordnumber 3

[szotto]

How can I show in query (or form) the record number ?
( the autoincrement filed can have some holes depend if I was deleted some records)


SELECT id,name FROM mytable ORDER BY id DESC ;

# id Name
---------------
1 5 Beta
2 3 Alpha
3 1 Teta

SELECT id,name FROM mytable ORDER by name;

# id Name
---------------
1 3 Alpha
2 5 Beta
3 1 Teta

I want to get this "#" column.

I hope u understand what I need.

Thank's in advance

Otto

 

[KenReay]

Hi

If you are trying to do this in a report, then look at running sum property, it gives ability to product a 'line number' type effect Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[szotto]

the problem is that it's not enough in the report, I have to calculate with this column.

evrey visit cost 1$ ,but the second_visit / month it's free of pay, and not every day it will come, etc.

select * Visitors_table join visit_records;

# * visitor entry
--------------------------
1 1 Otto 1$
2 2 otto 0$
3 3 otto 1$
4 1 Michael 1$
5 2 Michael 0$
6 1 Dan 1$


for me it's enough the "#" column but if i can get the "*" column it would be great.


It's so hard to do this in Access in query ?

no function like :" SELECT IDENTITY(int,1,1), name;" or " SELECT _rowid, name;" or something else ???

 

[KenReay]

Hi

The #column - Other than the Running Sum option on a report the only other way I can think of to achieve the line number would be to use the AbsolutePosition property of the recordsetclone, but that is a guess, have not done it before.

To get the * column, I would add an extra column to the visits table (VisitNumberThisMonth) and write a ruotine to increment the number when adding a record, the routine could be made to increment with VisitorId and Month of visit.

Hope that helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MazeWorX]

http://ourworld.compuserve.com/homepages/attac-cg/

this article sounds like its exactly what your looking for

hth Remember amateurs built the ark - professionals built the Titanic

 

[cmmrfrds]

You can create a row number with an unequal self join. It requires a group by so will need to have both id and name, but that should be okay.

SELECT count(*) as rownum,
A.id, A.name FROM mytable as A, mytable as B
where B.id <= A.id
Group by A.id, A.name
ORDER BY A.id DESC