Results 1 to 3 of 3
  1. #1
    slg9303 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Location
    Los Angeles, CA
    Posts
    2

    In one-to-many relationship, count unique combinations of "many" records related to each "one"

    There is a "Project" and a "Measure" table. One or more measures may be related to each project. I want to know all the unique combination of measures related to projects, and how many projects have that unique combination. Then I want to be able to sort the combinations from most to least common.



    So, let's say there are six possible measures: A, B, C, D, E, and F. One project may have just measure "A". Another project may have measure "A" and "C". Another project may have measure "B", "D", and "F". I'd like to know all the combinations of measures that exist for the projects. So, for example, there are 10 projects that have the combination "A", "D", and "F". There are five projects that have "C" and "F".

    I have a feeling a nested query may be the solution. Any help is appreciated! Thanks.

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,994
    you will need to get your data into the form

    project...measures
    1...........A
    2...........AC
    3...........BDF

    on which you can do a simple group by and count

    how you get the data into that form - perhaps use the concatrelated function, or perhaps a crosstab query to get you

    project..measureA..measureB..measureC....
    1..........1
    2..........1..............................1
    3.........................1....................... .......

    and then another query to concat the measure columns together, substituting 1 for A, B, C etc depending on the column or replace the nulls with 0 so you get

    project...measures
    1...........100000
    2...........101000
    3...........010101

    whichever way you go, it is important that the order of measures is consistent

  3. #3
    slg9303 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Location
    Los Angeles, CA
    Posts
    2
    Thank you this was very helpful!

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

Similar Threads

  1. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 1
    Last Post: 09-03-2014, 03:27 AM
  4. Replies: 4
    Last Post: 08-14-2012, 11:56 AM
  5. "Count" and "Countif" in Reports
    By JMantei in forum Reports
    Replies: 1
    Last Post: 06-20-2006, 02:20 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
  •  
Tech Forums: Microsoft Office Forums