sqlms-accessinsert-into

Insert a records count into a different table

I need to write the count of all records in one table into another. I am using an INSET INTO statement, it seems pretty straightforward. Access returns that I am making a syntax mistake. Here is my query: 

   INSERT INTO tblA (Field1)
   VALUES (SELECT COUNT(tblB.ID) FROM tblB);

What is the problem here? Access documentation says you are allowed to give a query as argument for VALUES. Is it the aggregation that Access doesn't like?

Solution

  • Use INSERT INTO...SELECT:

    INSERT INTO tblA (Field1)
SELECT COUNT(tblB.ID) FROM tblB