Aggregate Function in a Macro?

[johncollier1]

I have a created a very simple application that I want to use to track how often I have to remind employees to ware their PPE. So I have a two table database. One table for the employee names and their status, status being the number of times I’ve had to remind them about their PPE in the last twelve months e.g. Jims been told twice, his [Status]=2, Fred’s been told three times in the last twelve months so his [Status]=3 and so on.
The other table records the incident, so [EmployeeID] and a date field. Very simple
However what I want to do is create a named Macro that performs an aggregate function on the incident table to work out how many times an employee has been spoken to in the last twelve months, and then use that aggregate to update the [Employee].[Status] field in the employee table.
I’ve tried several approaches to achieve this with little success. The logic just escapes me.
Can anyone point me in the right direction? This thing is driving me to distraction :banghead:

 

[CJ_London]

can you post your table structure - fieldname and type - will give us a bit more to go on!

 

[johncollier1]

Thanks CJ
I have an Employee table with fields

EmployeeID (autonumber)
EmployeeName (text)
DepartmentID (Number)
Status (number)

and an incident table with fields

IncidentID (autonumber)
EmployeeID (number) this has a relationship to the Employee table
IncidentDate (date/time)

Now, I want to find from the Incident table, how many times has an employee been spoken to in the last twelve months, and put that number into the [Employee].[Status] field

this is so that I can generate a report from the Employee Table that shows the [Status] of all employees, not just those in the IncidentTable

I've also created a Query that performs the aggregate I'm looking for, but I've not been able use to the result from that to update the [Employee].[Status] field

I've tried to create a DataMacro to update [Employee].[Status] but the aggregate functions aren't available in DataMacros and the Query can only be called in a "For Each Record In" Data Block, which makes any results from the Query read only.

I want to do this in a Macro if possible so that the App will upload to a sharepoint server

I'm using Access 2010 By the way

Sorry for the long thread, hope this gives enough info

Regards and Thanks

 

[CJ_London]

This should be the SQL you need - just need to check the field names match to your actual names. When you say 'last 12 months' I have taken today less a year. If you actually mean 'end of last month' or something similar you will need to change the bit in red

UPDATE Employee INNER JOIN (SELECT Incidents.EEID, Count(1) AS noofincidents
FROM Incidents
WHERE Incidents.IncidentDate>=DateDiff('yyyy',-1,[COLOR=red]Date()[/COLOR]) 
GROUP BY Incidents.EEID) As TMP on employee.eeid=tmp.eeid set employee.status=tmp.noofincidents

 

[johncollier1]

Wow
I had considered using SQL
Ill give it a go

Many Thanks CJ

 

[johncollier1]

Hi CJ
The code doesnt work, instead it thows up two input boxes asking for Perameter values for Incident.EEID and Employee.EEID

The aggregate Query I generated to count the incidents for an employee looks like this

SELECT Incidents.EmployeeID, Employee.EmployeeName, Count(Incidents.EmployeeID) AS CountOfEmployeeID
FROM Employee INNER JOIN Incidents ON Employee.EmployeeID = Incidents.EmployeeID
WHERE ((([Incidents]![IncidentDate]) Between Now() And Now()-365))
GROUP BY Incidents.EmployeeID, Employee.EmployeeName;

I use the WHERE criteria like this because i want an incident to expire after a year (365 days)

so for an Incedent table that looks like this

ID        employeeID     IncidentDate
1            1                 01/01/2011
2            1                 17/03/2013
3            3                 03/01/2013
4            3                 09/03/2013
5            3                 18/03/2013
6            5                 07/11/2012 
7            5                 13/02/2013

The Aggregate query generates this result

EmployeeID   EmployeeName   CountOfEmployeeID
1                      john                   1
3                      fred                    3
5                      sam                    2

Note that employee 1 (john) has two incidents but only has 1 incidaent in the [CountOfEmployeeID] field this is because one of his incidents is out of date

Now, what I cant do is use the result of the Aggregate Query to update the Employee Status [Employee].[Status]=[CountOfEmployeeID]

Actualy, while writting this another problem as just occured to me
in 365 days time john's incident will be out of time and thus no longer appear in the Query results and so his [Status] will not be updated to 0

I think the logic of what I'm trying to do goes like this

For each Employee in the [Employee] Table
Check if they appear in the Query Results
IF they do THEN Update their Status [Employee].[Status]=[CountOfEmployeeID]
Else they dont appear in the Query Results [Employee].[Status]=0

I just cant make this thing work in this way:banghead:

 

[CJ_London]

You need to change the names to suit what you have

just need to check the field names match to your actual names

UPDATE Employee INNER JOIN (SELECT Incidents.[B][I]EmployeeID
[/I][/B], Count(1) AS noofincidents
FROM Incidents
WHERE Incidents.IncidentDate>=DateDiff('yyyy',-1,Date()) 
GROUP BY Incidents.[B][I]EmployeeID
[/I][/B]) As TMP on employee.[B][I]EmployeeID
[/I][/B]=tmp.[B][I]EmployeeID
[/I][/B] set employee.status=tmp.noofincidents

 

[johncollier1]

Hi CJ
I've checked the field names but Still not working

I've tried to change your code to refleft my logic and this is what it looks like

UPDATE Employee INNER JOIN (SELECT Incidents.EmployeeID, Employee.EmployeeName, Count(Incidents.EmployeeID) AS CountOfEmployeeID
FROM Incidents
WHERE ((([Incidents]![IncidentDate]) Between Now() And Now()-365))
GROUP BY Incidents.EmployeeID As TMP on employee.EmployeeID=tmp.EmployeeID
set employee.status=tmp.CountOfEmployeesID

But this raises a Syntax Error in the FROM clause


Regards
John

 

[CJ_London]

Your code won't work because you are bringing through Employee.EmployeeName which is not in the incidents table so should be removed (also it is not required for the update that you want), you are also missing a bracket see my code.

UPDATE Employee INNER JOIN (SELECT Incidents.EmployeeID, [COLOR=red]Employee.EmployeeName[/COLOR][COLOR=red],[/COLOR] Count(Incidents.EmployeeID) AS CountOfEmployeeID FROM Incidents WHERE ((([Incidents]![IncidentDate]) Between Now() And Now()-365)) GROUP BY Incidents.EmployeeID As TMP on employee.EmployeeID=tmp.EmployeeID
set employee.status=tmp.CountOfEmployeesID

My code

UPDATE Employee INNER JOIN (SELECT Incidents.EmployeeID
, Count(1) AS noofincidents
FROM Incidents
WHERE Incidents.IncidentDate>=DateDiff('yyyy',-1,Date()) 
GROUP BY Incidents.EmployeeID
[COLOR=red]) As TMP[/COLOR] on employee.EmployeeID
=tmp.EmployeeID
set employee.status=tmp.noofincidents

just try pasting my code into a query and see if it runs

 

[johncollier1]

Hi C J

I'v pasted your code into a new empty query saved it, then run it

I get an error message that says


! Operation must use an updatable query

I can see this is an update query so I dont see where the error is being generated?

See Attachment

Thank for being pationt

 

[CJ_London]

Ah, my fault - I forgot update queries can be quite picky

try this instead:

UPDATE Employee SET Employee.Status = DCount("IncidentID","Incidents","EmployeeID=" & [employeeID] & " AND IncidentDate>=" & DateDiff('yyyy',-1,Date()))

 

[johncollier1]

Noop, that not it either, now I get a different Error (See attachment)

I knew this was going to be a problem, I've been puzzling over it for a couple of days :banghead:

 

[CJ_London]

I'm puzzled - the error message implies that the datatype does not match - The only thing I can think of is EmployeeID or Incidentdate in the Incident table - should be number and date respectively, and EmployeeID in the employee table should also be a number

I've attached a test db where it does work - can you compare with your own tables

 

[johncollier1]

Hi CJ
Thanks so much for the sample
I've tried it out and it works fine, or that is it did at first
I tried adding more incidents and it worked fine so i delleted some incidents and it still worked great

so I decided to change the date of an incident to put it out of date and see if it updated correctly after that.

but the incident was still recognised by the query, and whats more if I then add more out of date incidents for the same employee they are also added to the result and updated. My simple select query still fillters the data correctly (See attached image)

I wonder, could this be some kind of Access bug? where Update querys only like to perform calculations on original data but somehow they dont like to work with data that has been changed

 

[CJ_London]

Sorry - my mistake, should have been using dateadd rather than datediff!

Also, added in '#' which seems to make a difference in this case

DCount("IncidentID","Incidents","[EmployeeID]=" & [employeeID] & " AND [IncidentDate]>=#" & DateAdd('yyyy',-1,Date()) & "#")

I'm working too many hours!

 

[johncollier1]

Thats it

Thanks so much CJ

You've inspired me to take a Better look at SQL

Kind regards
John

 

[CJ_London]

glad to help!