Results 1 to 5 of 5
  1. #1
    bengineer is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    3

    Grouping dual keys in Access 2007 Report

    Hello

    I am trying to make a report where two of the fields can have simultaneous multiple values. E.g. a study can be sponsored by one or more companies.

    I'm trying to have my report list these multiple values. Instead, a new line in the report, with all the data, is created.

    Here's what I'm trying to see:


    StudyID Sponsor Start End Measurements
    113 NHL Jan 2007 Oct 2008 Customer Satisfaction
    NBA ROI
    Ticket sales

    But this is what I'm getting:
    Hello

    I am trying to make a report where two of the fields can have simultaneous multiple values. E.g. a study can be sponsored by one or more companies.

    I'm trying to have my report list these multiple values. Instead, a new line in the report, with all the data, is created.

    Here's what I'm trying to see:
    StudyID Sponsor Start End Measurements
    113 NHL Jan 2007 Oct 2008 Customer Satisfaction
    113 NHL Jan 2007 Oct 2008 ROI
    113 NHL Jan 2007 Oct 2008 Ticket sales

    If anyone knows how to fix this, please let me know!

    Thanks in advance.

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    you need to group on a value that is common to both; even if this value is not visible.

    hope this helps.

  3. #3
    bengineer is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    3
    Thanks for the reply. As I re-read my post, I realize that it doesn't make as much sense as it should. I've attached a screenshot for clarity. The study ID is used across tables as part of the dual-key relationship.

    I'm hoping that the report can be generated without duplicating the data.

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    It isn't duplicating records.

    Look at that far right column; the value in that differs.... that is enough to make the overall record differ and be not duplicate.

    So, ....if you need that info - then you need both records...

    Otherwise you can remove that from the underlying record source and get a different set of records with no duplicates by modifying the query to not show duplicates.

    Hope this helps.

  5. #5
    bengineer is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    3
    What I'm trying to do is have only the data in the far-right column and the sponsor column printed on the additional lines. I don't want everything else to be reprinted. The other issue, as can be seen in the screen shot, for the same study, each outcome measure is reprinted for each sponsor, rather than showing each item once.

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

Similar Threads

  1. report grouping and sort
    By Meccer in forum Access
    Replies: 1
    Last Post: 01-05-2011, 08:30 AM
  2. Access report with 3 tables -grouping,sorting
    By kartechy in forum Reports
    Replies: 0
    Last Post: 07-22-2009, 09:07 AM
  3. Replies: 1
    Last Post: 05-21-2009, 08:13 AM
  4. Grouping data in Report
    By Leelers in forum Reports
    Replies: 1
    Last Post: 02-20-2009, 08:49 AM
  5. Report settings at runtime in Access 2007
    By soringc in forum Programming
    Replies: 0
    Last Post: 11-22-2007, 10:04 AM

Tags for this Thread

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 - Senior Forums