Count function in Query always Read Only?

[terbs]

I have added several counts/sums to an existing query my company uses on our switchboard. Since then the query has become Read Only, which is a problem because it needs to be updateable.



Is there any work around I can use to allow me to have a count within a query while still being updateable? I have tried using DCount, but its way to inefficient, the screen refreshes like 3-4 times before anything can be used.

Here is my query if someone could suggest a change.

SELECT x.EMPINITIALS, x.EMPNAME, x.EMPLOYEEID, x.Version, DEFAULTS.repdate, x.status, x.comments, x.State, 
 
(SELECT Count(y.CallNumber) 
FROM TABservice as y 
WHERE y.Engineer = x.EMPINITIALS AND y.DateLogged > #1/1/2010#) AS CallCount, 
 
(SELECT Count(y.ID) 
FROM TBLServiceCallItem as y 
WHERE y.Engineer = x.EMPINITIALS AND y.Date >#1/1/2010#) AS JobCount, 
 
(SELECT Round(Sum(y.LabourTime),1) 
FROM TBLServiceCallItem as y 
WHERE y.Engineer = x.EMPINITIALS AND y.Date>Date()-7) AS LabourSum, 
 
(SELECT Round(Sum(y.TravelTime),1) 
FROM TBLServiceCallItem as y 
WHERE y.Engineer = x.EMPINITIALS AND y.Date>Date()-7) AS TravelSum
 
FROM DEFAULTS INNER JOIN TBLEmployee AS x ON DEFAULTS.BRANCHID = x.EMPLOYEEID
 
WHERE (((x.EMPLOYEEID)>9) AND ((x.CURRENT)=True))
 
ORDER BY x.State;

[RuralGuy]

Yes, queries that aggregate records are read-only. They are read only because the records are ambuguous and Access would have no idea which record to update. Where did you have the DCount()? Was it the ControlSource of a control on your form?

[terbs]

No basically just

SELECT x.field, MyCount: DCount("[Field]","[Table]","[x] = " & [FieldX]), xfield2 etc etc

Thanks for your reply anyway have found a solution, not that im really happy with it.

I suggested adding fields to the database and updating them on open, but my boss is happy with a second subform adjacent to the existing. Messy option in my mind.

[RuralGuy]

So you are displaying the various counts in another SubForm on your form? Hard to envision but sounds like a useful solution. Thanks for posting back with your solution.