Right in the first SELECT include Name to display the count against each Name, because Name is already used in the GROUP BY.
So, by little modification to your original SELECT, you could finish your job.
INSERT [Tbl B]
SELECT [TBL A].[name],Count([TBL A].[Date Given]) AS [CountOfDate Given]
FROM [TBL A]
GROUP BY [TBL A].[name]
-- In 'thoughts'...
Lonely Rogue.
Twitter.Com/LonelyRogue
The subquery below counts all instances of Date Given and groups it by name. This subquery operates from table a.
SELECT Count([TBL A].[Date Given]) AS [CountOfDate Given]
FROM [TBL A]
GROUP BY [TBL A].[name]
So by running the subquery on this table:
-----------TBL A------------
Name --- Date Given
Bob --- 3/3/2013
Bob --- 7/3/2013
Steve --- 4/3/2013
Steve --- 5/3/2013
Sven --- 6/3/2013
Sven --- 4/3/2013
Sven --- 5/3/2013
Sven --- 6/3/2013
The output is:
2
2
4
I'm trying to put the above output into table b.
Currently, table B looks like this:
-----------TBL B------------
Name --- Object Count
Bob
Steve
Sven
I need the output to be like this:
-----------TBL B------------
Name --- Object Count
Bob --- 2
Steve --- 2
Sven --- 4
This subquery returns the output (column of numbers). I need to put this column of numbers into table b but the below SQL code isn't working:
UPDATE [TBL B]
SET [Object Count] =
(
SELECT Count([TBL A].[Date Given]) AS [CountOfDate Given]
FROM [TBL A]
GROUP BY [TBL A].[name]
);
Any pointers?
Using Microsoft Access 2013 by the way.
