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
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.
UPDATE statement does iterate through the rows contained in MYTABLE. What was the error message you received?Update tablename
set count=(select Count(*) from PATIENT WHERE AGE IS NOT NULL)
where table_name=PATIENT
