Hi,
I have 1 table I'm querying against. And that table keeps track of child transaction records for each client.

The first field (which is referenced) is ClientID, the second field is Ticker.

I'm trying to get all the ClientID's (grouped) that don't have the ticker "GOOG".

I'm not sure how to execute the Group By clause accurately.

Hi,
I have 1 table I'm querying against. And that table keeps track of child transaction records for each client.

The first field (which is referenced) is ClientID, the second field is Ticker.

I'm trying to get all the ClientID's (grouped) that don't have the ticker "GOOG".

I'm not sure how to execute the Group By clause accurately.

Please post the query code.

Something like this air code:

select ClientId, Count(*)
from
whateveryourtablenameis
where
ClientId Not In
(select ClientId from whateveryourtablenameis where Ticker = 'GOOG')
group by
ClientID

I tried that, and again I ran into performance issues. In fact, the query never successfully created the view.

I moved the criteria to after the grouping and it worked :

SELECT ClientID
FROM Holdings
GROUP BY ClientID
HAVING ClientID NOT IN (Select ClientID From Holdings Where Ticker = 'Goog');

Does anyone have a good explanation why putting it in the WHERE clause doesn't work?

Try this and see if you have better results.

SELECT ClientID
FROM Holdings ah
LEFT JOIN (
SELECT ClientID
FROM Holdings
WHERE Ticker = "Goog"
) gh
ON ah.ClientID = gh.ClientID
WHERE gh.ClientID IS NULL
GROUP BY ah.ClientID;

Let me know how it goes and I'll provide a better explanation of what's going on, but first I want to verify if this works to your satisfaction.

@ Banana, the code you posted was successful with 1 small edit I underlined in the main SELECT:

SELECT ah.ClientID
FROM Holdings ah
LEFT JOIN (
SELECT ClientID
FROM Holdings
WHERE Ticker = "Goog"
) gh
ON ah.ClientID = gh.ClientID
WHERE gh.ClientID IS NULL
GROUP BY ah.ClientID;

I'm always concerned about performance when it comes to these kinds of queries since my system is slow and the dba has denormalized all the data (for some unknown reason)

Well, I'm glad to know it was 'successful', though I'm not sure whether to take it to mean that it performed well enough and more importantly better than the one using HAVING.

But anyway here's the explanation:

1) In some situations, when we want to get non-matches from JET tables, it's easy to reach for NOT IN() or NOT EXISTS. However, for whatever reasons, JET's optimizer doesn't work well with those two functions though they does in say, MS SQL Server. Therefore, the usual approach to get only non-matches is to use 'frustrated join', which was what I just handed to you.

2) Be aware that HAVING clause occurs after GROUP BY clause, meaning that the engine has to retrieve all those records, group it then evaluates the HAVING _after_ the grouping, then toss out the records. It's kind like telling it to go and get a bucket of water then pour it into the bottle, and discard the rest of water down the sink and enjoying the small bottle. Not every effective, is it? Now, HAVING is appropriate for what cannot be known beforehand such as "HAVING COUNT(somefield)>2" but for any other criteria that doesn't depend on aggregated results, use WHERE. It's the only place it belongs in.

3) I'm not sure why HAVING worked where WHERE failed, and does make me wonder whether ClientID in fact indexed? Does Holdings table contain many duplicates ClientID? Furthermore, I just realized that in your original query you were getting ClientID from Holdings... I'd bet that WHERE crashed because it was evaluating duplicates clientID against many duplicates ClientID whereas GROUP BY simplifies the evaluation to only set of distinct ClientID.

NOT IN is a dog in all DBMSs that I know of (including Oracle, MS SS, and all the old ones that don't seem to exist anymore). I only use it when I'm too lazy to do an outer join and check for NULL.

@ Banana or anyone else,

Could you explain this concept of a JET Table if its not too much trouble?
I think a lot of beginner/intermediate users like myself are still struggling with how SQL code is stepped through by the DBMS.

I'm also aware that JET doesn't handle UPDATE statements very well either.

Is this because of the vendor? There aren't very many ANSI SQL standard compliant DBMS' out there. And I'm guessing MS Access uses JET for...? What is JET any way?

JET is a database engine that Access uses. (Side note: In Access 2007 and later, it has been renamed to ACE, but the technology is still same) Access is just what you use to interact with data but the data are actually processed by JET/ACE.

I wouldn't say that JET doesn't handle UPDATE well- I would say that UPDATE statement is most tricky of all SQL statements. It's easy to insert because you're adding something completely new, and it's easy to delete because it works on a row level. Updating, OTOH, you have to be really careful that you not only get the right rows but also in such way that it traces back to the source row. In other words, if you tried to update a query that was based on an aggregate, it would fail because in process of aggregating, several rows were grouped together to form the output (e.g. summing all a certain column for a group)... the question becomes "which row should actually get updated??"

Allen Browne has a nice article on how queries could become 'read-only'. (http://allenbrowne.com/ser-61.html) The article is very relevant even in different contexts since as using a query to provide source for update/appending or creating a recordsource.

As for ANSI compliance... You know that we're finalizing HTML 5. Yet none of modern browsers support HTML 4 fully and even do not implement HTML 3 in all aspects. HTML and SQL was a means to standardize on a convention (markup language in former, query language in latter). However! Both allowed vendors to introduce their "extensions"... SQL Server has their MERGE statement. MySQL has their ENGINE option, REPLACE, ON DUPLICATE KEY UPDATE, and Oracle has their PL/SQL & compiler just as Internet Exploder has their ActiveX, Firefox has their extensions not to mention that they could parse same page slightly different.

Anyway, there is an option in Access to use ANSI-92. However if you've already created a database, it'd break several queries and would be more hassle than helpful so it's usually not worth the trouble to do this.

Thanks for all your help Banana and others.

I read over Allen Browne's non-updateable query notes for access. And one interesting thing he covered was Subqueries.

I found the following code from TechOnTheNet:

UPDATE suppliers
SET city = ( SELECT customers.city
FROM customers
WHERE customers.customer_name = suppliers.supplier_name)
WHERE EXISTS
( SELECT customers.city
FROM customers
WHERE customers.customer_name = suppliers.supplier_name);

The problem is that this code can't be mimicked in an access environment. Does anyone know if this has to do with the JET environment? And how does someone issue the same kind of update statement within access without having to parse it out in VBA?