How to put the "RowNumber" in a "Query"?

[Javad]

Hello
I have a "Table" containing these fields in Access2003:
ID, Name, Phone

Now I want to make a "Query" of that table, containing these fields:
Number, Name, Phone

I want the query to be sorted by "Name" and I want the "Number" to show the
"RowNumber" of each "Record", and if I add or delete the records of the
list, I want all the numbers to be reset automatically and show the correct
"RowNumber" of their "Records" in the new list.

How can I do this?
Thank you

 

[Bill Edwards]

The following worked for me on Access 2000 (change your field names
accordingly)

SELECT c.Last, c.First,
(SELECT count(*) FROM tblCLient c2 where c2.Last & c2.Clientkey <=c.Last &
c.ClientKey) AS RowNumber
FROM tblClient AS c
ORDER BY c.Last, c.ClientKey

ClientKey is the Primary Key value of the table (Your ID ?)
It has to be included as above in case (as is likely) you have duplicate
names.

 

[Van T. Dinh]

See also Stephen Lebans' RowNumber:

http://www.lebans.com/rownumber.htm

 

[Javad]

Hello
Thank you, but I need the query to use it in a "VisualBasic6" program,
therefore, the codes can't be useful in VB environment. I was looking for a
way to put the rownumbers in the query without using codes, but it seems
like this is impossible, and the only way I have, is either to put the query
without "RowNumbers", or calculate the "RowNumbers" using VB recordset
events.

If anybody knows an easy way to automate this process, please tell me.
(because I'm a beginner in both Access and VB).

Thank you

 

[Allen Browne]

Javad, what are you actually trying to do?

In general, the row numbers are neither desirable nor consistent in
recordsets of a multi-user database. There is an AbsolutePosition property,
but I would discourage you from using it.

The example code in this article might help:
What, no record numbers?
at:
http://allenbrowne.com/xbase-03.html

 

[Javad]

I have a "PhoneBook" query in my DB (Containing name, phone, address, etc.)
and I want its rows to have numbers, because I always want to know the
number of people in the phonebook, but I can't use codes, because this
Access DB is the background DB for a VB6 application. I thought there may be
a simple way to this, but it seems like I was wrong, therefore, I ignored
putting the "rownumbers" in my program.

Thank you

 

[John Vinson]

I always want to know the
number of people in the phonebook

You don't need rownumbers to do so.

=DCount("*", "[tablename]")

in the control source of a textbox will show you that count at any
time.


John W. Vinson[MVP]

 

[Javad]

Hello
Thank you Joh, I wrote this code in a commandbutton's click-event:

Text1.Text= DCount("*", "[Names]") count ("

but the program said "End of statement needed". What's the problem?
My data source is "DataEnvironment1" (Connected to "database.mdb"(MS-Access
2003), and one of its tables named "Names" is connected to "Command1" or
DataEnvironment1. Now How can I count its row numbers?
Thank you

I always want to know the
number of people in the phonebook

You don't need rownumbers to do so.

=DCount("*", "[tablename]")

in the control source of a textbox will show you that count at any
time.


John W. Vinson[MVP]

 

[John Vinson]

Hello
Thank you Joh, I wrote this code in a commandbutton's click-event:

Text1.Text= DCount("*", "[Names]") count ("

That has no relationship whatsoever to what I suggested, of course.

but the program said "End of statement needed". What's the problem?

First off you can't set the Text property of a control; if you want to
"push" data into a textbox use

Me.Text1 = <some expression>

Secondly, what are you EXPECTING to put into the textbox? You have a
function call

DCount("*", "[Names]")

which will return an Integer, a count of the number of records in the
table called [Names]. You follow that with a completly meaningless

count ("

which has an unbalanced parenthesis and an unbalanced quotemark. I
have no way to guess what you might have intended this to do, but it
certainly won't work!

My data source is "DataEnvironment1" (Connected to "database.mdb"(MS-Access
2003),

Is there actually a database named (wierdly) database.mdb? What is
"DataEnvironment1" - a Table? another MDB file? Something else?

and one of its tables named "Names" is connected to "Command1" or
DataEnvironment1. Now How can I count its row numbers?
Thank you

What do you mean by a table being "connected" to "Command1"? I'm
REALLY confused at what you're describing.

Again:

Tables DO NOT HAVE ROW NUMBERS.

They *simply don't exist*.

Stop thinking about row numbers.

If you want to count the number of records in a table, and display
that count in a textbox on a Form, open the Form in design view. Use
the toolbox to add a new Textbox control. View that control's
Properties. In the Control Source property type

=DCount("*", "[Names]")

if Names is the name of your table.

No VBA code of any sort is required.

Is that what you want, or am I missing the point?

John W. Vinson[MVP]

 

[Javad]

Hello John
Thank you for the guide.
I think there have been a misunderstanding here. As I said in my second
post, I can't use any codes in my "Access" database. because I am using the
DB as the database of a "VisualBasic" program. I can only write my codes in
"Visual Basic 6.0" environment. (I don't mean the VB environment of Access
itself, but I mean the stand-alone version of VB6)

The things I said like "DataEnvironment", "Command1", "Text1", and the codes
I wrote, are not related to Access, they are related to VB.

The thing I exactly need, is to put the RowNumbers in my table or query,
without using any codes, in a way that if I add or delete a row, the numbers
will be corrected automatically, I know how to do it in VB, (using some
codes) but I thought there must be a way in Access to facilitate this action
without using codes, but seems like I was wrong.

Thank you

Hello
Thank you Joh, I wrote this code in a commandbutton's click-event:

Text1.Text= DCount("*", "[Names]") count ("

That has no relationship whatsoever to what I suggested, of course.

but the program said "End of statement needed". What's the problem?

First off you can't set the Text property of a control; if you want to
"push" data into a textbox use

Me.Text1 = <some expression>

Secondly, what are you EXPECTING to put into the textbox? You have a
function call

DCount("*", "[Names]")

which will return an Integer, a count of the number of records in the
table called [Names]. You follow that with a completly meaningless

count ("

which has an unbalanced parenthesis and an unbalanced quotemark. I
have no way to guess what you might have intended this to do, but it
certainly won't work!

My data source is "DataEnvironment1" (Connected to
"database.mdb"(MS-Access
2003),

Is there actually a database named (wierdly) database.mdb? What is
"DataEnvironment1" - a Table? another MDB file? Something else?

and one of its tables named "Names" is connected to "Command1" or
DataEnvironment1. Now How can I count its row numbers?
Thank you

What do you mean by a table being "connected" to "Command1"? I'm
REALLY confused at what you're describing.

Again:

Tables DO NOT HAVE ROW NUMBERS.

They *simply don't exist*.

Stop thinking about row numbers.

If you want to count the number of records in a table, and display
that count in a textbox on a Form, open the Form in design view. Use
the toolbox to add a new Textbox control. View that control's
Properties. In the Control Source property type

=DCount("*", "[Names]")

if Names is the name of your table.

No VBA code of any sort is required.

Is that what you want, or am I missing the point?

John W. Vinson[MVP]