Issue in running an update query in access

Question

I have a relational access database and I want to update a table based on another table. You can see relations in the picture. SQL statement is as bellow. When I try to update I face "Your query does not include the specified expression "TRX900" as part of an aggregate function."

But When I try to see in datasheet view mode, it is OK. Your support is appreciated.

Update
  (
    (
        Sites INNER JOIN Cells ON Sites.ID = Cells.SiteID
    ) INNER JOIN Cells_2G ON Cells.ID = Cells_2G.[Cell ID] 
  ) ,
  ImportedTRX INNER JOIN ActiveStatus ON ImportedTRX.[Active Status] = ActiveStatus.Status
Set
  Cells_2G.TRX900=Sum( IIF ( ImportedTRX.Frequency <=124 , 1,0 )  )
  , 
  Cells_2G.TRX1800=Sum( IIF ( ImportedTRX.Frequency >=512 , 1,0 )  )
WHERE 
  (
    ImportedTRX.[cell name]=[Sites].[SiteID] & [Cells].[Cell_Order]
    AND 
    ActiveStatus.YesNo=True
  )
;

Sites table sample:

-----------------------
|    ID      | SiteID |
-----------------------
|     1      | T4000X |
-----------------------

Cells table sample:

------------------------------------
|    ID      | SiteID | Cell_Order |
------------------------------------
|     1      |    1   | A          |
|     2      |    1   | B          |
|     3      |    1   | C          |
------------------------------------

Cell_2G sample table:

------------------------------------------------------------
|    ID      | CellID | Expected TRX900 | Expected TRX1800 |
------------------------------------------------------------
|     1      |    1   |        1        |        2         |
|     2      |    2   |        2        |        1         |
|     3      |    3   |        2        |        3         |
------------------------------------------------------------

ImportedTRX table sample

-------------------------
| Cell Name | Frequency |
-------------------------
|  T4000XA  | 800       |
|  T4000XA  | 801       |
|  T4000XA  | 22        |
|  T4000XB  | 4         |
|  T4000XB  | 33        |
|  T4000XB  | 860       |
|  T4000XC  | 20        |
|  T4000XC  | 21        |
|  T4000XC  | 840       |
|  T4000XC  | 841       |
|  T4000XC  | 842       |
-------------------------

Answer 1

After some try I used following code, but it is slow yet.

UPDATE 
  ImportedTRX, 
  (
    Sites INNER JOIN Cells ON Sites.ID=Cells.SiteID
  ) INNER JOIN Cells_2G ON Cells.ID= cells_2G.[Cell ID] 
SET 
  Cells_2G.TRX900 = DSUM("IIF(Frequency<=124,1,0)", "ImportedTRX", "[Cell Name]='"& Sites.SiteID & Cells.Cell_Order & "'")
  , 
  Cells_2G.TRX1800 = DSUM("IIF(Frequency>=512,1,0)", "ImportedTRX", "[Cell Name]='"& Sites.SiteID & Cells.Cell_Order & "'")
WHERE 
  (
    ImportedTRX.[Cell Name]=Sites.[SiteID] & Cells.[Cell_Order]
  );

Answer 2

There are two approaches to this problem:

1. Create 2 queries, 1 preparing the result and 1 executing the update, where the
2. Parse using VBA.

I'm going to share the second approach.

The first query is essentially your current query converted to a SELECT query, only the table you're updating has been removed

Query Query1:

SELECT
  Sum( IIF ( ImportedTRX.Frequency <=124 , 1,0 )  ) As TRX900
  , 
  Sum( IIF ( ImportedTRX.Frequency >=512 , 1,0 )  ) As TRX1800,
  Cells.ID

FROM
    (
        Sites INNER JOIN Cells ON Sites.ID = Cells.SiteID
    ),
  ImportedTRX INNER JOIN ActiveStatus ON ImportedTRX.[Active Status] = ActiveStatus.Status
WHERE 
  (
    ImportedTRX.[cell name]=[Sites].[SiteID] & [Cells].[Cell_Order]
    AND 
    ActiveStatus.YesNo=True
  )
GROUP BY
  Cells.ID
;

Then, we're going to update the table using DLookUp and querying from that query:

Query Query2:

UPDATE Cells_2G 
SET
Cells_2G.TRX900= DLookUp("TRX900", "Query1", "ID = " & [Cell ID]),
Cells_2G.TRX1800= DLookUp("TRX1800", "Query1", "ID = " & [Cell ID])

This produces the desired result, though you haven't included the ActiveStatus table so I couldn't include that in testing.

Unfortunately, the statement is too complex for me to write into a single update query, so this two-step approach is the best non-VBA solution I can come up with.