Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Unanswered: Dcount - I'm Stuck

    Also posted in error to microsoft.public.access.reports

    Greetings,

    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.

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    First of all I think your table design is wrong. If I understand you correctly, you have a table that looks like:

    ID___TK AM____TK PM____AA AM__AA PM___AB AM__AB PM......

    Is this how you have it structured? If so then consider structuring it like this:

    Date___Name___Shift___Attendance

    1/1/6___TK______AM______S
    1/1/6___AA______AM______P
    1/1/6___AB______AM______P
    2/1/6___TK______PM______P
    2/1/6___AA______AM______P
    2/1/6___AB______AM______P

    You can include an ID field if you wish. If you do it this way then the count that you require becomes:

    =DCount("[Date]","[ATTENDANCE REPORT Query]","[Name]='TK' ")

    I've assumed you only have am and pm. Obviously you can restrict by date
    also.

    hth
    Chris

  3. #3
    Join Date
    Apr 2004
    Posts
    15
    Quote Originally Posted by howey
    First of all I think your table design is wrong. If I understand you correctly, you have a table that looks like:

    ID___TK AM____TK PM____AA AM__AA PM___AB AM__AB PM......

    Is this how you have it structured? If so then consider structuring it like this:

    Date___Name___Shift___Attendance

    1/1/6___TK______AM______S
    1/1/6___AA______AM______P
    1/1/6___AB______AM______P
    2/1/6___TK______PM______P
    2/1/6___AA______AM______P
    2/1/6___AB______AM______P

    You can include an ID field if you wish. If you do it this way then the count that you require becomes:

    =DCount("[Date]","[ATTENDANCE REPORT Query]","[Name]='TK' ")

    I've assumed you only have am and pm. Obviously you can restrict by date
    also.

    hth
    Chris
    Chris,

    the table design is actually:

    ID - Week Begining - Cycle No - TK AM - TK PM - nn AM - nn PM - etc

    I'm stuck with that layout, isn't there a way to make it work?

    Thanks

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Why not simply sum your first two expressions...
    Code:
    =DCount("[ID]","[ATTENDANCE REPORT Query]","[TK AM]='S' ")+DCount("[TK PM]","[ATTENDANCE Table]","[TK PM]='S' ")
    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.

    hth
    chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •