Also posted in error to microsoft.public.access.reports
I have a database that records staff attendance at my place of work. Each member of staff has 2 coloums in a table (ATTENDANCE Table), one morning and one for the afternoon, e.g. TK AM and TK PM. The table holds a variety of codes that describe the attendance (S=Sick, P=Present, L=Leave etc).
I would like to produce a report showing how much sickness any individual has taken during the year. I have tried the following which works (on a Form):
=DCount("[ID]","[ATTENDANCE REPORT Query]","[TK AM]='S' ")
Returns 11 which is correct.
=DCount("[TK PM]","[ATTENDANCE Table]","[TK PM]='S' ")
Returns 10 which is correct.
I then tried to sum the 2 (Morning and Afternoon):
=DCount("[TK PM] & [TK AM]","[ATTENDANCE Table]")='S'
Returns 0 the correct answer would be 21
What's wrong? Also what would be the correct syntax to return a Total value for 'S' for the whole table?
My other problem is that if I use the first working example as the control on a Report it returns a report with Multiple duplicate entries and pages all reporting TK AM = 11. How do I need to design the report so that It displays just the single result?
Thanks to everyone for their held and a Happy New Year to one and all.
Anything else is going to be pretty messy. You could create a calculated column in a query that equates to 0,1 or 2 depending on whether TK has 0, 1 or 2 "S" for the day (using iif statements). Then DSUM the column with TK as the criteria.
Regarding your second problem, if all you want to see in your report is the above result then create a report but don't associate it with a record source i.e. make the record source blank.