Union query - sort of

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
 
J

Jeff Boyce

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
 
S

Stephanie

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

Jeff Boyce

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

Stephanie

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

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

Query with count, but what am I counting?! 4
Query summing on one line 4
"negative" query 4
to view data of a customer from union query 3
run a make table query using parameters in multi-select list box 7
WTD: Nvidia Videocard for 4x AGP Slot 0

Top