Numbered query list

[ojf]

Does anyone out there know a way to get a numbered output from a query?

Example - when sorted by country:

1 England London
2 France Paris
3 Norway Oslo

Example - when sorted by capital:

1 England London
2 Norway Oslo
3 France Paris

Thanks!

 

[LaurenC]

You can use a "RunningSum" on a report, but I think you're out of luck on a query.

 

[UncleJack]

What do you mean by a numbered output? Do you mean the number that you're apparently using in the table?

First of all set up your table properly:

ID Country Capital
1 England London
2 France Paris

Base a query on the table and select the fields that you want to display and choose how they should appear in the query, ascending, descending. Perhaps you will have to use two or more queries with different sorting routines and then select which query to use.

Did this answer your question, or did I miss the mark?

Uncle Jack

 

[tlbroadbent]

Questions about enumerating rows have been asked dozens of times in this forum. It seems that each time there is a slightly different need.

The following queries will work unless city and country names are duplicated. In that case, you'll get duplicated numbers. If your table is large, the queries may be slow.

Example: number if sorted by city
SELECT
(Select count(*)
From tableA
Where City & &quot;|&quot; & Country <= t.City & &quot;|&quot; & t.Country) AS nmbr,
t.City, t.Country
FROM tableA AS t
ORDER BY t.City, t.Country;

Example: number if sorted by country
Select
(Select count(*)
From tableA
Where Country & &quot;|&quot; & City <= t.Country & &quot;|&quot; & t.City) as Nmbr,
t.City, t.Country
From tableA as t
Order By t.Country, t.City; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[PaulF]

create a new field in your query which you will call a Public Function from and pass the name of one of the fields in your table

This is variation of an example from an issue of Inside Microsoft Access

create a new module with this in it

Global RecordNum
Function DoesIncrement(AnyValue)
RecordNum = RecordNum + 1
DoesIncrement = RecordNum
End Function


Add this field to your query (I used Country as the Field Name, but it can be any Field in your Table)

RecNum: Doesincrement([Country])

Also if you intend to run this several times, besure to Reset the Public Variable RecordNum back to zero before running it (just create another function to do it)

PaulF