Re: Finding records with equal values in 3 fields

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi!

Thank you Marshall, you solution works great.

What I'm looking for is a bit more complicated: I want the program to help
me identify WHICH FIELDS has duplicated values, and display the records
which has these duplicated values. I want to distinguish between different
"cases". I am willing for "answers" like:

* Records which have same values in field1:
Records 1, 2, 5, Field1: 0
Records 3, 4, 6, Field1: 1
* Records which have same values in field2:
Records 1 , 4. Field1: 1
Records 2,3,5,6 Field1: 0
....
(and for the more complicated cases:)
* Records which have same values in fields 1, 3 and 4:
Records 1 , 2. Field1: 0, Field 3: 0, Field 4: 0.
Records 4 , 6. Field1: 1, Field 3: 1, Field 4: 0. (note that different
fields do NOT have to be equal. The condition is that values in the SAME
FIELD but from DIFFERENT RECORDS will be equal, but this doesn't mean that
all the fields that are checked should be equal, as in this example, for 1,
1, 0)

* Records which has same values in fields 1,2,3,4 (This line will not be
displayed because there are no such records. Again, note that Record 2 has
0's in all of the fields, but that's not what the program should look for,
but for different records which has same values in the fields, so it should
check for each field seperately)

etc.

I think can solve this quite easily by using Excel: If I export all the
records to Excel, all I have to do is to run the following procedure for
each COLUMN (field):
- store the value of the first row (record) in a variable
- check for the values in the rest of the rows in the current column. If one
of the values is different from the value in the variable, delete the
current column and move to the next one.

This will give me all the fields which has same values for ALL the records,
which is not such bad, but not as good as what i've described previously.


I would be grateful if you help me solve this puzzle.

Kind Regards,
Amir.

"Marshall Barton" <marshbarton@xxxxxxxxxx> wrote in message
news:2nka719mr4of384cspslevj5q83ftla2lo@xxxxxxxxxx
> Amir wrote:
>>I'm looking for a way to find all the records in a table which has equal
>>values in 3 specific fields.
>>
>>Lets say I have a table named tblMyTable, which has 4 fields: Field1,
>>Field2, Field3, Field4.
>>
>>What I wish to do is to build SQL query that will select only records
>>which
>>has other records that has same values in the fields Field1, Field3,
>>Field4.
>>
>>for example, If my records' values are:
>>Field: 1 2 3 4
>>Record1:0 1 0 0
>>Record2:0 0 0 0
>>Record3:1 0 0 0
>>Record4:1 1 1 0
>>Record5:0 0 1 0
>>Record6:1 0 1 0
>>
>>Then the query will select only the following records:
>>Record1, Record2 (Both have 0 _ 0 0)
>>Record4,Record6 (Both have 1 _ 1 0)
>>
>>I think this can be done by grouping by Field1, then by Field3, then by
>>Field4, then selecting only the groups which has a total of more than 1
>>record in it. I think this has to do something with the HAVING clause but
>>I'm just not that good with SQL,
>
>
> You are on the right track Amir.
>
> qryGetCounts:
> SELECT Field1, Field3, Field4, Count(*) As DupCnt
> FROM tblMyTable
> GROUP BY Field1, Field3, Field4
> HAVING Count(*) > 1
>
> qryGetDups:
> SELECT T.keyfield, T.Field1, T.Field3, T.Field4, C.DupCnt
> FROM tblMyTable INNER JOIN qryGetCounts AS C
> ON T.Field1 = C.Field1
> AND T.Field3 = C.Field3
> AND T.Field4 = .CField4
>
> Since records don't have a record number, I used a
> hypothetical primary key field.
>
> --
> Marsh
> MVP [MS Access]
>


.

Relevant Pages

  • Re: Finding records with equal values in 3 fields
    ... >What I wish to do is to build SQL query that will select only records which ... >I think this can be done by grouping by Field1, then by Field3, then by ... You are on the right track Amir. ... FROM tblMyTable INNER JOIN qryGetCounts AS C ...
    (microsoft.public.access.queries)
  • Re: Finding records with equal values in 3 fields
    ... it's way beyond my SQL ... >>>has other records that has same values in the fields Field1, Field3, ... >> FROM tblMyTable INNER JOIN qryGetCounts AS C ...
    (microsoft.public.access.queries)
  • 3 csv file to one & linking to find unique rows
    ... Field1 = Year ... Field2 = Location ... Field3 = Dept ... Field4 = Month ...
    (microsoft.public.excel.programming)
  • Re: IF then
    ... Both situations rely on the value of Field1, ... Field3 = False ... Field4 = False ... Candia Computer Consulting. ...
    (microsoft.public.access.formscoding)
  • Finding records with equal values in 3 fields
    ... What I wish to do is to build SQL query that will select only records which ... I think this can be done by grouping by Field1, then by Field3, then by ... Kind Regards, ... Amir. ...
    (microsoft.public.access.queries)