Results 1 to 6 of 6
  1. #1
    MikeNZ is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    7

    Query to combine(?) records


    May sound like a really dumb question, but please remember I am NEW to Access and databases in general....but....is there a way to run a query that will combine multiple records for the same person? I have a table that shows name and number of wins(fig 1) but after the query I would like it to show as in fig 2. Can this be done and if so how? If not then guess I'll have to go continue to use an excel sheet.

    Fig 1 Fig 2
    John 2 John 4
    Bill 4 Bill 4
    Bob 2 Bob 3
    John 1 Kate 3
    John 1
    Kate 3
    Bob 1

    As always, all help, suggestions, advice is appreciated.

    Thanks

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Here is the SQL statement for that:

    SELECT Sheet1.ID, Sum(Sheet1.Wins) AS SumOfWins
    FROM Sheet1
    GROUP BY Sheet1.ID;

  3. #3
    MikeNZ is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    7
    Thanks, It works!!

    But now how would I adjust it to pull fields from another table? Eg: Want to number of games played from the GamesPlayed field in table2

    I tried to just add the table and field but it didn't work

    SELECT (Table1.FirstName, sum(Table1.Wins) AS SumOfWins),Table2.GamesPlayed
    FROM Table1,Table2
    GROUP BY Table1.FirstName;
    Last edited by MikeNZ; 07-27-2016 at 06:42 AM.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Mike,

    This might be useful to you.

    Also, I suggest you work through this tutorial from RogersAccessLibrary

    and for general tips on Database and Access see this info from Gina


    Good luck.

  5. #5
    MikeNZ is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    7
    Wow...brilliant. Thanks Orange, looks like I have some reading to do.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Replies: 1
    Last Post: 04-06-2016, 09:26 AM
  2. Replies: 8
    Last Post: 07-11-2014, 12:36 PM
  3. Replies: 3
    Last Post: 05-29-2014, 09:50 AM
  4. Replies: 2
    Last Post: 10-02-2012, 03:18 PM
  5. Combine master detail records in one query
    By mariost in forum Queries
    Replies: 4
    Last Post: 05-14-2011, 11:25 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