Increment Expression in Select Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a function that would allow me to add a incremented # to a select
query? For example, a query returns...

Name
------
Fred
Joe
Barney

.... and I want to add a field Number (caption #) that looks like...

# Name
-- ------
1 Fred
2 Joe
3 Barney

.... and if I edit the query to sort by name, it should look like...

# Name
-- ------
1 Barney
2 Fred
3 Joe

Thanks,

PetroChris
 
PetroChris said:
if I edit the query to sort by name, it should look like...

# Name
-- ------
1 Barney
2 Fred
3 Joe
Click to expand...

CREATE TABLE Test
(Name VARCHAR(35) NOT NULL);

INSERT INTO Test VALUES ('Fred');

INSERT INTO Test VALUES ('Joe');

INSERT INTO Test VALUES ('Barney');

SELECT (
SELECT COUNT(*)
FROM Test AS T2
WHERE T2.Name <= T1.Name
) AS row_ID,
T1.Name FROM Test AS T1
ORDER BY T1.Name;
 
Roger: Thanks for the reply. Sorry to be a pest, but my company won't let
me download *.zip files from websites (securities).

If it is not too much trouble, can you copy/paste the expression syntax from
a calculated query field that generates an incremental number, into a text
reply.

Thanks,

PetroChris
 
Would prefer not to create a new table if possible.

I'm looking for a function that I can use in a calculated query field, that
generates an incremented number.
 
Lynn: Thanks for the reply. In my original note, I intended to make my
request independent of sort order.

In the code provided as an example "T2.Name <= T1.Name" enforces a sort.

I simply want the 1st record returned by a query labeled "1", the second
labled "2", etc., with or without a sort field selected.

Is there a "Built in Function" that accomplishes this?

Thanks,

PetroChris
 
One question, where are you using the results of the query. If you are using
the results in a report, there is a simple method of getting the number.

Add a control to the detail section (Named for example "txtMyRowNum")
Set its control source to =1
Set Running Sum to Overall

You're done.
 
PetroChris said:
I intended to make my
request independent of sort order.
Click to expand...

Assuming this is a business-related project, how do your specs define
'independent'? Date/time order? Pseudo-random order? A different order
each time?
 
Back
Top