Results 1 to 11 of 11
  1. #1
    rsmith is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    5

    Custom report does not show all FNAMEs if LNAMEs are the same


    I have inherited a problem report. The query runs and shows all information correctly. A report created with the wizard does too. The problem is with a custom report that was created before my time. This report is supposed to show information from a defined time period for each person separately. It should be sorted by last name, first name. The information includes the date of the activity, travel time, mileage, support time, other & team. Then there are totals for that individual before going to the next person.



    The problem with this particular report is that if there is more than one person with the same last name, only the first person with that name is included in the report.
    How do I get this report to include all persons with the same last name?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It's not clear if the query that runs OK is the source for the problem report. Assuming it is not, then if the report is based on a query, open that and verify that it is even returning the records as you expect. If not, that is where you need to start fixing things. If it does return all the correct records, then look at the report design. It may be a group by property is incorrect (grouping by last name, in which case there may or may not be records for 2 different first names) or the query is missing a PK field. If the PK field is LastName, that is bad design. If the PK field was an autonumber (included in the query) then each person would have a unique ID regardless of last name, and the report could then group by PK ID.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    In reports there is a setting in the properties to hide duplicates. What is that set to? Is it hiding duplicate last names?

  4. #4
    rsmith is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    5

    Post

    Quote Originally Posted by Micron View Post
    It's not clear if the query that runs OK is the source for the problem report. Assuming it is not, then if the report is based on a query, open that and verify that it is even returning the records as you expect. If not, that is where you need to start fixing things. If it does return all the correct records, then look at the report design. It may be a group by property is incorrect (grouping by last name, in which case there may or may not be records for 2 different first names) or the query is missing a PK field. If the PK field is LastName, that is bad design. If the PK field was an autonumber (included in the query) then each person would have a unique ID regardless of last name, and the report could then group by PK ID.

    The query itself does return what I would expect. Could you tell me more about Grouping in the report? I took a look at the table the query is pulling from and I do not see any PK defined.

  5. #5
    rsmith is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    5
    Could you tell me where to look for this property?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Click image for larger version. 

Name:	PKView1.png 
Views:	18 
Size:	53.9 KB 
ID:	42584
    Does this help?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Could you tell me more about Grouping in the report?
    I suppose I could, but if you have access to a forum like this, you have the ability to search on something that's suggested to you? You'll learn more, but aside from that, a lot of topics that relate to a suggestion like this will be fairly in depth and there's no point in me simply repeating what is covered in a million or so other web pages that who knows, some of which you might even want to bookmark.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I expect you can find numerous online tutorials on report design using Grouping & Sorting. Or get a basic tutorial book that should have a whole chapter on the topic.
    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.

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by rsmith View Post
    Could you tell me where to look for this property?
    in the property sheet, format column

    Click image for larger version. 

Name:	Annotation 2020-08-06 212517.png 
Views:	15 
Size:	9.4 KB 
ID:	42585

  10. #10
    rsmith is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    5
    Quote Originally Posted by ssanfu View Post
    Click image for larger version. 

Name:	PKView1.png 
Views:	18 
Size:	53.9 KB 
ID:	42584
    Does this help?

    Yes it does. Whoever created this originally did not used a PK. I may try adding one to see if it helps. Thank you.

  11. #11
    rsmith is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    5
    Quote Originally Posted by moke123 View Post
    in the property sheet, format column

    Click image for larger version. 

Name:	Annotation 2020-08-06 212517.png 
Views:	15 
Size:	9.4 KB 
ID:	42585
    Thanks moke123. I'll see if I can find anything like that.

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

Similar Threads

  1. Custom grouping report
    By mmiklauz in forum Reports
    Replies: 5
    Last Post: 11-14-2018, 08:40 AM
  2. Replies: 5
    Last Post: 02-24-2016, 10:58 PM
  3. Custom Report Generation
    By jmitchelldueck in forum Reports
    Replies: 1
    Last Post: 08-24-2015, 11:09 AM
  4. Problem with EOF in custom report
    By ino_mart in forum Programming
    Replies: 4
    Last Post: 09-27-2011, 02:56 PM
  5. Custom Report Printing
    By robocopfl in forum Reports
    Replies: 3
    Last Post: 06-04-2010, 12:23 PM

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