MS ACcess 2003 Query Question (1 Viewer)

[MC1-1]

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

 

[PNGBill]

You could use one query to get all the records, group them on field 1 and get the first record (top?)
The 2nd query will then run through the records again and get all the records where where group 1 is equal to group 1 in the first query and group 2 is not equal to group 2.
ie, compare all records against the first record for the group.
Maybe a 3rd query will then select all the records where group 1 matches the result of query2.
This will ensure you get all the records as query 2 may not return the matching record.

Off the cuff idea.

 

[MStef]

Look at "DemoSelectQueryA2002-2003.mdb" (attachment, zip).
Open form and try. I think it is what you need.

 

[Lightwave]

I posted a reply but realised it was wrong so deleted it. I think the other posters have more valid points.