Query By Dollar Amount

[nancier]

I have a tblAmount with various dollar amounts and another tblRanges with beginning and ending dollar ranges. The query is supposed to look at each amount in tblAmount and throw the amt into the proper dollar range from tblRanges. The query gives totals by dollar range. The problem is when a range has no activity, instead of making a row with zero's, the row is just omitted. I want to still see the range even if there were no items in the row. I've been told it isn't possible to do without adding another query which I don't want to do. Is this correct? Here is the query.

SELECT tblStrataRangeAll.ID, [Begin] & " To " & [End] AS Range, Count(tblAmount.ID) AS Records, Val(Nz(Sum([Amt]),0)) AS Dollars, tblStrataRangeAll.Diff
FROM tblAmount, tblStrataRangeAll
WHERE (((Abs([Amt])) Between [Begin] And [End]))
GROUP BY tblStrataRangeAll.ID, [Begin] & " To " & [End], tblStrataRangeAll.Diff, tblStrataRangeAll.Begin
HAVING (((Sum(tblAmount.Amt))<>False))
ORDER BY tblStrataRangeAll.ID;

 

[r937]

if you want all rows from one table with or without matching rows from another, that's a LEFT OUTER JOIN

you don't need the HAVING

SELECT tblStrataRangeAll.ID
     , [Begin] & " To " & [End] AS Range
     , Count(tblAmount.ID) AS Records
     , Val(Nz(Sum([Amt]),0)) AS Dollars
     , tblStrataRangeAll.Diff
  FROM tblStrataRangeAll
left outer
  join tblAmount
    on Abs([Amt]) Between [Begin] And [End]
GROUP 
    BY tblStrataRangeAll.ID
     , [Begin] & " To " & [End]
     , tblStrataRangeAll.Diff
     , tblStrataRangeAll.Begin
ORDER 
    BY tblStrataRangeAll.ID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[nancier]

Hi, it's odd but I ran your code and get this error even though there is an And. Any idea what's wrong. Thanks

Between operator without And in query expression 'Abs([Amt]) Between [Begin]'.

 

[r937]

ah, access, dontcha just love it

try parenthesizing the ON condition

on (Abs([Amt]) Between [Begin] And [End])

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[nancier]

Hi, I tried that but now it says Join Expression not supported.

Possible causes:

Your SQL statement contains multiple joins in which the results of the query can differ, depending on the order in which the joins are performed. You may want to create a separate query to perform the first join, and then include that query in your SQL statement.
The ON statement in your JOIN operation is incomplete or contains too many tables. You may want to put your ON expression in a WHERE clause.
Thanks

 

[r937]

stupid access

i'm pretty sure i've done a left outer join in access before, but i can't remember the secret gangsign to get this kind of ON condition to work, and i'm too lazy to test it myself, so i'll keep guessing...

on (
   Abs([Amt]) >= [Begin] 
 and
   Abs([Amt]) <= [End]
   )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[nancier]

Hi, I appreciate your efforts but its still says,
Join expression not supported.

 

[r937]

:-(

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)