Query with count, but what am I counting?!

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?
 
D

Duane Hookom

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")));
 
S

Stephanie

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...
 
D

Duane Hookom

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...
 
S

Stephanie

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union query - sort of 4
History of Xbox 360 defects - Dean Takahashi has the inside story(long) 2

Top