Displaying row no/Record Counting

  • Thread starter Thread starter Kerri Davies
  • Start date Start date
K

Kerri Davies

I am trying to include a row number in a query. Is this
possible?

I am compiling top 10's of product for 4 individual years
and in order to link each individual query I need to
calcaluate a position (position 1 in 2002 will link to
position 1 in year 2001 etc)

Is there a way to do this without having to create tables
with an autonumber field?

Any help would be appreciated
K
 
There is not a nice way to do this, but if you don't mind a read-only result
(e.g. if it's going to a report), you could use a subquery to count the
number of records ahead of this one.

You type the subquery into a fresh column in the Field row in query design
view. Something like this:
Rank: (SELECT (Count([ID]) + 1) AS Rank FROM MyTable AS Dupe WHERE (Dupe.ID
< MyTable.ID))

Where there is a tie, you get typical ranking results, e.g.:
1
2
2
4
5

If you need the records to be updatable, you can use DCount() to create the
calculated field, but it will be very slow.
 
This seems to also be a solution to my situtation; does it
appear so to you? When I attempt to translate your example
to my query I'm getting a syntax error, so evidently I
don't guite get it yet. Perhaps you could clarify your
example for the SQL-challenged?

I am creating a waiting list with the position on the list
based on the date and time the record was added. I've done
that using a running-sum counter textbox in a report. In
order to generate an alphabetical listing of these people,
I have to analyze the report in Excel and sort the
spreadsheet based on the client's name.

While this gets the job done, it's a bit of a kludge. I'd
prefer to set a calculated field within the query so the
report can be sorted any way I like (position, name, zip
code, etc) without having to leave Access (and without
having to become a VB programmer).

I found something of a solution in the KB
<http://support.microsoft.com/default.aspx?scid=kb;en-us;
199679>, but hope a simpler solution exists! I'm certain
that if it does, someone here will be able to enlighten me!

Thanks,

-Bob
-----Original Message-----
There is not a nice way to do this, but if you don't mind a read-only result
(e.g. if it's going to a report), you could use a subquery to count the
number of records ahead of this one.

You type the subquery into a fresh column in the Field row in query design
view. Something like this:
Rank: (SELECT (Count([ID]) + 1) AS Rank FROM MyTable AS Dupe WHERE (Dupe.ID
< MyTable.ID))

Where there is a tie, you get typical ranking results, e. g.:
1
2
2
4
5

If you need the records to be updatable, you can use DCount() to create the
calculated field, but it will be very slow.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to include a row number in a query. Is this
possible?

I am compiling top 10's of product for 4 individual years
and in order to link each individual query I need to
calcaluate a position (position 1 in 2002 will link to
position 1 in year 2001 etc)

Is there a way to do this without having to create tables
with an autonumber field?

Any help would be appreciated
K
Click to expand...


.
Click to expand...
 
Okay. Let's assume your table is named "Table1", and it has fields:
- Table1ID AutoNumber primary key
- ClientID Number refers to ClientID in the Client table
- EnteredOn Date/Time when the record was added.

You create a query that contains Table1.
You can include the Client table to get names and addresses from there if
you wish.

On each row of your query, you need to count the number of records in Table1
that are ahead of this one. To refer to Table1 again, you will need use
another name (an alias) to refer to the 2nd one. That's why the subquery
gets its records:
FROM Table1 AS Dupe
i.e. from another copy of Table1.

You have to count some field. Any field will do, as long as it always has a
value, so the primary key is the safest choice to count. That's why you want
to:
SELECT Count(Table1ID)

Now you want to limit that count to ony the records that are ahead of the
current one. Since "ahead" is defined by the date field, you want the count
where the date/time field in the copy is earlier than the date/time in the
row being examined, i.e.:
WHERE Dupe.EnteredOn < Table1.EnteredOn

Now, the count of records ahread of the first row is zero. You probably want
to count the first record as 1, and so on through all the records, so you
need to add 1 to the count.

Putting that together, your subquery is:

(SELECT Count(Table1ID) + 1) AS HowMany
FROM Table1 AS Dupe
WHERE (Dupe.EnteredOn < Table1.EnteredOn)


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This seems to also be a solution to my situtation; does it
appear so to you? When I attempt to translate your example
to my query I'm getting a syntax error, so evidently I
don't guite get it yet. Perhaps you could clarify your
example for the SQL-challenged?

I am creating a waiting list with the position on the list
based on the date and time the record was added. I've done
that using a running-sum counter textbox in a report. In
order to generate an alphabetical listing of these people,
I have to analyze the report in Excel and sort the
spreadsheet based on the client's name.

While this gets the job done, it's a bit of a kludge. I'd
prefer to set a calculated field within the query so the
report can be sorted any way I like (position, name, zip
code, etc) without having to leave Access (and without
having to become a VB programmer).

I found something of a solution in the KB
<http://support.microsoft.com/default.aspx?scid=kb;en-us;
199679>, but hope a simpler solution exists! I'm certain
that if it does, someone here will be able to enlighten me!

Thanks,

-Bob
-----Original Message-----
There is not a nice way to do this, but if you don't mind a read-only result
(e.g. if it's going to a report), you could use a subquery to count the
number of records ahead of this one.

You type the subquery into a fresh column in the Field row in query design
view. Something like this:
Rank: (SELECT (Count([ID]) + 1) AS Rank FROM MyTable AS Dupe WHERE (Dupe.ID
< MyTable.ID))

Where there is a tie, you get typical ranking results, e. g.:
1
2
2
4
5

If you need the records to be updatable, you can use DCount() to create the
calculated field, but it will be very slow.

I am trying to include a row number in a query. Is this
possible?

I am compiling top 10's of product for 4 individual years
and in order to link each individual query I need to
calcaluate a position (position 1 in 2002 will link to
position 1 in year 2001 etc)

Is there a way to do this without having to create tables
with an autonumber field?

Any help would be appreciated
K
Click to expand...
Click to expand...
Click to expand...
 
thanks - it was driving me mad......
-----Original Message-----
There is not a nice way to do this, but if you don't mind a read-only result
(e.g. if it's going to a report), you could use a subquery to count the
number of records ahead of this one.

You type the subquery into a fresh column in the Field row in query design
view. Something like this:
Rank: (SELECT (Count([ID]) + 1) AS Rank FROM MyTable AS Dupe WHERE (Dupe.ID
< MyTable.ID))

Where there is a tie, you get typical ranking results, e.g.:
1
2
2
4
5

If you need the records to be updatable, you can use DCount() to create the
calculated field, but it will be very slow.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to include a row number in a query. Is this
possible?

I am compiling top 10's of product for 4 individual years
and in order to link each individual query I need to
calcaluate a position (position 1 in 2002 will link to
position 1 in year 2001 etc)

Is there a way to do this without having to create tables
with an autonumber field?

Any help would be appreciated
K
Click to expand...


.
Click to expand...
 
Back
Top