Results 1 to 5 of 5
  1. #1
    tracyd2475 is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2014
    Posts
    2

    Eliminating duplicate rows in a query result based on one column


    Hi,
    I have a query based on 2 tables, joined on Memberid, the result showing :

    Table1 Table1 Table1 Table2 Table 2 Table2
    Category Association Memberid CustomerName E-mailAddress MemberID

    Board Member(Lookup,integer) SAMGA(Lookup,integer) 44 Smith smith@abc.co.za 44
    Board Member ADHTY 44 Smith smith@abc.co.za 44
    Grower SAMGA 44 Smith smith@abc.co.za 44

    I only want to show 1 row, based on the duplications of E-mail address.
    I know i should be using the row_number function, but cannot get to the result I want.
    Does anyone please have a solution?

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,900
    What does the data in each table look like before the query?
    Could you post some samples, and the SQL code of your query?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    if you are joining these two tables, how are you determining which is the correct data from table1? Your email information is coming from Table2. Your tables have a one to many relationship (One record in Table2 to MANY records in Table1) so you have to find a way to limit what you're going to show in your query if you only one one record per customername/email address.

  4. #4
    tracyd2475 is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2014
    Posts
    2
    Quote Originally Posted by JoeM View Post
    What does the data in each table look like before the query?
    Could you post some samples, and the SQL code of your query?

    Thanks so much for your response JoeM.
    I have worked out a way to solve the problem using VB and can finally proceed!

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,900
    That's good, though VB may not be necessary. You should be able to do what you want with an Aggregate and/or Nested Query or two.
    If you would like a non-VBA solution, just let me know the details I asked for.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

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

Similar Threads

  1. Replies: 3
    Last Post: 07-14-2013, 08:24 PM
  2. Replies: 2
    Last Post: 06-24-2013, 12:37 PM
  3. Replies: 3
    Last Post: 09-26-2012, 01:39 PM
  4. Replies: 4
    Last Post: 05-21-2012, 04:09 PM
  5. Replies: 1
    Last Post: 12-03-2011, 01:26 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 - Senior Forums