CODEWITHSUNDEEP
sqlms-accesssql-updatems-access-2010
Bryan
Bryan

Reputation: 1893

How do I correctly use an Avg function in an update statement?

I am trying to get the average for each MIUID inserted into a table. The relevant tables and column information are as follows:

Table1.[MIU ID], Table1.[Avg RSSI]

and

Table2.MIUID, Table2.MeanNum

If I were simply using a select statement I would do the following:

Select DISTINCT Table1.[MIU ID], Avg(Table1.[Avg RSSI]) as MeanNum
From Table1
GROUP BY Table1.[MIU ID]

However I need this information to be inserted into a column in Table2. I have tried the following and variations of the following and the errors I'm getting are that it won't let me use Group By, and another error saying that MeanNum is not part of the aggregate function. 

UPDATE Table2  
INNER JOIN Table1
ON Table2.MIUID = Table1.[MIU ID]  
SET Table2.MeanNum = Avg([Table1].[Avg RSSI]);

And the other query I've tried is: 

UPDATE Table2  
SET Table2.MeanNum = Avg([Table1].[Avg RSSI]) 
WHERE Table2.MIUID = Table1.[MIU ID]
Group By [Table1].[Avg RSSI]

Summary
To reiterate all I'm trying to do is get the average of the Avg RSSI column in Table1 for each distinct MIU ID and insert each value into the appropriate row in Table2.

Note
There is a column in Table2 called AvgNum that could be average to get the same number that needs to go into the MeanNum column if using that would be easier.

I know how to do what I'm trying to do in two steps I would prefer to be able to do it in one sql statement however.

Upvotes: 1

Views: 8638

Answers (1)

Phil Sandler
Phil Sandler

Reputation: 28046

Edit: the code below will not work in MS-Access/Jet. See this link:

Operation must use an updatable query. (Error 3073) Microsoft Access

Original answer:

You could use the original SELECT query in a subquery and join to it. Not syntax checked and I am more familiar with T-SQL than MS-Access, but something like:

UPDATE 
    t2
SET 
    t2.MeanNum = sub.MeanNum
From 
    Table2 t2
    INNER JOIN 
        (
        Select DISTINCT 
            Table1.[MIU ID], 
            Avg(Table1.[Avg RSSI] as MeanNum
        From 
            Table1  
        GROUP BY 
            Table1.[MIU ID] 
        ) sub
        ON sub.[MIU ID] = t2.MIUID

Upvotes: 2

Related Questions