Show all records in parameter query

[Mudface]

I have two related tables, tmain and a look up table for AccidentCauses. I can show all records for all accident causes (including those with no accidents) with the following SQL:-

SELECT tAccidentCauses.AccidentCause, Count(tMain.IncidentID) AS CountOfIncidentID
FROM tAccidentCauses LEFT JOIN tMain ON tAccidentCauses.AccidentCauseID = tMain.AccidentCause
GROUP BY tAccidentCauses.AccidentCause
ORDER BY Count(tMain.IncidentID) DESC;

However, I'd like to make it a parameter query so people can see accidents by year. When I use the SQL below, though I can only seem to show accident causes that have associated records. Anyone have an idea on what I need to do? Thanks in advance.

SELECT tAccidentCauses.AccidentCause, Count(tMain.IncidentID) AS CountOfIncidentID
FROM tAccidentCauses LEFT JOIN tMain ON tAccidentCauses.AccidentCauseID = tMain.AccidentCause
GROUP BY tAccidentCauses.AccidentCause, Year([incdate])
HAVING (((Year([incdate]))=[Enter Year]))
ORDER BY Count(tMain.IncidentID) DESC;

 

[Anne Troy]

I don't know SQL. But if it were me, I'd prolly go the easy route. Make your select query as you have, with no criteria. Then make another query (that handles the parameters) based on the SQL query.

_________________
Anne@TheOfficeExperts.com
TheOfficeExperts
This message was edited by Dreamboat on 2002-12-13 12:46

 

[Russell Hauf]

I think what Dreamboat is saying is right. Make a query based on your tMain, that selects AccidentCause and IncidentID, where Year(incdate) = [Enter Year] (don't show Year(incdate) ). Then, replace tMain in your second query with the query you just wrote, still using the LEFT join.

Something like:

SELECT tMain.AccidentCause, tMain.IncidentID
FROM tMain
WHERE Year([incdate])=[Enter Year];

And if you save this as qryMainByYear, then your final query would look something like this:

SELECT tAccidentCauses.AccidentCause, Count(qryMainByYear.IncidentID) AS CountOfIncidentID
FROM tAccidentCauses LEFT JOIN qryMainByYear ON tAccidentCauses.AccidentCauseID = qryMainByYear.AccidentCause
GROUP BY tAccidentCauses.AccidentCause
ORDER BY Count(qryMainByYear.IncidentID) DESC;

HTH,

Russell

 

[Russell Hauf]

By the way, I suggest that you rename AccidentCause in your tMain to AccidentCauseID. You'll avoid much trouble down the line.

-rh

 

[Mudface]

Excellent, thanks very much Russell, that's spot on. Strange how I couldn't seem to do it in one go, but never mind. Point taken about the field name, too. Cheers again.