如何在更新语句中正确使用Avg函数？

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

我试图将每个MIUID的平均值插入表中。相关表格和栏目信息如下:

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

表1. [MIU ID],表1. [平均RSSI]

and

Table2.MIUID, Table2.MeanNum

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

如果我只是使用select语句,我会执行以下操作:

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.

但是,我需要将此信息插入Table2中的列。我已经尝试了以下和以下的变体,我得到的错误是它不会让我使用Group By,另一个错误说MeanNum不是聚合函数的一部分。

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.

总结重申我要做的就是获取Table1中Avg RSSI列的平均值,以获得每个不同的MIU ID,并将每个值插入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.

注意Table2中有一个名为AvgNum的列可以是平均值,以获得需要进入MeanNum列的相同数字,如果使用它更容易。

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.

我知道如何按两个步骤做我想做的事情,但是我希望能够在一个sql语句中做到这一点。

1 个解决方案

#1

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

编辑:下面的代码在MS-Access / Jet中不起作用。看到这个链接:

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

操作必须使用可更新的查询。 (错误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:

您可以在子查询中使用原始SELECT查询并加入它。没有语法检查,我比MS-Access更熟悉T-SQL,但是类似于:

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

#1