Is there a way to make the # of records in a query or table show

[Guest]

Is there a way to make the # of records in a Table or Query show up by the
Table or query without actually opening the object?

 

[Guest]

You could do a Totals query that counts the record.

You could use code to do it:

DCount("*","YourTableName")

 

[Van T. Dinh]

Check Access VB Help on the DCount() function.

 

[Larry Linson]

Is there a way to make the # of records in a Table or Query show up by the
Table or query without actually opening the object?

What do you mean by "without actually opening the object" and why is that
important? A Totals Query can be prepared to return the number of Records,
but that Query will have to be opened/run, and behind the scenes it will
access the Table or Query about which you want to know the number of
records. It will not display the Table or Query on-screen.

Open the Query Builder window in Design View, add the Table or Query as a
data source, drag down a Field that should be populated in every Record (the
Primary Key, if you have defined one, is best). Run the Query to assure
yourself that it is returning all the information. Then, in Design View of
the Query, on the menu, View | Totals. Set the "Total:" line in the Query
Grid to Count.

Here's the SQL for a Query that does just what you want with a linked table
from the Northwind Traders sample DB that comes with Access.

SELECT Count(Customers.CustomerID) AS CountOfCustomerID
FROM Customers;

Now, I don't know if that is what you want. If not, please clarify, and
maybe someone will be able to help.

Larry Linson
Microsoft Access MVP

 

[Guest]

Larry,

Thanks.

The problem is my boss at my new job has us going in the the tables name and
entering the number of records for example "tblEmployeeName=3751" the problem
is we work with about 40 diffrent databases and tons of tables and Query's
and of course the record count changes frequently so just coming on board I
am having trouble changing his methods.

His theory behind it is becuase some tables/Query's have over 1 million
records he wants to know that before he opens due to the time it takes for
them to run.

So although I do not think there is a way but I wanted to check to see if in
the Database view which shows detail (Name, Description, Modified, Created,
Type) if there was a way to make it show the record count for the Tables and
Queries(w/o Parameters).

Like I said I do not know of a way to do it but if there was it would help
us out alot.

 

[Guest]

Van,

Thanks.

The problem is my boss at my new job has us going in the the tables name and
entering the number of records for example "tblEmployeeName=3751" the problem
is we work with about 40 diffrent databases and tons of tables and Query's
and of course the record count changes frequently so just coming on board I
am having trouble changing his methods.

His theory behind it is becuase some tables/Query's have over 1 million
records he wants to know that before he opens due to the time it takes for
them to run.

So although I do not think there is a way but I wanted to check to see if in
the Database view which shows detail (Name, Description, Modified, Created,
Type) if there was a way to make it show the record count for the Tables and
Queries(w/o Parameters).

Like I said I do not know of a way to do it but if there was it would help
us out alot.

 

[Guest]

Jerry,

Thanks.

The problem is my boss at my new job has us going in the the tables name and
entering the number of records for example "tblEmployeeName=3751" the problem
is we work with about 40 diffrent databases and tons of tables and Query's
and of course the record count changes frequently so just coming on board I
am having trouble changing his methods.

His theory behind it is becuase some tables/Query's have over 1 million
records he wants to know that before he opens due to the time it takes for
them to run.

So although I do not think there is a way but I wanted to check to see if in
the Database view which shows detail (Name, Description, Modified, Created,
Type) if there was a way to make it show the record count for the Tables and
Queries(w/o Parameters).

Like I said I do not know of a way to do it but if there was it would help
us out alot.

 

[Van T. Dinh]

I don't think there is anything in the Properties that Access stores the
number of Records (that you can get at). Since the number of Records is
constantly changing in most cases, there is no point to store the number of
Records which is a Calculated value that should be calculated and not
stored.

DCount() can be used on both Tables & Queries so it is the most appropriate
tool to use if you want to "preview" the Table / Query regarding the number
of Records / row.