Get Total Rows in Database?

J

JoeM

I have a list of data in a database(access). How can I get the total rows
in the database?

Thanks
 
J

JoeM

I have tried that it will count the columns without any trouble, but I need
to count the rows.
I have 2 columns Customer ID and Name, I need to get the either the last
customerID or how many entries are in the data base.

Example:
CustomerID Name
1 James
2 John
3 Adam
4 Jason

I would need to pull the last customerID. The numbers will increase and are
unknown when trying to get the ID.

Thanks
 
D

Dave Anderson

top.
to
bottom
from
read
not
do
conversations
sentences,
Like
toppost.
not
do
Please

I have tried that it will count the columns without
any trouble, but I need to count the rows.

Count(*) returns a row count. I don't know what makes you think it returns a
column count.


I have 2 columns Customer ID and Name, I need to get
the either the last customerID or how many entries
are in the data base.

Example:
CustomerID Name
1 James
2 John
3 Adam
4 Jason

I would need to pull the last customerID. The numbers
will increase and are unknown when trying to get the
ID.

Show us the SQL you have tried.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
 
B

Bob Barrows [MVP]

JoeM said:
I have tried that it will count the columns without any trouble,

No it won't. Count(*) counts rows, not columns.
but
I need to count the rows.
I have 2 columns Customer ID and Name, I need to get the either the
last customerID or how many entries are in the data base.

First of all, let's get your terminology straight: a "database" is a
collection of tables which are collections of rows and columns of data.

So it seems that you have a database (what is the type and version please?
It's not relevant in this situation, but that's rare. The type and version
of database is almost always relevant) that contains a table that contains
the following rows and columns of sample data.
Example:
CustomerID Name
1 James
2 John
3 Adam
4 Jason

I would need to pull the last customerID. The numbers will increase
and are unknown when trying to get the ID.

Will the number of "entries" in the table still be relevant if one of the
entries gets deleted? You should decide what you really want: the largest
CustomerID

Select Max(CustomerID) from tablename

or the number of rows:

Select Count(*) From tablename

HTH,
Bob Barrows
 
E

Evertjan.

Bob Barrows [MVP] wrote on 20 feb 2006 in
microsoft.public.inetserver.asp.general:
First of all, let's get your terminology straight: a "database" is a
collection of tables which are collections of rows and columns of data.

Bob,

Wouldn't it be far more efficient stick to talking about
[tables,] records and fields?

You are the only one that can implement that on this NG at the moment.

After all, in this sense a record is a collection of fields,
but a field is not a collection of records.

A database is not spreadsheet,
and it is detrimental to the many that see it that way.
 
P

Paxton

Thanks I had some lines mixed up, the code works, Thank you

Select Count(*) to identify the last ID will work *IF* you never delete
a customer record. However, in any real database, it is inevitable
that you will delete a customer record sooner or later. Any new record
will not adopt the deleted record's ID - it will just acquire the next
number that hasn't been used before.

So if you delete John, then add Fred, Fred will be number 5, but your
row count will be 4. If you really want to get the latest ID, you need
SELECT @@IDENTITY for Access, or SCOPE_IDENTITY() for SQL Server 2000+

http://www.aspfaq.com/show.asp?id=2174

/P.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top