Select difference in field value "A" when grouped by field value "B" (1 Viewer)

[dobseh]

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

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").

 

[plog]

Sounds like a job for a sub query:

SELECT A AS GoodA
FROM YourTableName
GROUP BY A
HAVING MIN(B) = MAX(B)

That's going to produce all the valid A values you want. If the maximum B for an A equals the minimum B for an A, that means all the B's are the same.

You can then take that query and bring it into another query with YourTableName and bring in the entire record from YourTableName for just the ones you want by joining the two datasources on A=GoodA.

 

[dobseh]

Sounds like a job for a sub query:

SELECT A AS GoodA
FROM YourTableName
GROUP BY A
HAVING MIN(B) = MAX(B)

That's going to produce all the valid A values you want. If the maximum B for an A equals the minimum B for an A, that means all the B's are the same.

You can then take that query and bring it into another query with YourTableName and bring in the entire record from YourTableName for just the ones you want by joining the two datasources on A=GoodA.

Perfect, thank you!

Could I ask why you would do the second step as a join rather than doing a SELECT A, B FROM tablename WHERE A NOT IN(SELECT A AS GoodA...) ? Just for my own edification

 

[plog]

You wouldn't use NOT IN, you would just use IN. But once you do, logically, the methods are equivalent. Speedwise, I'm uncertain which is better. I prefer simple easy to understand SQL. A JOIN is much cleaner than another entire query.

 

[dobseh]

Actually, I've just realised - your query is the reverse of what I wanted in that returns all the rows where B match, I need the ones where they don't! So swapping the "=" for "<>" to give me

SELECT A AS GoodA
FROM YourTableName
GROUP BY A
HAVING MIN(B) <> MAX(B)

is all I need for the "pre-flight" check of the bigger process, because I can dcount the result of the query and if it's zero I know I'm good to go. Thank you again for the help.

 

[Pat Hartman]

@dobseh Since this is an Access forum, we assume Jet or ACE as the BE and neither optimize subqueries well. So, unless a subquery is actually necessary - and it isn't in this case, then we help the database engine out by using a join instead. Also, a subquery is harder to test and the QBE doesn't display it easily and most people here are not experienced developers. So, step by step is easier for development.

 

[dobseh]

@dobseh Since this is an Access forum, we assume Jet or ACE as the BE and neither optimize subqueries well. So, unless a subquery is actually necessary - and it isn't in this case, then we help the database engine out by using a join instead. Also, a subquery is harder to test and the QBE doesn't display it easily and most people here are not experienced developers. So, step by step is easier for development.

Interesting, thank you. I'm absolutely not a developer but I prefer to write SQL queries directly rather than use the QBE as my background includes a bit of SQL Server DBA time. I'd like Access to lose it's paranthesis fetish though

 

[ebs17]

and the QBE doesn't display it easily

That is an argument? Anyone who clings to the QBE and cannot or does not want to work with the SQL view is like a cyclist who cannot do without training wheels. It becomes difficult when you have somewhat larger demands.

 

[Pat Hartman]

As I have mentioned many times I came to Access in the early 90's after over twenty years of coding embedded SQL for DB2 in my COBOL programs by hand. All of those years I dreamed of a tool that would automate that tedious process but you turn your nose up at it. OK. Write all the embedded SQL by hand that you want I've written my million lines of code. I don't need the practice. That is why I love Access. I get so much for so little

I understand your problem if you don't actually use the querydefs but still embed the SQL strings. If you don't ever look at the SQL written by the QBE, you don't care about its parentheses fetish I just don't look at it unless I have to. I just simply use the querydef.

To be honest, I occasionally write the SQL String myself when the Where clause is complex AND I know that SQL view will break it. I store it in a table that just holds SQL strings so I can replace the string if I lose my mind and open certain queries in QBE view. In addition to the parentheses fetish, the QBE rewrites complex where clauses that have multiple ANDs and ORs in a somewhat bizarre fashion to make it easy for itself to display the query in QBE view later. So, just a hint. If you have queries that you don't want QBE to mess with the format for, you can paste them into SQL view and save the querydef from that view, if you NEVER save from QBE view, Access won't mess up the string. It will still look the same as when you pasted it into SQL view. That's my compromise.

But, if all your SQL is embedded and you don't want to use querydef's then the QBE has no value for you since its editor is seriously lacking. MS has promised a replacement for 15 years but so far hasn't produced it.

 

[dobseh]

I'm old enough to remember when Intellisense was first available(as a paid product from a third party) for SQL Management Studio and the joy of tab complete that came with it. I'm also old enough to have written backup schedules in NT3.5 using AT commands. I am not against a GUI at all, but I do believe it's important to use the tool that you feel most comfortable in. I'd never want to write another AT command, but that doesn't mean I don't prefer to use the command prompt in Windows or a shell in Linux.
Having said all of that the SQL view in Access is also pretty lacking, I often write more complex queries in VSC because at least I get syntax highlighting and a font I can actually read, then I copy it across into the SQL View. Perhaps when the rewrite of QBE happens we'll also get an updated SQL editor as well...

 

[MajP]

Having said all of that the SQL view in Access is also pretty lacking, I often write more complex queries in VSC because at least I get syntax highlighting and a font I can actually read, then I copy it across into the SQL View. Perhaps when the rewrite of QBE happens we'll also get an updated SQL editor as well...

This is pretty interesting, and I will start another thread to see what people are using if anything. There are lots of Third party add-ins for sql editors, or stand alone applications. Not sure if there is a good free one. I have never bothered.

I looked at this

Access Studio - formatted SQL and more

Attached is a zip file containing 32 and 64bit versions of Access Studio as demonstrated at a number of Access user group meetings over the last few months. It is also available here https://accessusergroups.org/europe/ and also hosted here https://www.isladogs.co.uk/add-ins/index.html The zip...

www.access-programmers.co.uk

Seemed pretty limited. For sure I would want that zoom button on the form and lots of hot keys to drop text. Would want ability to save to file or as said to a table.