Can't quite phrase a query correctly...

[AccessChap]

Hi guys,

Can anyone help with how I should be constructing this query please? I have this so far:

SELECT Format(A.Date_Logged,"yyyy" & " mm " & "dd") AS DateRange, A.Action
as Action, Count(*) AS Total
FROM tbl_security_request as A left join tbl_security_request_track_amend
as B
on A.record_id = B.record_id
and B.password_reset = FALSE
GROUP BY Format(A.Date_Logged,"yyyy" & " mm " & "dd"), A.Action

I'm trying to list out all the security requests from table A where there may or may not be a corresponding record in table B. However should a matching record be found in table B I only want to return the record from table A should it not be regarding a password reset. Hope that makes sense

What I'm expecting is a listing like this:

Date Action Total
--------------------------
2009 01 01 Amend 11
2009 01 01 Delete 4
2009 01 02 Amend 10

etc etc

What I'm actually getting is an error message "Join expression not supported" but I can't figure out what is causing the statement to fail. Anybody got any hints or ideas?

Many thanks,

Andy

 

[DCrake]

Should be format(A.Date_Logged,"yyyy mm dd") As DateRange

 

[AccessChap]

Should be format(A.Date_Logged,"yyyy mm dd") As DateRange

Close but no banana, that's a formatting change. I still get the same error message