LEFT JOIN and WHERE clause

[Ian Chappel]

I have the following query:

SELECT tblNominals.NomCode AS Code, tblNominals.NomName AS Name,
Sum(qryIdNominals.Nett) AS Balance
FROM qryIdNominals RIGHT JOIN tblNominals ON qryIdNominals.NomID =
tblNominals.NomID
WHERE qryIdNominals.Date Between [StartDate] And [EndDate]
GROUP BY tblNominals.NomCode, tblNominals.NomName;

I want to list *all* records from tblNominals, and corresponding SUM values
from qryIdNominals.Nett, for a given date range

It works fine until I put in the WHERE clause.

I have tried - I sort of have an idea what's wrong, after spending an hour
searching, but still can't fix it!!!

 

[Jerry Whittle]

Try changing the word WHERE to HAVING.

 

[Ian Chappel]

Thanks for the speedy reply Jerry. Unfortunately it doesn't work!

I'm getting a "Syntax error in HAVING clause".

Try changing the word WHERE to HAVING.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the following query:

SELECT tblNominals.NomCode AS Code, tblNominals.NomName AS Name,
Sum(qryIdNominals.Nett) AS Balance
FROM qryIdNominals RIGHT JOIN tblNominals ON qryIdNominals.NomID =
tblNominals.NomID
WHERE qryIdNominals.Date Between [StartDate] And [EndDate]
GROUP BY tblNominals.NomCode, tblNominals.NomName;

I want to list *all* records from tblNominals, and corresponding SUM
values
from qryIdNominals.Nett, for a given date range

It works fine until I put in the WHERE clause.

I have tried - I sort of have an idea what's wrong, after spending an
hour
searching, but still can't fix it!!!

 

[Jerry Whittle]

Dang! I was hoping that it was something simple. It still might be. What
happens when you change RIGHT JOIN to INNER JOIN? I'm thinking that null
values might be messing up the sum.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks for the speedy reply Jerry. Unfortunately it doesn't work!

I'm getting a "Syntax error in HAVING clause".

Try changing the word WHERE to HAVING.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the following query:

SELECT tblNominals.NomCode AS Code, tblNominals.NomName AS Name,
Sum(qryIdNominals.Nett) AS Balance
FROM qryIdNominals RIGHT JOIN tblNominals ON qryIdNominals.NomID =
tblNominals.NomID
WHERE qryIdNominals.Date Between [StartDate] And [EndDate]
GROUP BY tblNominals.NomCode, tblNominals.NomName;

I want to list *all* records from tblNominals, and corresponding SUM
values
from qryIdNominals.Nett, for a given date range

It works fine until I put in the WHERE clause.

I have tried - I sort of have an idea what's wrong, after spending an
hour
searching, but still can't fix it!!!

 

[Jerry Whittle]

Further grasping at the low hanging fruit, you are using some reserved words
which might cause grief. In particular Name and Date.

Try changing the SQL this way by putting brackets around Date:

qryIdNominals.[Date]

Also try changing the alias "Name" to "Names".
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks for the speedy reply Jerry. Unfortunately it doesn't work!

I'm getting a "Syntax error in HAVING clause".

Try changing the word WHERE to HAVING.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the following query:

SELECT tblNominals.NomCode AS Code, tblNominals.NomName AS Name,
Sum(qryIdNominals.Nett) AS Balance
FROM qryIdNominals RIGHT JOIN tblNominals ON qryIdNominals.NomID =
tblNominals.NomID
WHERE qryIdNominals.Date Between [StartDate] And [EndDate]
GROUP BY tblNominals.NomCode, tblNominals.NomName;

I want to list *all* records from tblNominals, and corresponding SUM
values
from qryIdNominals.Nett, for a given date range

It works fine until I put in the WHERE clause.

I have tried - I sort of have an idea what's wrong, after spending an
hour
searching, but still can't fix it!!!

 

[Jerry Whittle]

I forgot to include a list of reserved words. Find them here:

http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks for the speedy reply Jerry. Unfortunately it doesn't work!

I'm getting a "Syntax error in HAVING clause".

Try changing the word WHERE to HAVING.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the following query:

SELECT tblNominals.NomCode AS Code, tblNominals.NomName AS Name,
Sum(qryIdNominals.Nett) AS Balance
FROM qryIdNominals RIGHT JOIN tblNominals ON qryIdNominals.NomID =
tblNominals.NomID
WHERE qryIdNominals.Date Between [StartDate] And [EndDate]
GROUP BY tblNominals.NomCode, tblNominals.NomName;

I want to list *all* records from tblNominals, and corresponding SUM
values
from qryIdNominals.Nett, for a given date range

It works fine until I put in the WHERE clause.

I have tried - I sort of have an idea what's wrong, after spending an
hour
searching, but still can't fix it!!!

 

[John Spencer]

Your where clause is limiting the records that are returned to those that
have a date in qryIDNominals. If there is no matching record in that query
for a record in tblNominals the date field will be NULL.

You can try the following. Although this may give you unwanted records. If
so, you need to modify qryIdNominals so that it is restricted to the date
range and then use it in this query.

SELECT tblNominals.NomCode AS Code, tblNominals.NomName AS Name,
Sum(qryIdNominals.Nett) AS Balance
FROM qryIdNominals RIGHT JOIN tblNominals
ON qryIdNominals.NomID = tblNominals.NomID
WHERE qryIdNominals.Date Between [StartDate] And [EndDate]
OR qryIdNominals.Date is Null
GROUP BY tblNominals.NomCode, tblNominals.NomName;

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

I want to list *all* records from tblNominals, and corresponding SUM values
from qryIdNominals.Nett, for a given date range

It works fine until I put in the WHERE clause.

I have tried - I sort of have an idea what's wrong, after spending an hour
searching, but still can't fix it!!!

The trouble is that with your outer join, you're requiring that the date value
in qryIDNominals be within a certain range - *even if there is no record in
qryIDNominals at all*. Applying the criterion restricts you to those records
with data! Same problem with the Sum.



Try

SELECT tblNominals.NomCode AS Code, tblNominals.NomName AS Name,
Sum(NZ(qryIdNominals.Nett)) AS Balance
FROM qryIdNominals RIGHT JOIN tblNominals ON qryIdNominals.NomID =
tblNominals.NomID
WHERE (qryIdNominals.[Date] Between [StartDate] And [EndDate] OR
qryIDNominals.[Date] IS NULL)
GROUP BY tblNominals.NomCode, tblNominals.NomName;

Alternatively, move the date criterion into qryIDNominals.

John W. Vinson [MVP]

 

[IanC]

Thanks - I may not have been clear as to what my problem was...

It works fine as I posted, except I don't get all records from
tblNominals. That is to say I get exactly the same results if I use
INNER JOIN as when I use RIGHT JOIN. The RIGHT JOIN only works if I
don't have the WHERE clause. In other words I can't have everything at
once!

I don't _think_ it's to do with Reserved Words, as I have a lot of
other queries etc. in this database that work fine. BTW John, every
record in qryIDNominals does have a date.

 

[IanC]

Thanks John V, that's cracked it. I sort of had an idea along those
lines, but couldn't quite get there...

 

[IanC]

I posted too soon!

John Vinson's query doesn't actually return all records from
tblNominals. It returns the easy ones (ones where a record exists in
qryIdNominals within the Date Range) and ones which have no records in
qryIdNominals (I assume picked up by the "OR NULL"). What it misses is
the ones where a record does exist in qryIdNominals outisde the Date
Range.

Any ideas? Or is the only option to move the date criterion into
qryIDNominals? I'd prefer not to if possible, as this query is used in
a few other objects, but I guess I could clone it if it's the only way.

 

[John W. Vinson]

Any ideas? Or is the only option to move the date criterion into
qryIDNominals?

If you want to see all the records whatever the date, why have the criterion
at all!!?

John W. Vinson [MVP]

 

[John Spencer]

Since you are usiing Access, the only option is to move the date criterion
to qryIDNominals. You could use a subquery but the problem is you can use a
subquery in the from clause that require square brackets.

The following would probably work:

SELECT tblNominals.NomCode AS Code
, tblNominals.NomName AS Name,
Sum(Q.Nett) AS Balance
FROM
(SELECT NomID, Nett
FROM qryIDNominals
WHERE qryIdNominals.Date Between #1/1/2007# And #12/31/2007# )
as Q RIGHT JOIN tblNominals
ON Q.NomID = tblNominals.NomID
GROUP BY tblNominals.NomCode, tblNominals.NomName;

The following will probably (very probably) fail
SELECT tblNominals.NomCode AS Code
, tblNominals.NomName AS Name,
Sum(Q.Nett) AS Balance
FROM
(SELECT NomID, Nett
FROM qryIDNominals
WHERE qryIdNominals.Date Between [Start Date] And [End Date] )
as Q RIGHT JOIN tblNominals
ON Q.NomID = tblNominals.NomID
GROUP BY tblNominals.NomCode, tblNominals.NomName;

Of course, you could always use VBA to build the first query string and then
use that as the source for your report (or form).

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[IanC]

John Spencer:
No it doesn't fail! And Thanks - the second example works fine, and
does exactly what I wanted. BTW I'm using Access 2003 (not sure if
that makes any difference).

John Vinson:
I only wanted all records on the "one" side of the relatisionship, and
a totals from a range of dates (from data on the "many" side).

Thanks to you both.