CODEWITHSUNDEEP
ms-accessms-access-2003
user2825585
user2825585

Reputation: 19

SQL query to count rows in a table

I have a table (MYTABLE) in ms access 2003 with the following columns: table name, columnname and count I would like to read through the table and dynamically create an SQL statement for each table and column, run the SQL to get the count and update the 'count' column in the table.

Eg

My table has

Tablename columnname count
PATIENT.  AGE.          0

Generate SQL select Count(*) from PATIENT WHERE AGE IS NOT NULL

Update 'count' column in my table with the vaL of count

Upvotes: 1

Views: 1277

Answers (2)

HansUp
HansUp

Reputation: 97101

You can use the DCount Function in an UPDATE query.

UPDATE MYTABLE
SET [count] =
    DCount('*', [Tablename], '[' & columnname & '] Is Not Null');

That UPDATE statement examines each row in MYTABLE, gives DCount() the values stored in the Tablename and columnname fields, and stores the number returned from DCount in a field named count in the same row.

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Update tablename
set count=(select Count(*) from PATIENT WHERE AGE IS NOT NULL)
where table_name=PATIENT

Upvotes: 0

Related Questions