Results 1 to 6 of 6
  1. #1
    jwang1228 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    3

    Query To Sum a Field When 2 Other Fields are Similar

    Hi,

    I rarely use Access and have imported a file that I need to condense. It is for payroll purposes and has multiple fields but the 3 I am focused on are:



    Employee
    Paytype
    Pay

    I want to Sum up the Pay of all lines where Employee and Paytype combination are similar.
    The data I have now have multiple lines where the Employee field and Paytype field are similar and I want to combine them into one row.

    Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean by 'similar'? Can do an aggregate query that groups data by employee and paytype and sums the pay.

    Or build a report using Grouping & Sorting features with aggregate calcs in group section. This allows display of detail records and summary calcs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jwang1228 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    3
    The aggregate query sounds exactly like what I need to do.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use the query builder. Basic Access functionality.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jwang1228 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    3
    I know how to do that but I've been tinkering with it for quite some time now and can't figure out the settings. All the fields have to be Group By so I don't know how to define that Employee and Paytype are the parameters of summing Pay.

    I need all the fields to show so I don't know how to omit them in the Total settings.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Options:

    1. build report

    2. build the aggregate query then in another query join the aggregate query to the table by compound linking on Employee and Paytype fields

    3. DSum() expressions - however, domain aggregate functions can be slow performers
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 7
    Last Post: 06-19-2014, 12:00 PM
  2. How to Join Similar Fields Based on a Third Field?
    By Inaccessable in forum Queries
    Replies: 1
    Last Post: 07-12-2013, 03:56 PM
  3. Relating similar fields???
    By smoothlarryhughes in forum Access
    Replies: 10
    Last Post: 02-26-2013, 12:50 PM
  4. Replies: 3
    Last Post: 02-24-2012, 06:20 PM
  5. Replies: 1
    Last Post: 07-07-2009, 01:00 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