Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DSum (A2000 SR1)

    I'm trying to create a DSum to add from a Query
    The attached pic shows the Name, Production, Active.

    A member of staff can be active and none productive.ie admin staff
    A member of staff can be active and productive. ie workshop staff.
    If a member of staff is in-active he can still be productive, these are workshop staff which have left the company.
    If a member of staff is in-active and none-productive, these are admin staff which have left the company.

    My DSum statement is picking all records and adding them together which is defeating the object.

    =DSum("hrsAvail","qryStaff","production & active=true")

    I need the DSum of production ONLY if they are still active.

    Can some-one check my DSum statement.

    Thanks
    Dave
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum (A2000 SR1)

    Dave,

    Try replacing the condition by "production=True And active=True", so your expression becomes

    =DSum("hrsAvail","qryStaff","production=True And active=True")

    This will add hrsAvail only for those records that have production checked AND active checked.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum (A2000 SR1)

    Perfect.
    Thanks
    Dave

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum (A2000 SR1)

    Just one more thing Hans:
    Is it possible to add another field:

    =DSum("hrsAvail","qryStaff","production=True And active=True") and Workgroup=paint
    =DSum("hrsAvail","qryStaff","production=True And active=True") and Workgroup=body
    =DSum("hrsAvail","qryStaff","production=True And active=True") and Workgroup=fit

    Thanks
    Dave

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum (A2000 SR1)

    Dave,

    I don't see Workgroup in the picture of qryStaff in your first post in this thread. You'll have to add it to the query (unless it was present but hidden).

    And you'll have to put the extra condition within quotes, since it looks as if Workgroup is a text field:
    <pre>=DSum("hrsAvail","qryStaff","production=Tru e And active=True And Workgroup='paint'")</pre>

    Note that paint has been put in single quotes within the condition string in double quotes.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum (A2000 SR1)

    Yes Hans it was hidden, your DSum works ok now.
    I was confused using the single quotes.

    Regards
    Dave

Posting Permissions

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