Update query (1 Viewer)

I am creating two update queries and neither are working right. I am hoping someone has some suggestions to get this to function correctly.

I have two tables
One called edms_batch_info
One called edms_index_history

I want a query to be run on the child tables records edms_index_history and update a parent record field on edms_batch_info

I can not get query 1 to work at all.
Query 2 gives the access error
"You tried to execute a query that does not include the specified expression 'num_of_doc' as part of the aggregate function."

Query 1

UPDATE edms_batch_info
set num_of_doc = Count (edms_index_history.num_of_doc)
from edms_index_history
where edms_index_history.batch_num =edms_batch_info.batch_num;


Query 2

UPDATE edms_batch_info INNER JOIN edms_index_history ON edms_batch_info.batch_num = edms_index_history.batch_num SET edms_batch_info.num_of_doc = Sum(edms_batch_info.num_of_doc)
WHERE (((edms_batch_info.batch_num)=[edms_index_history].[batch_num]));


Any help would be greatly appreciated.

Kat

Make two queries.

First query looks like

SELECT batch_num, Count([num_of_doc]) AS doc_count FROM edms_index_history GROUP BY batch_num ;

Second query looks like

UPDATE edms_batch_info SET num_of_doc = doc_count FROM query1 WHERE edms_batch_info.batch_num = query1.batch_num ;

This is one of the cases where "divide and conquer" works best, I think.

You may have answered my question I can not do it in access. I can do it in Oracle so I may have to go the Oracle and VB route instead. We have an access front end, but our tables are really in Oracle so I may just have to get away from Access entirely.

Thanks
Kat

Not really. Access should be able to do this for you even only operating as a front end for an ODBC-linked ORACLE.