Issues with Left Join

[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];

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not positive about this, but I believe you can simplify your query
by putting the criteria of one query, with an IIf() function, in a SUM()
function in the SELECT clause, like this:

SELECT R.RecordID AS Rec, S.Site, S.Department,
COUNT(*) as TotalCount ,
SUM(IIf(DateAdd("m",[Re-assessment Period],[Assessment Date])>=Date()),
1,0) As CurrentCount
FROM [Staff-tbl] As S INNER JOIN [AllRecords-tbl] As R ON S.[Employee #]
= R.[Employee #]
GROUP BY R.RecordID, S.Site, S.Department

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSctRgIechKqOuFEgEQKOQgCg2wFKMsd7IqzO2+Kfog0qQREOyfEAoIvX
OM4r7VCaKbAOZKUgI6TVXbiX
=ilZI
-----END PGP SIGNATURE-----

(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];

 

[Rich_in_NZ]

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