Query with count, but what am I counting?!

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

Hi. I'm going in circles.
I want to pass date parameters into my query (StartDate, EndDate).

I am counting the number of incident at end level that occur in a given year
with in the date parameters. In order to feed the date parameters, I need to
include [LoggedDate], but then the total count for Level doesn't work- I want
4 counts, not a count for each row. Arg!

PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );
SELECT tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(tbIncidents_Final.Level) AS CountOfLevel
FROM tbIncidents_Final
GROUP BY tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))),
tbIncidents_Final.LoggedYear, tbIncidents_Final.LoggedDate
HAVING (((tbIncidents_Final.LoggedYear)=2009))
ORDER BY tbIncidents_Final.LoggedYear;

Hope you can help me realign my query. This query is for a graph on a
subform. Would the form query include the data parameter as well?
 
Try:

PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );
SELECT tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(tbIncidents_Final.Level) AS CountOfLevel
FROM tbIncidents_Final
WHERE (((tbIncidents_Final.LoggedYear)=2009))
GROUP BY tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2")));
 
Thanks Duane. Now I know how to pass the parameters- very helpful
But my bad- I didn't include the actual data parameters and now I'm trying
to figure out how to add two parameters (syntax craziness!)

PARAMETERS BETWEEN [Forms]![frQuick]![StartDate] AND
[Forms]![frQuick]![StartDate)];
PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );

Thanks,
Stephanie

Duane Hookom said:
Try:

PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );
SELECT tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(tbIncidents_Final.Level) AS CountOfLevel
FROM tbIncidents_Final
WHERE (((tbIncidents_Final.LoggedYear)=2009))
GROUP BY tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2")));

--
Duane Hookom
Microsoft Access MVP


Stephanie said:
Hi. I'm going in circles.
I want to pass date parameters into my query (StartDate, EndDate).

I am counting the number of incident at end level that occur in a given year
with in the date parameters. In order to feed the date parameters, I need to
include [LoggedDate], but then the total count for Level doesn't work- I want
4 counts, not a count for each row. Arg!

PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );
SELECT tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(tbIncidents_Final.Level) AS CountOfLevel
FROM tbIncidents_Final
GROUP BY tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))),
tbIncidents_Final.LoggedYear, tbIncidents_Final.LoggedDate
HAVING (((tbIncidents_Final.LoggedYear)=2009))
ORDER BY tbIncidents_Final.LoggedYear;

Hope you can help me realign my query. This query is for a graph on a
subform. Would the form query include the data parameter as well?
Click to expand...
Click to expand...
 
Your parameters clause should look like:
PARAMETERS [Forms]![frQuick]![StartDate] DateTime,
[Forms]![frQuick]![StartDate)] DateTime;

They don't include the "WHERE" and are separated by a comman with the
associated data type.
--
Duane Hookom
Microsoft Access MVP


Stephanie said:
Thanks Duane. Now I know how to pass the parameters- very helpful
But my bad- I didn't include the actual data parameters and now I'm trying
to figure out how to add two parameters (syntax craziness!)

PARAMETERS BETWEEN [Forms]![frQuick]![StartDate] AND
[Forms]![frQuick]![StartDate)];
PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );

Thanks,
Stephanie

Duane Hookom said:
Try:

PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );
SELECT tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(tbIncidents_Final.Level) AS CountOfLevel
FROM tbIncidents_Final
WHERE (((tbIncidents_Final.LoggedYear)=2009))
GROUP BY tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2")));

--
Duane Hookom
Microsoft Access MVP


Stephanie said:
Hi. I'm going in circles.
I want to pass date parameters into my query (StartDate, EndDate).

I am counting the number of incident at end level that occur in a given year
with in the date parameters. In order to feed the date parameters, I need to
include [LoggedDate], but then the total count for Level doesn't work- I want
4 counts, not a count for each row. Arg!

PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );
SELECT tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(tbIncidents_Final.Level) AS CountOfLevel
FROM tbIncidents_Final
GROUP BY tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))),
tbIncidents_Final.LoggedYear, tbIncidents_Final.LoggedDate
HAVING (((tbIncidents_Final.LoggedYear)=2009))
ORDER BY tbIncidents_Final.LoggedYear;

Hope you can help me realign my query. This query is for a graph on a
subform. Would the form query include the data parameter as well?
Click to expand...
Click to expand...
Click to expand...
 
Duane,

Thanks for the reply and forthe lesson!

Cheers,
Stephanie

Duane Hookom said:
Your parameters clause should look like:
PARAMETERS [Forms]![frQuick]![StartDate] DateTime,
[Forms]![frQuick]![StartDate)] DateTime;

They don't include the "WHERE" and are separated by a comman with the
associated data type.
--
Duane Hookom
Microsoft Access MVP


Stephanie said:
Thanks Duane. Now I know how to pass the parameters- very helpful
But my bad- I didn't include the actual data parameters and now I'm trying
to figure out how to add two parameters (syntax craziness!)

PARAMETERS BETWEEN [Forms]![frQuick]![StartDate] AND
[Forms]![frQuick]![StartDate)];
PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );

Thanks,
Stephanie

Duane Hookom said:
Try:

PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );
SELECT tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(tbIncidents_Final.Level) AS CountOfLevel
FROM tbIncidents_Final
WHERE (((tbIncidents_Final.LoggedYear)=2009))
GROUP BY tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2")));

--
Duane Hookom
Microsoft Access MVP


:

Hi. I'm going in circles.
I want to pass date parameters into my query (StartDate, EndDate).

I am counting the number of incident at end level that occur in a given year
with in the date parameters. In order to feed the date parameters, I need to
include [LoggedDate], but then the total count for Level doesn't work- I want
4 counts, not a count for each row. Arg!

PARAMETERS [Forms]![frQuick]![Combo82] Text ( 255 );
SELECT tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(tbIncidents_Final.Level) AS CountOfLevel
FROM tbIncidents_Final
GROUP BY tbIncidents_Final.Dept, IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))),
tbIncidents_Final.LoggedYear, tbIncidents_Final.LoggedDate
HAVING (((tbIncidents_Final.LoggedYear)=2009))
ORDER BY tbIncidents_Final.LoggedYear;

Hope you can help me realign my query. This query is for a graph on a
subform. Would the form query include the data parameter as well?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top