Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dup Query of two columns not rows (2000)

    I have a large amount of data (20K+ rows), 6 columns wide. I need to create a query that will return only the rows when the values in two of the columns are duplicate, e.g. the values the second and sixth column both equal AB12345. The regular duplicate query wizard doesn't seem to take this type of query in to account (only duplicate rows) and I'm no sure how to write it.

    Any help would be appreciated.

    Thanks,
    Scott

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dup Query of two columns not rows (2000)

    Do you mean that you want to return rows where ANY two of the six fields have the same value? You could do it as described below, but the query will probably be very slow:
    Create a query based on the table.
    Add the six fields to the query grid.
    Set the criteria as follows, substituting the correct field names:

    <table border=1><td>Field:</td><td>Field1</td><td>Field2</td><td>Field3</td><td>Field4</td><td>Field5</td><td>Field6</td><tr><td>...</td><td align=right>

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dup Query of two columns not rows (2000)

    Hans,

    Thanks for the info. Actually, only columns 2 and 6 would have a duplicate value, basically a user id; the other 4 columns is just data related to the transaction. For access management purposes, we're tyring to identify which transactions were both created and verified by the same user id.

    Thanks for you help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dup Query of two columns not rows (2000)

    In that case you only need to put [Field6] in the criteria line under Field2 (with the appropriate names substituted).

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dup Query of two columns not rows (2000)

    Thanks Hans, that worked great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •