I am having a hard time here... I want to LEFT JOIN a table to an inline view of another table but when I try to save the query access tells me that it is an invalid format... This is what I am trying.
SELECT Nature.Nature, CINT(NZ(Apr1.Apr, 0)) AS Apr
FROM Nature LEFT JOIN (SELECT Nature, ComplaintNumber AS Apr FROM NatureCountPerMonth WHERE Month = 4) Apr1
ON Nature.Nature = Apr1.Nature;
It doesn't like seeing the second SELECT but as far as I can tell this is a valid SQL statement. What am I doing wrong?
Also a side note that should probably be in another thread I have also written a TRANSFORM query and would like to generate a report but I can't get access to open the query to design the report... Is this something that is just not going to happen or do I have to do something that I'm not seeing. This is the transform query...
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM NZ(Count(Complaints.Nature), 0) AS CountOfNature
SELECT Complaints.Nature, COUNT(Complaints.Nature) AS Total
WHERE Complaints.LetterDate BETWEEN [Enter Start Date] AND [Enter End Date]
GROUP BY Complaints.Nature
PIVOT CDate("1/" & Month(Complaints.LetterDate) & "/" & Year(Complaints.LetterDate));
It works like I want it to but I want to create a report for it and it just ain't happening.