Results 1 to 4 of 4
  1. #1
    UTLee is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    73

    Basic Query Help

    In Access, I've created the following query using design view:


    Code:
    SELECT tbltempPositions.client, Sum(tbltempPositions.amount) AS SumOfamount
    FROM tbltempPositions INNER JOIN tblClients ON tbltempPositions.client = tblClients.client
    GROUP BY tbltempPositions.client, tblClients.Manager, tbltempPositions.security
    HAVING (((tblClients.Manager)="ll") AND ((tbltempPositions.security)="abc" Or (tbltempPositions.security)="def" Or (tbltempPositions.security)="ghi"));
    I'm trying to sum each client's total value of all 3 of the securities - abc, def, ghi.

    The current query shows me each client that has these 3 securities and their individual amounts, so if the client has all 3 securities it shows me 3 records for that client. I want to sum the amount of all 3 of the securities and have 1 record for each client showing that total summed value only. I know I can make a query based on this query to do this but I was wondering if there was a way to avoid that.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have security in the GROUP BY clause, so it's doing exactly what you asked. From the look of it, you unchecked the Show box in design view, but it still says Group By. Change that to Where.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    UTLee is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    73
    Ahhh! Exactly right, that got it. Thanks for your help - My Access background is very limited.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Basic query to vba translations
    By Lewis in forum Programming
    Replies: 2
    Last Post: 05-08-2015, 03:32 AM
  2. Query Help VERY BASIC
    By nparrillo in forum Queries
    Replies: 5
    Last Post: 04-06-2011, 11:15 AM
  3. Basic Query/totaling fields in a query
    By afisher in forum Queries
    Replies: 6
    Last Post: 08-05-2010, 01:43 PM
  4. Query in visual basic
    By Lucas83 in forum Programming
    Replies: 1
    Last Post: 06-10-2010, 11:00 AM
  5. basic query codes
    By joms222 in forum Queries
    Replies: 1
    Last Post: 03-20-2009, 11:31 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