SQL syntax

[dssrun]

Instead of creating a query and running it against 2 tables I am attempting a subquery, however my experience is limited and below i am getting "Syntax error (missing operator) in join expression". The tbl "Area" has the primary key Facility

SELECT sum(Q.[SumOfTaxes]),[Area].[Facility], Sum([tbl1].[Payment]) AS [SumOfPayment]
FROM
          (SELECT [tbl2].[Facility], Sum([tbl2].[Taxes]) AS [SumOfTaxes]
          FROM tbl2
          WHERE (((Month([Run Date]) & Year([Run Date]))=Month(Date()) & Year(Date())))
          GROUP BY [tbl2].[Facility]
          ) AS Q INNER JOIN [Area] ON [Q].[Facility] = [Area].[Facility] INNER JOIN [tbl1] ON [Area].[Facility] = [tbl1].[Facility]

WHERE ((([tbl1].Time)="Month"))

GROUP BY [Area].[Facility];

[kennejd]

This line won't work: WHERE ((([tbl1].Time)="Month"))....what is the value of "Month" supposed to be (in your query "Month" = "Month")? What type of field is Time (date/time I would expect)? You need to compare like with like....so maybe month(tbl1.time) = month(date())?

[dssrun]

Sorry I should have specified, that field has values "month", "year", "week" etc and I want the records with "month"

[kennejd]

Did you figure it out yet? I would probably break it into pieces...bite-sized, of course...

[dssrun]

I did, but I simplified my query a lot. I plan to expand on this through trial and error little by little. Part of my problem was not putting the proper field in the GROUP BY. Thanks.

SELECT Sum([tbl1].[Payment]) - Q.[SumCharge]) AS [Balance]
       
FROM (SELECT Sum([Charge]) as SumCharge)AS Q, [tbl1]

GROUP BY [Balance]