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) );
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
