Report question regarding totals

[Ixtreme]

I would like to generate a report that displays the total number of
records per table using the primary key field. Looks like a simple
question, however, I can't get it to work.

Something like:

Table 1: Total records 100
Table 2: Total records 78
Table 3: Total records 23

 

[Douglas J. Steele]

Not quite sure what you mean by "the total number of records per table using
the primary key field". How would that be any different than the total
number of records in the table?

The following SQL will give you a list of tables and the number of rows in
each:

SELECT [Name], DCount("*", [Name]) AS TotalRows
FROM MSysObjects
WHERE [Type] IN (1, 4, 6)
AND Left([Name], 4) <> "MSys"
ORDER BY [Name]

Note that depending on how big your tables are, how many tables there are,
and whether the tables are in some other DBMS to which you're connected, the
query could take a while to run.

(In case you're interested, entries with a value of 1 for Type are in the
current database, entries with a value of 6 are linked tables, and entries
with a value of 4 are linked via ODBC)

 

[Ixtreme]

Hi Doug,

Exactly what I needed. Big thanks!

Mark