Results 1 to 4 of 4
  1. #1
    mkelly1082 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    2

    Sum Values for Rows based on certain criteria

    Hey everyone. Trying to solve an issue regarding multiple records for Sales agents. We pull our daily sales data from one data source (includes agent name, supervisor name, manager name, etc,) into an Access table. Due to occasional roster changes in the original data source, we sometimes have agent data that gets split between multiple supervisors and managers. This results in more than one row for the agent for a single day, when one row is preferred.

    Example:

    Date Manager Supervisor Agent Metric 1 Metric 2 Metric 3
    3/22/2018 Manager A Supervisor A Billy Bob 19 4 3
    3/22/2018 Manager B Supervisor B Billy Bob 2 1 8


    Ideally, I'd like the end result to be just one row:

    Date Manager Supervisor Agent Metric 1 Metric 2 Metric 3
    3/22/2018 Manager A Supervisor A Billy Bob 21 5 11


    I'd want to make this change to the existing table. Any help would be appreciated. Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    remove SUPERVISOR from the query. Otherwise you get Ex.1.

  3. #3
    mkelly1082 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2019
    Posts
    2
    Quote Originally Posted by ranman256 View Post
    remove SUPERVISOR from the query. Otherwise you get Ex.1.

    How would I get the existing table to keep the Manager A/Supervisor A info?

    I've found how I can sum the existing data by removing the Manager/Supervisor columns, but amending those to the table just adds the additional record, with blanks for Manager/Supervisor.

    Thanks!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    what dictates that the right association is A - A - 21 instead of B - B - 21 ?
    If you know that, then you might be able to join this query to the underlying table and use the answer to that question as a rule/join or criteria in another query. It may take more than 1 additional table along with this query - impossible to say without understanding the relationships. Based on your last comment, you could also consider following up your append with an update, based on that same rule. Sometimes you just have to do things in multiple steps.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 7
    Last Post: 05-25-2018, 05:56 PM
  2. Conditional Import - skip rows based on criteria
    By RealmOfConfusion in forum Import/Export Data
    Replies: 3
    Last Post: 04-22-2015, 09:05 AM
  3. Filtering query based on multiple rows values
    By GeorgeB in forum Queries
    Replies: 6
    Last Post: 12-05-2013, 06:17 PM
  4. Replies: 1
    Last Post: 10-29-2012, 08:15 AM
  5. Replies: 2
    Last Post: 08-28-2011, 06:06 AM

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