update table from query (1 Viewer)

basilyos

Registered User.
Local time
Yesterday, 23:03
Joined
Jan 13, 2014
Messages
252
hello guys,


i have a query that counts how many times the same record exist


and i have table that i want to update the field on counter with the data from this query


this is the query
Code:
SELECT Count(tbl_Personal_Sanctions_02.Sanction_Date) AS txt_Numbers_Original
FROM tbl_Personal_Sanctions_02
WHERE (((tbl_Personal_Sanctions_02.PID)=[Forms]![frm_Sanctions_Out]![PID]) AND ((tbl_Personal_Sanctions_02.Sanction_Date) Between [Forms]![frm_Sanctions_Out]![Sanctions_Date]-3650 And [Forms]![frm_Sanctions_Out]![Sanctions_Date]) AND ((tbl_Personal_Sanctions_02.Sanction_Code.Value)=[Forms]![frm_Sanctions_Out]![Sanctions_Code_01]));


now i want to update the field code1counter in table 1 with the data from the field txt_Numbers_Original in the query


any solution plz
 

isladogs

MVP / VIP
Local time
Today, 06:03
Joined
Jan 14, 2017
Messages
18,186
Why save the results of your count? Just obtain the values as needed using your aggregate query

If you really insist on saving them, you have a problem to overcome.
Aggregate queries are READ ONLY. So if you run an update query on the destination table using your aggregate query as the source data, I believe you will get an error that you must use an updateable query

To get around that, you could save the results of your aggregate query to a TEMP table using a MAKE TABLE query. Then use the TEMP table as the source for your UPDATE query

The issues with this approach are that:
a) the values won't necessarily be up to date.
b) repeatedly making TEMP tables will cause database bloat & may lead to instability
 

Users who are viewing this thread

Top Bottom