Results 1 to 4 of 4
  1. #1
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    114

    sum within query for only select records


    Attached is a table in which I loaded a small result set for ease. I want to further query to be able to tell how many months Terminated employees lost that were previously earning.
    I have concatenated the FY Year and FY Period and user created prompts.
    Table looks like this:
    FY Year FY Period FYPeriod Employee First Name End Date Months Earned Months Lost
    2021 1 20211 234 Bernie 12/21/2020 1 0
    2021 2 20212 234 Bernie 12/21/2020 1 0
    2021 3 20213 234 Bernie 12/21/2020 0 0
    2021 3 20213 173 Clifton 1 0
    2021 4 20214 173 Clifton 1 0



    For the 20213 Period I would like query to return a sum of the Months Lost for all the previous months where there is a Termination date populated and the FYPeriod is less than that prompted, so the result would look like this. I have tried and failed trying SUMIF, wondering if this would require a DSUM? I have no idea how to make this work.
    FY Year FY Period FYPeriod Employee First Name End Date Months Earned Months Lost
    2021 3 20213 234 Bernie 12/21/2020 0 2
    2021 3 20213 173 Clifton 1 0
    Attached Files Attached Files

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,908
    sumif is an excel function not used in access. try

    monthsLost: -sum([enddate] is null and monthsEarned<>0)

  3. #3
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    114
    I could not get that to work, the query prompts for FYPeriod so I would like 20213 to to show Bernie lost 2 from previous periods. If you wouldn't mind trying your solution in the attachment, I would appreciate.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,908
    try

    monthslost: nz((SELECT SUM([Months Earned]) FROM sheet1 T WHERE employee=sheet1.employee AND fyperiod<sheet1.fyperiod AND [End Date] is not null),0)

    however be aware your data is different to the example you posted and all text. And really does not go far enough to provide a proper solution

    For example what happens when you get to October? The FY code becomes 202110? If you keep them as text, then there will not be any months less

    ?"20219"<"202110"
    False

    And what happens if Bernie 'loses' in March per your example, earns in April and loses again in May? should that be 1 or 3.

    good luck with your project

Please reply to this thread with any new information or opinions.

Similar Threads

  1. using select query to add records
    By ntambomvu in forum Access
    Replies: 8
    Last Post: 03-11-2019, 11:25 PM
  2. Replies: 37
    Last Post: 03-10-2017, 04:57 AM
  3. Query to Select Records and Alphabetize them
    By MarcieFess in forum Queries
    Replies: 2
    Last Post: 10-24-2013, 09:34 AM
  4. SELECT TOP 10 Query returns 12 records
    By Paul H in forum Queries
    Replies: 8
    Last Post: 09-11-2013, 03:38 PM
  5. Replies: 3
    Last Post: 01-04-2011, 07:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums