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?

3 Answers

I would do it with insert . . . select as Tim recommends. But, I want to explain what is wrong with your syntax.

The syntax for insert . . . values is:

insert into tblA(field1) values ( . . . );

Notice the parentheses. In addition, a subquery always needs to have its own parentheses. So, to make your version work, you need an extra set of parentheses:

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

1

Use INSERT INTO...SELECT:

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

this the easy way we can use COALESCE, i hope it's usefull

SELECT table1.id, COALESCE(table2_count, 0) AS table2_count, COALESCE(table3_count, 0) AS table3_count
FROM users
LEFT JOIN ( SELECT id, COUNT(*) AS table2_count FROM table2 GROUP BY id
) table2_counts ON table2_counts.id = table1.id
LEFT JOIN ( SELECT id, COUNT(*) AS table3_count FROM table3 GROUP BY id
) table3_counts ON table3_counts.id = table1.id

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy