Difficult to come up with a title that makes sense for this one. I'm sure there is an obvious and simple solution to this query, but it's evading my tiny brain...
This query is part of some error trapping of a more complicated piece, but essentially what I am trying to get to is to identify where a field has differing values to others that it is grouped by. To give an example, a table with the following three fields:
Autonumber ID
Field A: an Integer which is a lookup reference to another table
Field B: an Integer with a value of 1, 2 or 3
Field A can be the same value for multiple ID's in the table, Field B should be the same value for every instance of whatever Field A is set to. So where ID's 1, 4, and 6 have the value of Field A set to 10, their Field B value should all be the same.
So if my table looks like
I would like the query to just return ID's 1, 4 and 6 because they don't all have the same value in B, but not ID's 3 and 7 because they are both set to the same value ("1").
This query is part of some error trapping of a more complicated piece, but essentially what I am trying to get to is to identify where a field has differing values to others that it is grouped by. To give an example, a table with the following three fields:
Autonumber ID
Field A: an Integer which is a lookup reference to another table
Field B: an Integer with a value of 1, 2 or 3
Field A can be the same value for multiple ID's in the table, Field B should be the same value for every instance of whatever Field A is set to. So where ID's 1, 4, and 6 have the value of Field A set to 10, their Field B value should all be the same.
So if my table looks like
ID | Field A | Field B |
1 | 10 | 1 |
2 | 14 | 1 |
3 | 12 | 1 |
4 | 10 | 2 |
5 | 11 | 1 |
6 | 10 | 2 |
7 | 12 | 1 |
I would like the query to just return ID's 1, 4 and 6 because they don't all have the same value in B, but not ID's 3 and 7 because they are both set to the same value ("1").