Inner Join Involving Range 1

[TXWizard]

In an Aceess 2000 data base, I have two tables, CUSTOMER and tblMonthsOccBrackets. Table CUSTOMERcontains customer daata, including the number of months that each customer occupied a rental unit. Table tblMonthsOccBracketscontains ranges of months (brackets) into which I wish to group the data. My query (not quite finished) looks like this (formatted for readability):

SELECT Count() AS Expr1,
       CUSTOMER.[Property ID],
       [Property Master].Name,
       CUSTOMER.[Unit Type Code],
       CUSTOMER.[Months Occupied]
FROM [Property Master] 
 INNER JOIN (tblMonthsOccBrackets
    INNER JOIN (CUSTOMER
       ON CUSTOMER.[Months Occupied] BETWEEN tblMonthsOccBrackets.intMonthsOccLower 
 AND tblMonthsOccBrackets.intMonthsOccUpper)
       ON [Property Master].[Property ID] = CUSTOMER.[Property ID])
WHERE (((IsNull([Unit Type Code]))=False))
GROUP BY CUSTOMER.[Property ID],
         [Property Master].Name,
         CUSTOMER.[Unit Type Code],
         CUSTOMER.[Months Occupied]
;

Access reports an error in the first join, highlighting the word ON itself.

This is my first foray in years into unequal inner joins. What am I overlooking?

 

[Norris68]

Access is a bit of a git when it comes to all the bracketing in a multiple inner join. Anyway, you seem to have missed a chunk from the innermost join - no WHERE keyword and no table name.

Try:

SELECT Count() AS Expr1,
CUSTOMER.[Property ID],
[Property Master].[Name],
CUSTOMER.[Unit Type Code],
CUSTOMER.[Months Occupied]
FROM tblMonthsOccBrackets, [Property Master]
INNER JOIN CUSTOMER
ON [Property Master].[Property ID] = CUSTOMER.[Property ID]
WHERE (CUSTOMER.[Months Occupied] BETWEEN tblMonthsOccBrackets.intMonthsOccLower
AND tblMonthsOccBrackets.intMonthsOccUpper)
AND ([Unit Type Code] IS NOT NULL)
GROUP BY CUSTOMER.[Property ID],
[Property Master].[Name],
CUSTOMER.[Unit Type Code],
CUSTOMER.[Months Occupied]

You will want to group on the tblMonthsOccBrackets primary key as opposed to the [Months Occupied] field - you will have to also change it in the SELECT clause if so. The above may need tweaking, as obviously I don't have the table structure/data you have.

BTW: The above example does an INNER JOIN between CUSTOMER and [Property Master], and an OUTER JOIN on the other table (in effect no join at all)

 

[TXWizard]

Thanks much. I appreciate your comment about the brackets, which would be less bothersome if I made a regular practice of writing my own SQL.

I thought I'd left out an INNER JOIN clause, too; good catch.

As I thought further about the matter, I decided it would be best to move all these fields into the CUSTOMERS table and let my custom import wizard do all the heavy lifting. These tables are rebuilt daily from an import that comes from an external data base where all the updates happen.

Thanks again for your useful comments.

David Gray
P6 Consulting

http://www.p6c.com/

You are more important than any technology we may employ.

 

[Norris68]

Thanks for the star