Trying to combine Two queries into One Query.. Need Help!

[Alicia]

Hi, I am trying to match two queries and make it into one query. I
have only been about to group similar attributes but nothing that
looks like the FinalQuery below. If anyone knows of a query that
would create the final result that would be great. Thanks in advance.

The FIRSTQUERY looks like this

Month Week OpenedCount
May Week: 3 3
May Week: 4 6
Jun Week: 1 4
Jun Week: 2 2
Jul Week: 3 4
The SECONDQUERY looks like this

Month Week ClosedCount
Apr Week: 1 2
Apr Week: 2 4
Oct Week: 4 3
Oct Week: 1 5
Dec Week: 3 4
Dec Week: 4 2
The FINAL RESULT I am looking for is

Month Week OpenedCount ClosedCount
Apr Week: 1 0 2
Apr Week: 2 0 4
May Week: 3 3 0
May Week: 4 6 0
Jun Week: 1 4 0
Jun Week: 2 2 0
Jul Week: 3 4 0
Oct Week: 4 0 3
Oct Week: 1 0 5
Dec Week: 3 0 4
Dec Week: 4 0 2

[Alicia]

Here is the SQL for the Open and Closed Queries, thanks in Advance.

ClosedQuery

SELECT Format(([Altered Date]),"mmm") & " " & Format(([Altered
Date]),"yyyy") AS MonthYear, "Week: " & GetWeekNumberInMonth([Altered
Date]) AS CheckWeek, Count(CLOSEDPMRDATES.[Altered Date]) AS OpenCount
FROM CLOSEDPMRDATES
GROUP BY Format(([Altered Date]),"mmm") & " " & Format(([Altered
Date]),"yyyy"), "Week: " & GetWeekNumberInMonth([Altered Date]),
Year([Altered Date]), Month([Altered Date])
ORDER BY Year([Altered Date]), Month([Altered Date]);

Represent this

MonthYear Week OpenCount
Apr Week: 1 2
Apr Week: 2 4
Oct Week: 4 3
Oct Week: 1 5
Dec Week: 3 4
Dec Week: 4 2
------------------
OpenQuery

SELECT Format(([Open Date]),"mmm") & " " & Format(([Open
Date]),"yyyy") AS MonthYear, "Week: " & GetWeekNumberInMonth([Open
Date]) AS CheckWeek, Count(OPENPMRDATES.[Open Date]) AS ClosedCount
FROM OPENDATES
GROUP BY Year([Open Date]), Month([Open Date]), Format(([Open
Date]),"mmm") & " " & Format(([Open Date]),"yyyy"), "Week: " &
GetWeekNumberInMonth([Open Date])
ORDER BY Year([Open Date]), Month([Open Date]);

Represents this

Month Week ClosedCount
May Week: 3 3
May Week: 4 6
Jun Week: 1 4
Jun Week: 2 2
Jul Week: 3 4
And I would like the final result to look like this:

Month Week OpenedCount ClosedCount
Apr Week: 1 0 2
Apr Week: 2 0 4
May Week: 3 3 0
May Week: 4 6 0
Jun Week: 1 4 0
Jun Week: 2 2 0
Jul Week: 3 4 0
Oct Week: 4 0 3
Oct Week: 1 0 5
Dec Week: 3 0 4
Dec Week: 4 0 2

[James Fortune]

al******@hotmail.com (Alicia) wrote in message news:<d3**************************@posting.google. com>...

Hi, I am trying to match two queries and make it into one query. I
have only been about to group similar attributes but nothing that
looks like the FinalQuery below. If anyone knows of a query that
would create the final result that would be great. Thanks in advance.

This is a hack, but it worked:

SELECT theMonth, theWeek, OpenedCount, 0 As ClosedCount,
Month([theMonth] & ' 1 2000') As MonthNum FROM FIRSTQUERY UNION SELECT
theMonth, theWeek, 0 AS OpenedCount, ClosedCount, Month([theMonth] & '
1 2000') As MonthNum FROM SECONDQUERY ORDER BY MonthNum, theWeek;

Note: I changed your Month and Week fields to theMonth and theWeek.

James A. Fortune

[James Fortune]

al******@hotmail.com (Alicia) wrote in message news:<d3**************************@posting.google. com>...

Here is the SQL for the Open and Closed Queries, thanks in Advance.

I see what you're trying to do now. You want to create a single SQL
statement that groups and sums information from your two base tables
directly into your final result table. Your first post neglected the
year information and gave no clue about any base table fields. I'd
say give it another try using a Union Query with matching field names
in the same order for both SELECT parts. If you still need help post
again including the SQL you tried. Also, it seems odd to include the
'Week: ' text inside the field.

James A. Fortune