Results 1 to 4 of 4
  1. #1
    fauowls is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    5

    Finding all possible combinations

    I have a table with the following data (example):
    Chris Florida 1
    Jamie Texas 1
    Samantha Georgia 1
    John Maine 1
    Jim Wisconsin 1
    Steve Nebraska 2
    Melanie Florida 2
    Hector California 2
    Susanna Oregon 3
    David Kentucky 3



    What I am trying to do is create a table that finds all possible combinations based on the digits in the last column. It should find all possible combinations NOT based on the same number and put them next to each other. Example:

    Chris Florida 1 2 Nebraska Steve
    Chris Florida 1 2 Florida Melanie
    Chris Florida 1 2 California Hector
    Chris Florida 1 3 Oregon Susanna
    Chris Florida 1 3 Kentucky David
    Jamie Texas 1 2 Nebraska Steve
    Jamie Texas 1 2 Florida Melanie
    Jamie Texas 1 2 California Hector
    Jamie Texas 1 3 Oregon Susanna
    Jamie Texas 1 3 Kentucky David


    And so on until I have all the combinations.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Research Cartesian Join or Cartesian Product

    Tell us your table and field names.

  3. #3
    fauowls is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    5
    Quote Originally Posted by orange View Post
    Research Cartesian Join or Cartesian Product

    Tell us your table and field names.
    It would be one table. We can use the first table above, field name would be "name", "state" and "ID". Based on that one table I would need to get an output that looks like the second table.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    create 3 queries

    1. Select all the names call it qNames
    2 Select all the states call it qStates
    3 select all the IDs call it qIds

    Then

    Select qNames.Name
    ,qStates.State
    ,qIds.Id FROM
    qNames,qStates,qIDs
    Order By qNames.Name, qStates.State,qIDs.ID

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

Similar Threads

  1. How to search for duplicate combinations
    By EvanRosenlieb in forum Access
    Replies: 5
    Last Post: 04-17-2012, 02:56 PM
  2. Calculating all possible combinations
    By Data in forum Queries
    Replies: 3
    Last Post: 06-20-2011, 03:02 PM
  3. Need a query to figure all possible combinations
    By julestrip in forum Queries
    Replies: 1
    Last Post: 05-27-2011, 07:23 AM
  4. Query for All Possible Combinations
    By Rawb in forum Queries
    Replies: 1
    Last Post: 10-09-2010, 08:33 PM
  5. ms access version combinations
    By marianne in forum Access
    Replies: 1
    Last Post: 08-05-2009, 07:37 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