Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2020
    Posts
    1

    Multiple Sum Criteria

    I hope someone will be able to help me with this (probably simple) question.

    I have a table with four columns, 'Date', 'Deal ID', 'Deal Type' and 'Units'.

    I'd like to create a query with two columns. The first column should be the sum of the whole 'Units' column. The second column should be the sum of 'Units' where 'Deal Type' equals 'Buy' or 'Transfer In'.

    Is it possible to set this up as a single query using the Query Design tool or do I need to get into SQL?

    Although I have a good deal of experience in Excel - including VBA - I'm new to Access and have not yet learned SQL.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Try setting up a select query with your criteria then click the Totals button on the ribbon. The view will change slightly. You should be able to figure it out, even if it takes a bit of playing, but that's how you learn. Depending on what field(s) you choose to Group By, you may end up with identical sum values in every record but that's how it works. You may have to removed fields from the Grouping. If you need them later, often the solution is to create a totals query then query that query.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In addition to Micron's advice, here is some more general info re Access.

    Using Names for tables, fields and objects that do not have embedded spaces will simplify some programming issues at some point. Better to use names with no embedded spaces nor special characters such as #,%.$ etc.

    There are examples of SQL statements and test areas at w3schools

    Good luck with your project.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-02-2015, 12:45 PM
  2. Replies: 5
    Last Post: 03-22-2015, 08:43 PM
  3. Replies: 2
    Last Post: 04-10-2013, 03:54 PM
  4. Replies: 7
    Last Post: 01-02-2013, 11:23 PM
  5. Replies: 1
    Last Post: 07-13-2011, 11:00 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