[AccessD] Record number in query

Cheers Matt!

That's not quite what I'm after sadly. I'm using Access to write SQL queries
to be used in PHP. I'm sure I saw somewhere/sometime a bit of SQL using
COUNT that can do this.

I could use the SQL query to populate a temporary table and then use the row
numbers, but I'm trying to avoid this if poss.

I found this on Tek-Tips:
----------------------------------
-Assume the primary key is a composite of Col1 and Col2.
Select
 RowNum=(Select Count(*) From TableName
         Where Col1<=t1.Col1 And Col2 <=t1.Col2)
 Col1, Col2, Col3, Col4
>From TableName t1
Order By Col1, Col2 
----------------------------------

But could not get it to work.

Thanks for trying to help!

Regards
Chris Foote

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: 25 May 2006 14:23
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Record number in query

Chris,

I sent you a sample db offline that shows what you need.  Its a simple 2
line function...but you have to reset it each time you use it.  Also, this
works best with MAKE,APPEND,UPDATE type queries...if you display a query
using this function...the numbers will change as you scroll.

Let me know if you don't get the offline sample.

Thanks,

Mark A. Matte


>From: "Chris Foote (Spike)" <spikee at oatlandspark.org.uk>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: [AccessD] Record number in query
>Date: Thu, 25 May 2006 08:29:57 +0100
>
>Good day!
>
>Dumb question here.
>
>I've got a SELECT query and wish to add a calculated field with row number.
>The number needs to start at 1, for the first record, and increment to 
>n for record n. I'm sure I've seen a post here recently about this but 
>I cannot find it in the archives.
>
>TIA!
>Chris Foote