Hi
I am setting up a reporting function in my holiday planning database and whilst what I have done works I feel sure there is a better/faster/neater way to do it.
I have set up a table called tblReporting which takes totals from a number of criteria. I ended having to make tables to hold the totals and then updating the records in tblReporting since I could not update from a query with a sum in it.
What I did is create a query which identifies suitable records, in this instance qryHolidaysBooked which has an SQL of

SELECT tblStaffDays.lngEmpNo, tblStaffDays.bytStatCode, tblStaffDays.dtmDate
FROM tblStaffDays
WHERE (((tblStaffDays.bytStatCode)=2 Or (tblStaffDays.bytStatCode)=12))
ORDER BY tblStaffDays.lngEmpNo, tblStaffDays.dtmDate;

Then in a form which is used to set up the dates for the report I have the following code on a button

DoCmd.DeleteObject acTable, "tblTmpHolBook"
cnn.Execute "SELECT [qryHolidaysBooked].[lngEmpNo], Count([qryHolidaysBooked].[dtmDate]) AS Days INTO [tblTmpHolBook]" & _
"FROM [qryHolidaysBooked]" & _
"GROUP BY [qryHolidaysBooked].[lngEmpNo], [qryHolidaysBooked].[bytStatCode]" & _
"HAVING ((([qryHolidaysBooked].[bytStatCode])=2));"
cnn.Execute "UPDATE [tblReporting] INNER JOIN [tblTmpHolBook] ON [tblReporting].[lngEmpId] = [tblTmpHolBook].[lngEmpNo] SET [tblReporting].[dblHolBookDays] = [Days];"

In all there are about ten sets of these delete/make/update statements each working with a different set of criteria, e.g. days accrued, days off ill, days at college etc.

I have it in mind that I should be able to use ADO to work with tblReporting and then update the fields using DCount() straight from the main data holding table but do not know how to start, assuming it is even possible.
There are curently about 15 records in tblReporting and about 10 to 12 fields for each record, so if I can find how to use the DCount() function I should be able to loop through the records having a batch of 10 to 15 one line statements instead of these great SQL strings and all the temporary files that they create.
Could someone perhaps point me in the right direction?
Thanks and best wishes

Malcy