Using Avg With Filtered Data

Hi everyone, I am new to writing queries in access and I have got stuck with this one.



I have 2 tables containing similar data from 2 different paradigms.



The main columns I am interested in are not unique - labelled ID and Delta Ex (a score). The same ID values can occur more than once in the same table, so I am interested in calculating the average Delta Ex score for each ID value in each table.



I then want to ask what ID values occur in both table 1 and table 2 and then calculate the average score of each common ID values across both tables. i.e. ID 1 score = avg(table1.delta ex, table 2.delta ex).



I then want to rank the top 50 scores.



So far I have the following to find common ID values, with a score above a threshold set by the input string threshold. These are ordered with in descending score value.



SELECT [MODEL 1].[ID], [MODEL 1].[Delta Ex]
FROM [MODEL 1], [MODEL 2]
WHERE ((([MODEL 1].[Delta Ex])>[threshold]) And (([MODEL 2].[Delta Ex])>[threshold]) And (([MODEL 1].[Accession])=[model 2].[accession]))
ORDER BY [MODEL 1].[Delta Expression] DESC;

With a second query I can select the top 50 scores:



SELECT TOP 50 [query 1].ID, [query].[Delta Ex]
FROM [query 1]
ORDER BY [query 1].[Delta Ex] DESC;

However, I can't for the life of me work out how to obtain an avg score for each ID value.



Any help would be very much appreciated.



J