Union query - sort of

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

Stephanie

Hi. I am trying to combine two queries, I think. What I would like to see is
the # of incidents in the current timeframe against the # of incidents in the
previous timeframe. I'm hoping to be able to graph this information in one
graph.

Here's the sql from 1 of the queries:
SELECT IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(qu_tbFinalIncident_NoLevel4.Level) AS CountOfLevel
FROM qu_tbFinalIncident_NoLevel4
WHERE (((qu_tbFinalIncident_NoLevel4.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))),
qu_tbFinalIncident_NoLevel4.AuthorGroup
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorGroup)=[Forms]![FrQuick]![Combo82]));

The only difference in the "previous" query is:
[Discovery Date]) Between DateAdd('m',-3,[forms]![frquick]![StartDate]) And
DateSerial(Year([forms]![frquick]![EndDate])

Of course when I combine in a Union query, if get;
Level1 1
Level1 4
level 2 5
Level2 6

which doesn't tell me from which time period each of the levels is from. And
I can't imagine that I would be able to graph this output.

I'd appreciate any suggestions.

Cheers,
Stephanie
 
If you add in one more field in each of the two SELECT clauses (SELECT ...
UNION SELECT ...), you could make that/those extra fields a literal,
designated the timeframe.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks. I'm not sure I understand. When I add a field into each of the SELECT
statements, are these fields from the exisiting table used in the query or
would I create a "calculated" field in the query? And how would I get
something uniquely identifiying each query into these fields? Thanks.

Jeff Boyce said:
If you add in one more field in each of the two SELECT clauses (SELECT ...
UNION SELECT ...), you could make that/those extra fields a literal,
designated the timeframe.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Stephanie said:
Hi. I am trying to combine two queries, I think. What I would like to see
is
the # of incidents in the current timeframe against the # of incidents in
the
previous timeframe. I'm hoping to be able to graph this information in one
graph.

Here's the sql from 1 of the queries:
SELECT IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(qu_tbFinalIncident_NoLevel4.Level) AS CountOfLevel
FROM qu_tbFinalIncident_NoLevel4
WHERE (((qu_tbFinalIncident_NoLevel4.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))),
qu_tbFinalIncident_NoLevel4.AuthorGroup
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorGroup)=[Forms]![FrQuick]![Combo82]));

The only difference in the "previous" query is:
[Discovery Date]) Between DateAdd('m',-3,[forms]![frquick]![StartDate])
And
DateSerial(Year([forms]![frquick]![EndDate])

Of course when I combine in a Union query, if get;
Level1 1
Level1 4
level 2 5
Level2 6

which doesn't tell me from which time period each of the levels is from.
And
I can't imagine that I would be able to graph this output.

I'd appreciate any suggestions.

Cheers,
Stephanie
Click to expand...
Click to expand...
 
Stephanie

SELECT FieldA, FieldB, "TimeFrame1", ...
FROM YourFirstQuery
....
UNION
SELECT FieldA, FieldB, "TimeFrame2", ...
FROM YourSecondQuery
....


Regards

Jeff Boyce
Microsoft Office/Access MVP

Stephanie said:
Thanks. I'm not sure I understand. When I add a field into each of the
SELECT
statements, are these fields from the exisiting table used in the query or
would I create a "calculated" field in the query? And how would I get
something uniquely identifiying each query into these fields? Thanks.

Jeff Boyce said:
If you add in one more field in each of the two SELECT clauses (SELECT
...
UNION SELECT ...), you could make that/those extra fields a literal,
designated the timeframe.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Stephanie said:
Hi. I am trying to combine two queries, I think. What I would like to
see
is
the # of incidents in the current timeframe against the # of incidents
in
the
previous timeframe. I'm hoping to be able to graph this information in
one
graph.

Here's the sql from 1 of the queries:
SELECT IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(qu_tbFinalIncident_NoLevel4.Level) AS CountOfLevel
FROM qu_tbFinalIncident_NoLevel4
WHERE (((qu_tbFinalIncident_NoLevel4.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))),
qu_tbFinalIncident_NoLevel4.AuthorGroup
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorGroup)=[Forms]![FrQuick]![Combo82]));

The only difference in the "previous" query is:
[Discovery Date]) Between DateAdd('m',-3,[forms]![frquick]![StartDate])
And
DateSerial(Year([forms]![frquick]![EndDate])

Of course when I combine in a Union query, if get;
Level1 1
Level1 4
level 2 5
Level2 6

which doesn't tell me from which time period each of the levels is
from.
And
I can't imagine that I would be able to graph this output.

I'd appreciate any suggestions.

Cheers,
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Perfect. Thanks for the great explanation.

Jeff Boyce said:
Stephanie

SELECT FieldA, FieldB, "TimeFrame1", ...
FROM YourFirstQuery
....
UNION
SELECT FieldA, FieldB, "TimeFrame2", ...
FROM YourSecondQuery
....


Regards

Jeff Boyce
Microsoft Office/Access MVP

Stephanie said:
Thanks. I'm not sure I understand. When I add a field into each of the
SELECT
statements, are these fields from the exisiting table used in the query or
would I create a "calculated" field in the query? And how would I get
something uniquely identifiying each query into these fields? Thanks.

Jeff Boyce said:
If you add in one more field in each of the two SELECT clauses (SELECT
...
UNION SELECT ...), you could make that/those extra fields a literal,
designated the timeframe.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi. I am trying to combine two queries, I think. What I would like to
see
is
the # of incidents in the current timeframe against the # of incidents
in
the
previous timeframe. I'm hoping to be able to graph this information in
one
graph.

Here's the sql from 1 of the queries:
SELECT IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))) AS IncidentLevel,
Count(qu_tbFinalIncident_NoLevel4.Level) AS CountOfLevel
FROM qu_tbFinalIncident_NoLevel4
WHERE (((qu_tbFinalIncident_NoLevel4.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY IIf([Level]=1,"Level 1",IIf([Level]=0,"Near
Misss",IIf([Level]=4,"Tech (Level 4)","Level 2"))),
qu_tbFinalIncident_NoLevel4.AuthorGroup
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorGroup)=[Forms]![FrQuick]![Combo82]));

The only difference in the "previous" query is:
[Discovery Date]) Between DateAdd('m',-3,[forms]![frquick]![StartDate])
And
DateSerial(Year([forms]![frquick]![EndDate])

Of course when I combine in a Union query, if get;
Level1 1
Level1 4
level 2 5
Level2 6

which doesn't tell me from which time period each of the levels is
from.
And
I can't imagine that I would be able to graph this output.

I'd appreciate any suggestions.

Cheers,
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top