0

Is it possible to use the result of query as the value of a field in an INSERT query? This is what I'd like to achieve but am I going about it the right way?

INSERT INTO tblCounts ( CategoryID, GroupID, CountNo )
VALUES (DMAX("CategoryID","tblCategories"), (SELECT GroupID from tblGroups), 0);
JLome
  • 3
  • 2

1 Answers1

1

You should use this:

INSERT INTO tblCounts ( CategoryID, GroupID, CountNo )
select DMAX("CategoryID","tblCategories"), GroupID, 0
from tblGroups;

But it seem that DMAX is Access function, not MySQL?

For condition with a CountNo of 3 and CategoryID of 2 you should add where clause to the select query like this:

INSERT INTO tblCounts ( CategoryID, GroupID, CountNo )
select DMAX("CategoryID","tblCategories"), GroupID, 0  -- select 2, GroupID, 3 from tblgroups where CategoryID = 2 and CountNo = 3--?
from tblGroups
where CategoryID = 2 and CountNo = 3;
Pham X. Bach
  • 5,284
  • 4
  • 28
  • 42
  • I'm using SQL view in access, apologies for the confusion! This works, but to complicate things a bit more - I also want to add a condition to my query so that only groups with a CountNo of 3 and CategoryID of 2 are appended. How would I add this clause and make reference to `tblCounts`? – JLome May 18 '16 at 16:39
  • @JLome then you just add `where CategoryID = 2 and CountNo = 3;` to the select subquery. Could you check if that's what you need? – Pham X. Bach May 18 '16 at 16:45