Results 1 to 3 of 3
  1. #1
    crispy-bacon is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    13

    Distinct for one Column

    Hi so my problem is as follows, I have a query that uses multiple related tables to output a table along the lines of the following:

    Code:
    |Proposal|          Proponent|     info1|      info2|    etc.
      |1 |                              Bill |                        ... |             ...
      |1 |                              Steve |               ... |          ...
      |2 |                             Mary |                   ... |             ...
    The problem I have is each proposal can have multiple proponents and I would simply like one record to be shown for each proposal (they are partial matches so distinct row and distinct do not work). So I've searched the forum and I've seen a few solutions.

    First one used the Partition By clause to partition the table but I am using Access 2003 so that is not an option. The other solutions I have seen have mostly been for values from the same table using Group By. I figured there is no reason as to why it shouldn't work in my situation but I'm having trouble getting it to work (I am new to access/sql/relational databases).

    If more details are required I can post them later as I am not currently on the machine with the database but any help with aggregate functions or Group By or another work around would be appreciated.



    Thanks

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

  3. #3
    crispy-bacon is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    13
    Thanks for the response it did help figure out Group By. I managed to get it to work how i would like, I didn't realize every field had to be part of an aggregate funtion so I put all the info1, info2... fields in a MIN() since they are all equivalent for each individual proposal anyways and that did the trick.

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

Similar Threads

  1. Need help for distinct...
    By gunapriyan in forum Queries
    Replies: 2
    Last Post: 05-28-2010, 12:18 AM
  2. Replies: 1
    Last Post: 04-15-2010, 02:07 AM
  3. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  4. Distinct Values
    By Acramer8 in forum Reports
    Replies: 1
    Last Post: 06-15-2009, 08:37 AM
  5. Counting distinct id's
    By jqljql in forum Access
    Replies: 1
    Last Post: 09-01-2006, 07:28 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