Update table with result from query

[Heidestrand]

Hello community,

I'm dealing with a rather small problem at the moment. I want to update a column in my table with a value from a SELECT Count query. But Access tells me that the operation must use an updateable query. The thing is that I don't know how to do it :/.
Just to give you an example: In this table I want to count how often the system appears within a date time frame:

It's "4" in this case based on the constraint in my code.

And now I want to update this table respectively column Interim01 with the value 4 for the system "Artis one China":

I tried to perform this update with this code:

UPDATE tblUnitsNewOrders
SET Interim01 =
(SELECT Count(System) AS Ausdr1
FROM tblReport
WHERE (tblReport.[System]) LIKE 'Artis zee ceiling' AND tblReport.OrderDate Between #2/5/16# And #2/18/16#)
WHERE tblUnitsNewOrders.System LIKE 'Artis one China';

.. but it doesn't work.

Does anyone know how I make this work?

Really appreciate your help

 

[arnelgp]

sorry, its me again:

PDATE tblUnitsNewOrders
SET tblUnitsNewOrders.Interim01 = DCount("[System]","TblReport", "[System] Like 'Artis zee ceiling' AND [OrderDate] Between #2/5/16# And #2/18/16#) WHERE tblUnitsNewOrders.System LIKE 'Artis one China';

 

[Heidestrand]

Don't be sorry, arnelgp, you're always helpful

Thank you, I read something about DCount later as well

Btw, is it also possible to check a whole list of items? So like if I had a list of systems in my table that I want to update (tblUnitsNewOrders), could I take this list and check the amount of every item in my other table (tblReport)?

 

[arnelgp]

it would be easy if both tables have pk field, then you can create a junction table with one field pk of the table to be updated and one field, pk of updating table.

we can join this table to the other 2 table and do the update.

 

[Heidestrand]

I will try that later and will come back to you if I've questions