add autoNumber to query after sorting

[Guest]

I have a query that pulls info from a table and sorts it based on a particular field. Is there a way to assign a number to each record in the query (starting with 1) AFTER it has been sorted.

Simply adding the autonumber to the table will not help me b/c after it is sorted based upon the other field, those numbers will be out of order

For reference, this number after sorting is needed for a DLookup loop that I am creating.

 

[Ted Allen]

It is possible to number the output rows of a query. To
do this, you create a new field and set the field's
source as a subquery that counts all lesser records based
on the sorted field(s).

But, in order for this to work properly the sorted fields
must result in unique combinations. If some records will
have the same values in the sorted fields, they will both
display the same row number. In those cases you would
usually add other fields to serve as tiebreakers in the
subquery to prevent duplicate row numbers.

If you aren't familiar with subqueries, post back with
your query sql and some info on what fields could be used
to uniquely identify records and I can help with the
subquery text.

Hope that Helps

-Ted Allen

-----Original Message-----
I have a query that pulls info from a table and sorts it

based on a particular field. Is there a way to assign a
number to each record in the query (starting with 1)
AFTER it has been sorted.

Simply adding the autonumber to the table will not help

me b/c after it is sorted based upon the other field,
those numbers will be out of order.

For reference, this number after sorting is needed for a

DLookup loop that I am creating.

 

[Guest]

unfortunately i am not familiar with subqueries, but i'm trying to figure them out now. The SQL from the query is as follows
SELECT [Election Table].from, [Election Table].to, [Election Table].name, [Election Table].addres
FROM [Election Table
WHERE ((([Election Table].from) Is Not Null)
ORDER BY [Election Table].from

from and to fields may be shared between records, but a combination of name and address will never be shared

 

[Ted Allen]

Try pasting the following sql into a blank query and see
if it works for you. I have used the nz() function in
the subquery just in case the name and/or address is
sometimes null.

SELECT [Election Table].from, [Election Table].to,
[Election Table].name, [Election Table].address, 1+
(SELECT Count([Election Table].from) FROM [Election
Table] as VT
WHERE (VT.from < [Election Table].from) OR (VT.from =
[Election Table].from AND nz(VT.name,"") < nz([Election
Table].name,"")) OR (VT.from = [Election Table].from AND
nz(VT.name,"") = nz([Election Table].name,"") AND nz
(VT.address,"") < nz([Election Table].address,""))) AS
RowNum
FROM [Election Table]
WHERE [Election Table].from Is Not Null
ORDER BY [Election Table].from, [Election Table].name,
[Election Table].address;

After pasting the sql switch to design view and take a
look at the row number calculation field. You will see
the subquery syntax there. When used as a calculated
field like this, a subquery is simply an sql query
statement that only returns one value (usually count,
min, max, etc) meeting specific criteria. Also, since
the same table is used as the source for the subquery, it
is aliased as VT (Virtual Table) to differentiate between
the main query field values and the subquery fields.

Often, domain aggregate functions can be used in place of
subqueries. In this case, you could probably use the
DCount() function with the same criteria (although the
criteria would have to be built using concatenated
strings in the DCount function).

FYI, subqueries can be used in another capacity when used
in the criteria lines, you can specify that a field is IN
or NOT IN the resulting data set from a subquery.

Hope that helps. Post back if you have any problems.

-Ted Allen

-----Original Message-----
unfortunately i am not familiar with subqueries, but i'm

trying to figure them out now. The SQL from the query is
as follows:

SELECT [Election Table].from, [Election Table].to,

[Election Table].name, [Election Table].address

FROM [Election Table]
WHERE ((([Election Table].from) Is Not Null))
ORDER BY [Election Table].from;

from and to fields may be shared between records, but a

combination of name and address will never be shared.