Results 1 to 7 of 7
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Don't understand query error explanation



    Code:
    SELECT TDate, Description, Credit, SUM(Credit) FROM tblRegister  
    WHERE (TDate)>=#1/1/2021# And (TDate<=#12/31/2021#)
    GROUP BY Description  
    ORDER BY Description;
    Click image for larger version. 

Name:	000.jpg 
Views:	15 
Size:	24.3 KB 
ID:	47550

    Each record in tblRegister contain Description, TDate and Credit fields. I'm just tying to get totals relating to each Description in the table.
    Not being at all familiar with this type of query, I don't understand what the error message is trying to teach?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,987
    All fields except SUM(Credit) need to be grouped

    Code:
    SELECT TDate, Description, Credit, SUM(Credit) 
    FROM tblRegister  
    WHERE (TDate)>=#1/1/2021# And (TDate<=#12/31/2021#)
    GROUP BY TDate, Description, Credit 
    ORDER BY Description;
    If you had used the QBE, this would have been done automatically
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,953
    Every field in aggregate query SELECT clause not referenced in aggregate function calc must be included in GROUP BY clause. Remove TDate and Credit fields from SELECT clause.

    SELECT Description, Sum(Credit) AS Total
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Apparently, I've not described what I need clearly enough. For all records in tblRegister, total the amount of Credits for each Description group.
    Example:
    Description Total Credits
    Portfolio Div $6,998,435.00
    Portfolio Int $46,367.00
    USA Stimulus $12,000.00

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Code:
    SELECT tblRegister.Description, Sum(tblRegister.Credit) AS SumOfCredit
    FROM tblRegister
    WHERE (((tblRegister.[TDate])>=#1/1/2021# And (tblRegister.[TDate])<=#12/31/2021#))
    GROUP BY tblRegister.Description
    HAVING (((Sum(tblRegister.Credit)) Is Not Null))
    ORDER BY tblRegister.Description;
    That did the trick June7, thanks.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,953
    If you want to see raw data details as well as summary calcs, build a report and use its Sorting & Grouping features with aggregation in group section.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Yes, I remember doing that many years ago.
    Thanks again,
    Bill

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

Similar Threads

  1. Explanation On This Query Code
    By stildawn in forum Access
    Replies: 6
    Last Post: 03-15-2021, 01:27 AM
  2. Replies: 5
    Last Post: 10-05-2020, 03:50 AM
  3. Don't understand the error message
    By PATRICKPBME in forum Queries
    Replies: 3
    Last Post: 10-11-2019, 06:08 AM
  4. code error I don't understand why
    By LaughingBull in forum Access
    Replies: 5
    Last Post: 06-21-2017, 06:01 PM
  5. Replies: 5
    Last Post: 07-10-2013, 05:07 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