Issues with Left Join

  • Thread starter Thread starter Rich_in_NZ
  • Start date Start date
R

Rich_in_NZ

(XP using Access '03 in 2000 format)

Hi,
I have a staff training database and I'm trying to count the number of staff
from a specific location and department that have a particular training
record. I have two queries that count records and display the total. The
first query is a total count and the second is a count of records that are
current based on today's date. What I want to happen is combine the two
queries to produce one recordset with the Record Id, Site, Deparment, Total,
CurrentTotal displayed. I have taken an exmaple that a colleague made in
another database and have tried to alter it to my means wih no success. The
code is shown below, at present the error I get is 'Syntax error in FROM
clause' indicated by <error point>. I apologise if it is hard to follow after
copying and pasting. Thanks in advance for any help with this. - Rich

SELECT a.Rec, a.Site, a.Department, TotalCount, CurrentCount
FROM [
SELECT a.Rec, a.Site, a.Department, Sum(a.RecordTotal) as TotalCount,
NZ(SUM(b.CurrCount),0) as CurrentCount
FROM (
SELECT [AllRecords-tbl].RecordID AS Rec, [Staff-tbl].Site,
[Staff-tbl].Department, <error point> Count([AllRecords-tbl].RecordID) as
RecordTotal, Null as CurrCount
FROM [Staff-tbl] INNER JOIN [AllRecords-tbl] ON [Staff-tbl].[Employee #] =
[AllRecords-tbl].[Employee #]
GROUP BY [AllRecords-tbl].RecordID, [Staff-tbl].Site, [Staff-tbl].Department
) a LEFT JOIN
(
SELECT [AllRecords-tbl].RecordID AS Rec, [Staff-tbl].Site,
[Staff-tbl].Department, Null AS RecordTotal, Count(DateAdd("m",[Re-assessment
Period],[Assessment Date])) AS CurrCount
FROM [Staff-tbl] INNER JOIN [AllRecords-tbl] ON [Staff-tbl].[Employee #] =
[AllRecords-tbl].[Employee #]
WHERE ((DateAdd("m",[Re-assessment Period],[Assessment Date]))>=Date())
GROUP BY [AllRecords-tbl].RecordID, [Staff-tbl].Site, [Staff-tbl].Department
) b
ON a.Rec = b.Rec, a.Site = b.Site, a.Department = b.Department
GROUP BY a.Rec, a.Site, a.Department
]. AS [%$##@_Alias];
 
Brilliant!! Thank you very much, you saved me a lot of frustration! I'm a
rookie when it comes to writing these queries, I had thought it must be
possible to do it in one query, but couldn't think how to do it. Thanks again,
Rich
 
Back
Top