Results 1 to 4 of 4
  1. #1
    Paul51480 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    2

    Return based on sum of points

    Hello all,
    This will be ,y first post here. I am relatively new to Access, I have the basics and will figure out whatever I have to. I am running Access 2010.
    I am trying to get a query to only return the entries that sum over 2 points;


    For instance;
    DB where entryID/ userID/ points
    DB Entries:
    1 ABC 1
    2 BCD 2
    3 CDE 1
    4 EFG 3
    5 ABC 2
    6 EFG 1
    7 FGH 1
    8 CDE 1
    9 CDE 1
    Would return;
    1 ABC 1
    5 ABC 2
    3 CDE 1
    8 CDE 1
    9 CDE 1
    4 EFG 3
    6 EFG 1
    So basically it is summing the total points for each user ID and the returning all entries for that user ID if the sum is over 2

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make query1 ,Q1, to sum the user IDs...
    ABC,3
    CDE,3

    then make Q2, that uses the data table joined to Q1,where Sum>2
    this query shows all the data needed.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    As ranman said, 2 queries(change Table1 to name of your table).

    Query1:
    SELECT Table1.UserID, Sum(Table1.Points) AS SumOfPoints
    FROM Table1
    GROUP BY Table1.UserID
    HAVING (((Sum(Table1.Points))>2));

    Query2 (this will return your results):
    SELECT Table1.EntryID, Table1.UserID, Table1.Points
    FROM Table1 INNER JOIN Query1 ON Table1.UserID = Query1.UserID;

  4. #4
    Paul51480 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    2
    Thank you both, works great.

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

Similar Threads

  1. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  2. Replies: 2
    Last Post: 03-07-2013, 03:14 PM
  3. Replies: 1
    Last Post: 01-16-2013, 06:48 PM
  4. Replies: 1
    Last Post: 01-16-2013, 04:41 PM
  5. Award Customer Points based on sum of Columns
    By JohnBoy in forum Programming
    Replies: 3
    Last Post: 02-20-2010, 02:26 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