Results 1 to 10 of 10
  1. #1
    Join Date
    May 2020
    Posts
    26

    Unexplained result with simple query

    I have attached screenshots as it is easier to see.



    Created a simple query with one link with common field.

    Query runs no problem.

    Then select Group by and sum one field and count another.

    The result is startling!
    Attached Files Attached Files

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    When you see 'Chinese' characters, it is usually a sign of data corruption
    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
    Join Date
    May 2020
    Posts
    26
    Any idea on how I can pin down which fields are corrupted

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Presumably the one with the Chinese characters.
    You may find its only one or two records. Check that field in all records in the original query and also in the table.
    Do the number of records tally?

    Corruption can be hard to pin down / fix.
    If that doesn't help, make a backup then do a compact/repair.
    If still not OK, try importing all objects into a new database.

    In fact I would do a backup anyway as your database may be about to go 'belly up'.
    Good luck
    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

  5. #5
    Join Date
    May 2020
    Posts
    26
    Hi,
    That's one of the odd things. There is nothing with Chinese Characters!
    Yes backup and reload the tables one at a time sounds like a good approach.

    Still very odd!!

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I suggested checking the number of records in table and query as occasionally corrupted records do not display.
    As I said before, corruption can be hard to pin down. And if you do find corrupted records you may not be able to edit or delete them
    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

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Grouping on a Long Text field can cause this. Can't tell what data type your description field is.
    Maybe next time just post pics rather than a pdf that has to be downloaded and then unzipped?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    May 2020
    Posts
    26
    I tried to post pics but it was rejected as too many letters..tried to attach pdf and the file was too large.
    Description is Long Text

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @Micron is correct. I forgot about the issues with Long Text/Memo fields.

    Do you have any records where the Description field is more than 255 characters?
    If not the Long Text datatype is unnecessary and can indeed cause problems.
    Change it to Short Text then try your aggregate query again.

    However if you do have records which require the use of Long Text, change the Totals row of the Description field from Group By to First.
    OR replace the Description field in the query with Left(Description, 255)
    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

  10. #10
    Join Date
    May 2020
    Posts
    26
    Yep that was it. Changed to short text and no more Chinese characters!
    Thanks

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

Similar Threads

  1. Replies: 1
    Last Post: 09-14-2017, 10:19 AM
  2. simple query but result is difficult to analyze.
    By narendertangutooei in forum Access
    Replies: 7
    Last Post: 09-13-2017, 02:09 PM
  3. Replies: 7
    Last Post: 01-11-2017, 03:27 PM
  4. Simple Line Chart field result over time
    By illicited in forum Reports
    Replies: 4
    Last Post: 01-23-2015, 05:42 PM
  5. Export a simple query result to an excel file
    By wop_nuno in forum Import/Export Data
    Replies: 1
    Last Post: 05-21-2009, 04:18 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