Merging two crosstab queries to create one chart

[FA]

Hi Freinds I have a huge issue hoping that if anyone can resolve it. Is
there any way i can merge both chart in one chart. I only want one
chart with two bars for each month. On bar represent all number of
system with Completed status, and other bar represent the rest of the
system in que.
I created two sperate queries to achieve this goal but i want to have
only one chart. I tried to do the Union query with both of these
queries but it didnt work and it is giving me syntax errors.


I have a chart based on the following cross tab query.
TRANSFORM Count(dbo_SYS_INFO.SYS_NME) AS CountOfSYS_NME
SELECT Year([TEST_END_DATE]) AS [Year], dbo_SYS_INFO.SYS_NME,
dbo_ASMT_SIZE.ASMT_SIZE, Count((Month([TEST_END_DATE]))) AS YTD,
Count(dbo_ASMT_SIZE.ASMT_SIZE) AS CountOfASMT_SIZE
FROM (dbo_SYS_INFO INNER JOIN dbo_ASMT_SIZE ON
dbo_SYS_INFO.ASMT_SIZE_ID = dbo_ASMT_SIZE.ASMT_SIZE_ID) INNER JOIN
dbo_TEST_STAT ON dbo_SYS_INFO.TEST_STAT_ID = dbo_TEST_STAT.TEST_STAT_ID
WHERE (((dbo_ASMT_SIZE.ASMT_SIZE)="FULL") AND
((dbo_TEST_STAT.TEST_STAT)="Completed"))
GROUP BY Year([TEST_END_DATE]), dbo_SYS_INFO.SYS_NME,
dbo_ASMT_SIZE.ASMT_SIZE, dbo_TEST_STAT.TEST_STAT
PIVOT Month([TEST_STAT_DATE]);

I also have another chart based on the following query
TRANSFORM Count(dbo_SYS_INFO.SYS_NME) AS CountOfSYS_NME
SELECT Year([TEST_STAT_DATE]) AS [Year], dbo_SYS_INFO.SYS_NME,
dbo_ASMT_SIZE.ASMT_SIZE, Count((Month([TEST_STAT_DATE]))) AS YTD,
Count(dbo_ASMT_SIZE.ASMT_SIZE) AS CountOfASMT_SIZE
FROM (dbo_SYS_INFO INNER JOIN dbo_ASMT_SIZE ON
dbo_SYS_INFO.ASMT_SIZE_ID=dbo_ASMT_SIZE.ASMT_SIZE_ID) INNER JOIN
dbo_TEST_STAT ON dbo_SYS_INFO.TEST_STAT_ID=dbo_TEST_STAT.TEST_STAT_ID
WHERE (((dbo_ASMT_SIZE.ASMT_SIZE)="FULL") AND
((dbo_TEST_STAT.TEST_STAT)="In Progress")) OR
(((dbo_TEST_STAT.TEST_STAT)="Scheduled")) OR
(((dbo_TEST_STAT.TEST_STAT)="To Be Scheduled")) OR
(((dbo_TEST_STAT.TEST_STAT)="In Que"))
GROUP BY Year([TEST_STAT_DATE]), dbo_SYS_INFO.SYS_NME,
dbo_ASMT_SIZE.ASMT_SIZE, dbo_TEST_STAT.TEST_STAT
PIVOT Month([TEST_STAT_DATE]);

 

[[MVP] S.Clark]

Having just completed a multi-chart app, I'm feeling my oats on this one.

First, Access reports are very limited, compared to what you can do in
Excel. In Excel, you can define series ranges for the charts. If your data
is rather finite, it makes it very easy to export the data from an Access
Query, then build the chart around it. Record a macro in Excel while
building the chart, and it will write all of the code for you.

It may require further scrubbing the data to make it ready for graphing.
For example, merging two columns together to show something useful in the
legend.
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hi Freinds I have a huge issue hoping that if anyone can resolve it. Is
there any way i can merge both chart in one chart. I only want one
chart with two bars for each month. On bar represent all number of
system with Completed status, and other bar represent the rest of the
system in que.
I created two sperate queries to achieve this goal but i want to have
only one chart. I tried to do the Union query with both of these
queries but it didnt work and it is giving me syntax errors.


I have a chart based on the following cross tab query.
TRANSFORM Count(dbo_SYS_INFO.SYS_NME) AS CountOfSYS_NME
SELECT Year([TEST_END_DATE]) AS [Year], dbo_SYS_INFO.SYS_NME,
dbo_ASMT_SIZE.ASMT_SIZE, Count((Month([TEST_END_DATE]))) AS YTD,
Count(dbo_ASMT_SIZE.ASMT_SIZE) AS CountOfASMT_SIZE
FROM (dbo_SYS_INFO INNER JOIN dbo_ASMT_SIZE ON
dbo_SYS_INFO.ASMT_SIZE_ID = dbo_ASMT_SIZE.ASMT_SIZE_ID) INNER JOIN
dbo_TEST_STAT ON dbo_SYS_INFO.TEST_STAT_ID = dbo_TEST_STAT.TEST_STAT_ID
WHERE (((dbo_ASMT_SIZE.ASMT_SIZE)="FULL") AND
((dbo_TEST_STAT.TEST_STAT)="Completed"))
GROUP BY Year([TEST_END_DATE]), dbo_SYS_INFO.SYS_NME,
dbo_ASMT_SIZE.ASMT_SIZE, dbo_TEST_STAT.TEST_STAT
PIVOT Month([TEST_STAT_DATE]);

I also have another chart based on the following query
TRANSFORM Count(dbo_SYS_INFO.SYS_NME) AS CountOfSYS_NME
SELECT Year([TEST_STAT_DATE]) AS [Year], dbo_SYS_INFO.SYS_NME,
dbo_ASMT_SIZE.ASMT_SIZE, Count((Month([TEST_STAT_DATE]))) AS YTD,
Count(dbo_ASMT_SIZE.ASMT_SIZE) AS CountOfASMT_SIZE
FROM (dbo_SYS_INFO INNER JOIN dbo_ASMT_SIZE ON
dbo_SYS_INFO.ASMT_SIZE_ID=dbo_ASMT_SIZE.ASMT_SIZE_ID) INNER JOIN
dbo_TEST_STAT ON dbo_SYS_INFO.TEST_STAT_ID=dbo_TEST_STAT.TEST_STAT_ID
WHERE (((dbo_ASMT_SIZE.ASMT_SIZE)="FULL") AND
((dbo_TEST_STAT.TEST_STAT)="In Progress")) OR
(((dbo_TEST_STAT.TEST_STAT)="Scheduled")) OR
(((dbo_TEST_STAT.TEST_STAT)="To Be Scheduled")) OR
(((dbo_TEST_STAT.TEST_STAT)="In Que"))
GROUP BY Year([TEST_STAT_DATE]), dbo_SYS_INFO.SYS_NME,
dbo_ASMT_SIZE.ASMT_SIZE, dbo_TEST_STAT.TEST_STAT
PIVOT Month([TEST_STAT_DATE]);