Calculating Field-Specific Averages from Records in Separate Tables

[Xzfgiiimtsath]

Hi, I'm very new to Access (just started using it this week), and I've been playing around with it making databases for fun (as weird as that might sound). I've tried researching this problem on Google, and I always get answers that I simply do not understand, or that don't apply to my issue. Honestly, I'm not sure if Access is even capable of doing what I want it to.

Let's say I have a table sort of like this one: http://i.imgur.com/OTvQJJk.png (Table 1)



What I want to do is make another table that references the first table: http://i.imgur.com/YOPYAuR.png (Table 2)

I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.

Can this be done in Access? If it can, I'd like to know, no matter how convoluted the solution might be. While I'm not very Access-savvy just yet, I can follow instructions from the beginning as well as anyone else can.

[Bob Fitz]

Actually, the required result is quite easy to achieve, but not by using the method to outline.
The average of something is a calculation and should NOT normally be saved in a table. It should be calculated when ever and where ever it is required. So, assuming you have a table called "Table 1" use this:

SELECT Table 1.Color, Avg(Table 1.Price) AS AvgOfPriceFROM Table 1
GROUP BY Table 1.Color

as the SQL statement of a query to get the result.

BTY, it would be better not to use spaces in the names of fields, tables, queries etc so a better name for "Table 1" would be "tblTable1". Also better to have a Primary Key in each table.