|
I am trying to design a query to identify instances when, for every instance of the data in Field1, the data in Field2 field varies. Both fields can contain duplicated data. For example with the following data:
Data_Ref......Field1.......Field 2
12345.............1.............1
45725.............1.............2
15252.............1.............2
25128.............2.............1
25684.............2.............2
10528.............2.............1
...
45635.............31.............2
56745.............31.............2
67856.............31.............2
25125.............35.............1
34562.............35.............1
56453.............35.............2
56746.............35.............3
Data_Ref field is a primary field - no duplicates.
I would like the query to return the records containing 1,2 and 35 in "Field1" but not 31 (because for all values of 31 in "Field1", the values in "Field2" are identical).
I require a report of all the records where "Field1" has varying "Field2"'s and ideally the report would give all the records where this occurs - i.e the report would produce 10 records in my example (total 13 records less the 3 records with 31 in "Field1")
In other words, for every numeric value in the field "Field1", I would like to test to see that all the values in the field "Field2" are the same. If they are then I don't want the query to return any of those records but if they are not then I want the query to return all of those records. For example if there are say 8 records with the value 22 in Field1 and all of them have a value of 1 in Field2 then I don't want the query to return any of these 8 records. On the other hand if any one (or more) of these 8 records had a different value in Field2 then I would like the query to return all 8 records.
I would be very grateful for any pointers as to how I could do this as I have thousands of records in a table to run this on.
Thank you in advance
|
