Add [count] and [average] to existing table

[jpunja]

Although I know this is not how database-tables should be used, I would like to add two columns average and count to my existing table. The original table:

Name	Age	City
John	10	NY
John	80	London
John	30	NY
Bill	10	NY
Bill	40	NY
Bill	25	NY

I would like to add two columns:
average of age group by name and CITY = NY
Count: group by name and CITY = NY

So you get:

Name	Age	City	count	average
John	10	NY	2	20
John	80	London	-	-
John	30	NY	2	20
Bill	10	NY	3	25
Bill	40	NY	3	25
Bill	25	NY	3	25

I can make a table with average and count, and join this table in a second query. But I can't get it into one query. Hopefully you have the solution!

[JoeM]

Cross-posted here: http://www.mrexcel.com/forum/microso...ing-table.html

[ranman256]

build a 'report' table like yours.
empty the table
run an append query to add the names, count
then run another append query to post the avg.
then run a select query to sum all the columns for your results.