Combine 2 queries to produce one graph.. 1

[Webkins]

I am having difficulties to combine each of the 2 queries below. I want the final resulte to have 3 columns of data when executed, I think. #1 = weeknumber, #2 = SumOfOvertime2010 , #3 = SumOfOvertime2011. I have tried a union query but end up with only two columns of data. Keep in mind that 2010 has 52 weeks of data and that 2011 only has 19 weeks of data.

I am trying to create one bar graph in Access 2003 to compare the amount of overtime per year and week. Each of these queries works to create an individual report, but I want to display this data in a single report.

Thanks for any and all suggestions and assistance.

SELECT tbl_weekly_2010.WeekNumber, Sum(tbl_weekly_2010.Overtime) AS SumOfOvertime2010
FROM tbl_weekly_2010
GROUP BY tbl_weekly_2010.WeekNumber
HAVING (((tbl_weekly_2010.WeekNumber)>0));

SELECT tbl_weekly_2011.WeekNumber, Sum(tbl_weekly_2011.Overtime) AS SumOfOvertime2011
FROM tbl_weekly_2011
GROUP BY tbl_weekly_2011.WeekNumber
HAVING (((tbl_weekly_2011.WeekNumber)>0));

 

[Webkins]

This almost works. It gives me the 3 columns of data that I need with 52 weeks for 2010 and 19 weeks for 2011.

SELECT tbl_weekly_2010.WeekNumber, Sum(tbl_weekly_2010.Overtime) AS 2010_Overtime, Sum(tbl_weekly_2011.Overtime) AS 2011_Overtime
FROM tbl_weekly_2011 RIGHT JOIN tbl_weekly_2010 ON tbl_weekly_2011.WeekNumber = tbl_weekly_2010.WeekNumber
GROUP BY tbl_weekly_2010.WeekNumber;

But the overtime numbers are way far away from being correct. Week 1 should be: 2010 = 44 and 2011 = 79
I am getting Week 1 = 719 and 2011 = 841

 

[dhookom]

Create a union query first:

SELECT 2010 As YR, WeekNumber, Sum(Overtime) AS OT
FROM tbl_weekly_2010
WHERE WeekNumber > 0
GROUP BY WeekNumber
UNION ALL
SELECT 2011, WeekNumber, Sum(Overtime)
FROM tbl_weekly_2011
WHERE WeekNumber > 0
GROUP BY WeekNumber

Then create a crosstab query of the UNION query with Yr as the Column Heading, WeekNumber as the Row Heading, and Sum(OT) as the Value.

Duane
Hook'D on Access
MS Access MVP