Record Counter in a Query

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

Guest

I have 4 normalized tables which when queried produce a orderd flow for the
production of one of many ( 1000's ) of my companies products. What I get is
something like

Procedure A . . . .
Procedure T . . . .
Procedure 3 . . . .
..
Procedure # . . . .
Procedure D . . . .
Procedure 34 . . . .
Procedure P . . . .
Procedure 0 . . . .
Procedure QW . . . .
Procedure Z1 . . . .
..
..
Procedure n . . . .

Now at various times I will want to target say "Procedure 34 . . . ." and in
so targeting it I want to be able to retrieve "Procedure(s) D - Z1 . . . ."
now these "Procedure's" are not numerical or alphabetically assigned. What
I'm looking for is there away that in the query I can get it to numerically
count up the records something like

1 Procedure A . . . .
2 Procedure T . . . .
3 Procedure 3 . . . .
..
45 Procedure # . . . .
46 Procedure D . . . .
47 Procedure 34 . . . .
48 Procedure P . . . .
49 Procedure 0 . . . .
50 Procedure QW . . . .
51 Procedure Z1 . . . .
..
..
## Procedure n . . . .

Thanks for any help.
 
There is not a simple and efficient way to get this result, as it depends
how the data is sorted and filtered. You must have the data sorted to get
meaningful numbering.

If it is sorted by a primary key field named ID, you could type this into a
fresh column in the Field row:
(SELECT Count ("*") FROM [MyTable] AS Dupe
WHERE (Dupe.[ID] <= [MyTable].[ID]))

Notes:
a) Replace MyTable with the name of your table.

b) If you are sorting your main query by something other than the ID field,
you will need to adjust the WHERE clause in the subquery as well.

c) If you have criteria in the main query, you will need to add the same
criteria to the WHERE clause in the subquery.

d) If you are wanting this for a report, the subquery will probably give you
a "Multi-level Group By error." You can avoid the whole problem in a report
if you just add a text box and set these properties:
Control Source =1
Running Sum Over All

e) If subqueries are a new idea, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

f) For other alternatives, see:
How to Rank Records Within a Query
at:
http://support.microsoft.com/kb/208946/en-us
 
Access gives any calculated column a name if you don't - Expr and a number.
IF you want a specific name then you need to assign the name - For instance,
if you want the name to be MyRecordNumber then in the query grid you would
entere something like:

Field: MyRecordNumber: (SELECT Count ("*") FROM [MyTable] AS Dupe WHERE
(Dupe.[ID] <= [MyTable].[ID]))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

Rod Behr said:
I have used this code to achieve the numbering of rows in a SELECT query.
Thanks for the tip.

Question: When viewing the query results, Access given the new field the
name "Expr1004". Why and is this a consistent thing?

Allen Browne said:
There is not a simple and efficient way to get this result, as it depends
how the data is sorted and filtered. You must have the data sorted to get
meaningful numbering.

If it is sorted by a primary key field named ID, you could type this into
a
fresh column in the Field row:
(SELECT Count ("*") FROM [MyTable] AS Dupe
WHERE (Dupe.[ID] <= [MyTable].[ID]))

Notes:
a) Replace MyTable with the name of your table.

b) If you are sorting your main query by something other than the ID
field,
you will need to adjust the WHERE clause in the subquery as well.

c) If you have criteria in the main query, you will need to add the same
criteria to the WHERE clause in the subquery.

d) If you are wanting this for a report, the subquery will probably give
you
a "Multi-level Group By error." You can avoid the whole problem in a
report
if you just add a text box and set these properties:
Control Source =1
Running Sum Over All

e) If subqueries are a new idea, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

f) For other alternatives, see:
How to Rank Records Within a Query
at:
http://support.microsoft.com/kb/208946/en-us

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

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

iwrk4dedpr said:
I have 4 normalized tables which when queried produce a orderd flow for
the
production of one of many ( 1000's ) of my companies products. What I
get
is
something like

Procedure A . . . .
Procedure T . . . .
Procedure 3 . . . .
.
Procedure # . . . .
Procedure D . . . .
Procedure 34 . . . .
Procedure P . . . .
Procedure 0 . . . .
Procedure QW . . . .
Procedure Z1 . . . .
.
.
Procedure n . . . .

Now at various times I will want to target say "Procedure 34 . . . ."
and
in
so targeting it I want to be able to retrieve "Procedure(s) D - Z1 . .
.
."
now these "Procedure's" are not numerical or alphabetically assigned.
What
I'm looking for is there away that in the query I can get it to
numerically
count up the records something like

1 Procedure A . . . .
2 Procedure T . . . .
3 Procedure 3 . . . .
.
45 Procedure # . . . .
46 Procedure D . . . .
47 Procedure 34 . . . .
48 Procedure P . . . .
49 Procedure 0 . . . .
50 Procedure QW . . . .
51 Procedure Z1 . . . .
.
.
## Procedure n . . . .

Thanks for any help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top