MS-Access UPDATE statistics per person

Question

Ive got this update query that uses employee activity stored in two tables (Beldata_filter & Urenlog_filter) to create a performance analysis per employee in another table (Bellers_filter).

however, i cannot get my query to update the performance stats per employee using only the activities of that employee in the other two tables, instead, access updates the stats per employee to the total activities of all employees.

here is the update query that i have written, the '.Beller' and '.Naam' signify columns in the tables with the names of the employees that i would like access to distinguish from one another.

UPDATE (Bellers_Filter INNER JOIN Urenlog_Filter ON Bellers_Filter.Naam=[Urenlog_Filter].Naam) INNER JOIN Beldata_Filter ON Bellers_Filter.[Naam]=[Beldata_Filter].Beller SET 
 
Bellers_Filter.[Num b] = Dcount("[Beller]","[Beldata_Filter]"), 
Bellers_Filter.[Num o] = Dcount("[Opgenomen]","[Beldata_Filter]","Opgenomen = 1"), Bellers_Filter.[Num nno] = Dcount("[Actie]","[Beldata_Filter]","Actie = 3"), 

WHERE ([Beldata_Filter].Beller=[Urenlog_Filter].Naam);

The source table Beldata_filter looks like this:

Beller	ID	Moment	Opgenomen	Actie
Robert	55	8-11-2022	1	1
Susan	56	8-11-2022	1	1
Robert	55	9-11-2022	1	2
Robert	55	9-11-2022	0	3
Susan	56	9-11-2022	1	1

in this table each observation describes an action conducted by an employee. The other source table Urenlog_filter has got the same format, in this case the significance of the data in the tables is not as important as the observations of the data.

The table that will be updated needs to look like this

Id	Naam	Num b	Num o	Num nno
1	Robert	3	2	1
2	Susan	2	2	0

This is the desired result in its most simple form, the update query recognises 3 observations for robert and 2 for Susan under "Num b". Right now, the table looks like this

Id	Naam	Num b	Num o	Num nno
1	Robert	5	4	1
2	Susan	5	4	1

Who can help me with this problem? If you guys need more info please let me know!

without tables and data, it is very hard to comprehend what you are trying. i would expect , that after the joins all rows are correctly aligned to be updated, so i am guessing that you should make a select that give you the correct result with the names and use the same joins — nbk, Jan 26 '23 at 13:42
Thanks for the reply, i will add the tables in the post above! — wobbe vdpas, Jan 26 '23 at 15:00
as i saud without tables and data it is hard to grasp it, so add them please — nbk, Jan 26 '23 at 15:04

June7 · Answer 1 · 2023-01-27T04:10:41.683

Saving aggregate data is usually unnecessary and bad design. If it can be calculated for UPDATE, it can be calculated when needed.

SELECT Beldata_Filter.ID, Beldata_Filter.Beller, 
Count(Beldata_Filter.Moment) AS [Num n], 
Count(IIf([Opgenomen]=1,[Opgenomen],Null)) AS [Num o], 
Count(IIf([Actie]=3,[Actie],Null)) AS [Num nno]
FROM Beldata_Filter
GROUP BY Beldata_Filter.ID, Beldata_Filter.Beller;

However, if you really must save, I don't see need for JOINing tables.
Include filter criteria in DCount() for names.

UPDATE Bellers_Filter SET 
[Num b] = DCount("*","Beldata_Filter","Beller='" & [Naam] & "'"), 
[Num o] = DCount("*","Beldata_Filter","Beller='" & [Naam] & "' AND Opgenomen=1"),
[Num nno] = Dcount("*","[Beldata_Filter]","Beller='" & [Naam] & "' AND Actie = 3");

MS-Access UPDATE statistics per person

1 Answers1