Find missing value

[Annette]

I have a table of account numbers joined to another table that lists
dimensions of the account number. The relationship of these tables is
one (account) to many (dimension).

So account 123 has a dimension of ACCT, TRIALBAL, OBJECT, etc.

I would like to create a query that shows all accounts that do not
have a dimension of TRIALBAL. When I attempt to create the query, I
get all the other entries ACCT, OBJECT records when I just want to
find an account from the account table that doesn't have a TRIALBAL
child, disregarding all the other "many" records.

 

[John W. Vinson]

I have a table of account numbers joined to another table that lists
dimensions of the account number. The relationship of these tables is
one (account) to many (dimension).

So account 123 has a dimension of ACCT, TRIALBAL, OBJECT, etc.

I would like to create a query that shows all accounts that do not
have a dimension of TRIALBAL. When I attempt to create the query, I
get all the other entries ACCT, OBJECT records when I just want to
find an account from the account table that doesn't have a TRIALBAL
child, disregarding all the other "many" records.

A "NOT EXISTS" clause is useful here:

SELECT <whatever fields you want>
FROM ACCOUNT
WHERE NOT EXISTS(SELECT TRIALBAL FROM [Dimensions] WHERE
[Dimensions].[accountnumber] = [Account].[accountnumber])
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

A "NOT EXISTS" clause is useful here:

SELECT <whatever fields you want>
FROM ACCOUNT
WHERE NOT EXISTS(SELECT TRIALBAL FROM [Dimensions] WHERE
[Dimensions].[accountnumber] = [Account].[accountnumber])

But do be aware that NOT EXISTS (and NOT IN) is not well-optimized
by the Jet/ACE query pre-processor -- it will often fail to use the
indexes on both sides of the comparison, resulting in major
performance problems. If the query can be done with a JOIN, it's
preferable, as JOINs always utilize indexes to the greatest degree
possible.

 

[John W. Vinson]

A "NOT EXISTS" clause is useful here:

SELECT <whatever fields you want>
FROM ACCOUNT
WHERE NOT EXISTS(SELECT TRIALBAL FROM [Dimensions] WHERE
[Dimensions].[accountnumber] = [Account].[accountnumber])

But do be aware that NOT EXISTS (and NOT IN) is not well-optimized
by the Jet/ACE query pre-processor -- it will often fail to use the
indexes on both sides of the comparison, resulting in major
performance problems. If the query can be done with a JOIN, it's
preferable, as JOINs always utilize indexes to the greatest degree
possible.

Good point, David. In this case the JOIN approach is a bit tricky since you
need to join to a query with criteria. It would be simplest to create a query
qryTRIALBAL as

SELECT accountnum FROM Dimensions WHERE Dimension = "TRIALBAL";

and then use an unmatched query:

SELECT Account.<whatever fields>
FROM ACCOUNT LEFT JOIN qryTrialbal
ON Account.accountnum = qryTrialbal.Accountnum
WHERE qryTrialbal.Accountnum IS NULL;
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

A "NOT EXISTS" clause is useful here:

SELECT <whatever fields you want>
FROM ACCOUNT
WHERE NOT EXISTS(SELECT TRIALBAL FROM [Dimensions] WHERE
[Dimensions].[accountnumber] = [Account].[accountnumber])

But do be aware that NOT EXISTS (and NOT IN) is not well-optimized
by the Jet/ACE query pre-processor -- it will often fail to use
the indexes on both sides of the comparison, resulting in major
performance problems. If the query can be done with a JOIN, it's
preferable, as JOINs always utilize indexes to the greatest degree
possible.

Good point, David. In this case the JOIN approach is a bit tricky
since you need to join to a query with criteria. It would be
simplest to create a query qryTRIALBAL as

SELECT accountnum FROM Dimensions WHERE Dimension = "TRIALBAL";

and then use an unmatched query:

SELECT Account.<whatever fields>
FROM ACCOUNT LEFT JOIN qryTrialbal
ON Account.accountnum = qryTrialbal.Accountnum
WHERE qryTrialbal.Accountnum IS NULL;

It's not all that tricky -- you just use a derived table subquery:

SELECT Account.<whatever fields>
FROM ACCOUNT LEFT JOIN [SELECT TRIALBAL FROM Dimensions WHERE
Dimensions.accountnumber = Account.accountnumber]. As Trialbal
ON Account.accountnum = Trialbal.Accountnum
WHERE qryTrialbal.Accountnum IS NULL;

I do this all the time in dynamic SQL.

However, note that if you have fields with reserved words in the
names or spaces or other characters that require the square
brackets, you can't use this, because the square brackets required
for the derived table in Jet/ACE's SQL 89 dialect for derived tables
cannot have internal square brackets.

If you're working in SQL 92 mode, you can use standard parens
(without the silly trailing period):

SELECT Account.<whatever fields>
FROM ACCOUNT LEFT JOIN (SELECT TRIALBAL FROM Dimensions WHERE
Dimensions.accountnumber = Account.accountnumber). As Trialbal
ON Account.accountnum = Trialbal.Accountnum
WHERE qryTrialbal.Accountnum IS NULL;

When working in SQL 89 mode (i.e., Access's default), you can also
write your SQL in this form and the Access QBE will convert it to
it's idiosyncratic dialect, but if you've got internal square
brackets, it will break, nonetheless.

 

[John W. Vinson]

It's not all that tricky -- you just use a derived table subquery:

SELECT Account.<whatever fields>
FROM ACCOUNT LEFT JOIN [SELECT TRIALBAL FROM Dimensions WHERE
Dimensions.accountnumber = Account.accountnumber]. As Trialbal
ON Account.accountnum = Trialbal.Accountnum
WHERE qryTrialbal.Accountnum IS NULL;

I do this all the time in dynamic SQL.

But what's qryTrialbal in this context? Wouldn't you need to repeat the entire
[SELECT... ] clause again?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

It's not all that tricky -- you just use a derived table subquery:

SELECT Account.<whatever fields>
FROM ACCOUNT LEFT JOIN [SELECT TRIALBAL FROM Dimensions WHERE
Dimensions.accountnumber = Account.accountnumber]. As Trialbal
ON Account.accountnum = Trialbal.Accountnum
WHERE qryTrialbal.Accountnum IS NULL;

I do this all the time in dynamic SQL.

But what's qryTrialbal in this context? Wouldn't you need to
repeat the entire [SELECT... ] clause again?

I maybe misrepresented the actual SQL (as I see the WHERE clause
makes very little sense, unless it's a correlated subquery), but the
point is that almost any saved query you use in a FROM clause could
be replace by the exact same SQL with the correct derived-table
syntax.

That's all I was suggesting, i.e., that there was no requirement to
save the SQL.

 

[John W. Vinson]

That's all I was suggesting, i.e., that there was no requirement to
save the SQL.

True, but I wasn't sure of the syntax of repeating the subquery two or three
times!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Clif McIrvin]

True, but I wasn't sure of the syntax of repeating the subquery two or
three
times!
--

"Intermediate / Beginner" level question:

Would the "SELECT .... AS X" syntax work in this case, where you could
then refer to X the 2nd and 3rd occurrance (instead of repeating the
subquery)?

 

[David-W-Fenton]

"Intermediate / Beginner" level question:

Would the "SELECT .... AS X" syntax work in this case, where you
could then refer to X the 2nd and 3rd occurrance (instead of
repeating the subquery)?

I don't quite understand the question. When you use a derived table
(i.e., a subquery in the FROM clause) it's the same as using a table
or saved query. These two SQL statements are exactly the same:

SELECT DerivedTable.*
FROM [SELECT MyTable.* FROM MyTable]. As DerivedTable

With a saved query called DerivedTable whose SQL is:

SELECT MyTable.* FROM MyTable

....this is how you would use it:

SELECT DerivedTable.*
FROM DerivedTable

Everything outside the FROM clause is going to be EXACTLY the same
whether you use a derived table subquery, or if you save that
subquery as a QueryDef and use that.

 

[Clif McIrvin]

[ ]

Would the "SELECT .... AS X" syntax work in this case, where you
could then refer to X the 2nd and 3rd occurrance (instead of
repeating the subquery)?

I don't quite understand the question. When you use a derived table
(i.e., a subquery in the FROM clause) it's the same as using a table
or saved query. These two SQL statements are exactly the same:

SELECT DerivedTable.*
FROM [SELECT MyTable.* FROM MyTable]. As DerivedTable

With a saved query called DerivedTable whose SQL is:

SELECT MyTable.* FROM MyTable

...this is how you would use it:

SELECT DerivedTable.*
FROM DerivedTable

Everything outside the FROM clause is going to be EXACTLY the same
whether you use a derived table subquery, or if you save that
subquery as a QueryDef and use that.

Thank you David. My question may have been unclear, but your response
explained it quite well.