Need some help fixing my UNION statement

[Alicia]

Hello everyone based on the data, I created a union query which
produces this.

SELECT [MonthYear],[CheckWeek],[CheckCount], 0 As ClosedCount
FROM [OpenByDate]
UNION SELECT
[MonthYear],[CheckWeek], 0 AS OpenedCount,[CheckCount]
FROM [ClosedByDate]
ORDER BY [MonthYear], [CheckWeek];

MonthYear CheckWeek CheckCount ClosedCount
Apr 2003 Week: 3 1 0
Apr 2003 Week: 4 1 0
Apr 2004 Week: 1 1 0
Apr 2004 Week: 4 1 0
Apr 2004 Week: 5 1 0
Feb 2003 Week: 5 1 0
Jul 2003 Week: 2 0 1
Jul 2004 Week: 1 0 1
Jul 2004 Week: 2 0 3
Jul 2004 Week: 2 1 0
Jul 2004 Week: 3 1 0
Jun 2004 Week: 1 0 3
Jun 2004 Week: 1 6 0
Jun 2004 Week: 2 0 3
Jun 2004 Week: 2 6 0
Jun 2004 Week: 3 0 3
Jun 2004 Week: 4 0 7
Jun 2004 Week: 4 3 0
Jun 2004 Week: 5 4 0
May 2004 Week: 3 0 1
May 2004 Week: 3 1 0
May 2004 Week: 4 0 1
May 2004 Week: 4 1 0
May 2004 Week: 5 0 1
May 2004 Week: 5 5 0
Oct 2003 Week: 3 1 0

The data displayed above is correct but the weeks appear twice if they
occur in the same week. It doesn't put both opencount/closedcount
beside each other evenly. Also the Months are not in order. Do you
know what I could add for this. Thanks.

The two queries look like this:

ClosedByDate Query

SELECT Format(([Closure Date]),"mmm") & " " & Format(([Closure
Date]),"yyyy") AS MonthYear, "Week: " & GetWeekNumberIn Month([Closure
Date]) AS CheckWeek, Count(Closeddat es.[Closure Date]) AS CheckCount
FROM Closeddates
GROUP BY Year([Closure Date]), Month([Closure Date]), Format(([Closure
Date]),"mmm") & " " & Format(([Closure Date]),"yyyy"), "Week: " &
GetWeekNumberIn Month([Closure Date])
ORDER BY Year([Closure Date]), Month([Closure Date]);

OpenByDate Query

SELECT Format(([Opened Date]),"mmm") & " " & Format(([Opened
Date]),"yyyy") AS MonthYear, "Week: " & GetWeekNumberIn Month([Opened
Date]) AS CheckWeek, Count(Openeddat es.[Opened Date]) AS CheckCount
FROM Openeddates
GROUP BY Year([Opened Date]), Month([Opened Date]), Format(([Opened
Date]),"mmm") & " " & Format(([Opened Date]),"yyyy"), "Week: " &
GetWeekNumberIn Month([Opened Date])
ORDER BY Year([Opened Date]), Month([Opened Date]);

[Pieter Linden]

al******@hotmai l.com (Alicia) wrote in message news:<d3******* *************** ****@posting.go ogle.com>...

Hello everyone based on the data, I created a union query which
produces this.

SELECT [MonthYear],[CheckWeek],[CheckCount], 0 As ClosedCount
FROM [OpenByDate]
UNION SELECT
[MonthYear],[CheckWeek], 0 AS OpenedCount,[CheckCount]
FROM [ClosedByDate]
ORDER BY [MonthYear], [CheckWeek];

MonthYear CheckWeek CheckCount ClosedCount
Apr 2003 Week: 3 1 0
Apr 2003 Week: 4 1 0
Apr 2004 Week: 1 1 0
Apr 2004 Week: 4 1 0
Apr 2004 Week: 5 1 0
Feb 2003 Week: 5 1 0
Jul 2003 Week: 2 0 1
Jul 2004 Week: 1 0 1
Jul 2004 Week: 2 0 3
Jul 2004 Week: 2 1 0
Jul 2004 Week: 3 1 0

The data displayed above is correct but the weeks appear twice if they
occur in the same week. It doesn't put both opencount/closedcount
beside each other evenly.

Sounds like you want a Group By clause in your query and you don't
have one.

Also the Months are not in order.
Order by the Date field, not the alphabetic stuff, and then it will
work. Just don't show the date field in your output.

[James Fortune]

al******@hotmai l.com (Alicia) wrote in message news:<d3******* *************** ****@posting.go ogle.com>...

Hello everyone based on the data, I created a union query which
produces this.

SELECT [MonthYear],[CheckWeek],[CheckCount], 0 As ClosedCount
FROM [OpenByDate]
UNION SELECT
[MonthYear],[CheckWeek], 0 AS OpenedCount,[CheckCount]
FROM [ClosedByDate]
ORDER BY [MonthYear], [CheckWeek];

MonthYear CheckWeek CheckCount ClosedCount
Apr 2003 Week: 3 1 0
Apr 2003 Week: 4 1 0
Apr 2004 Week: 1 1 0
Apr 2004 Week: 4 1 0
Apr 2004 Week: 5 1 0
Feb 2003 Week: 5 1 0
Jul 2003 Week: 2 0 1
Jul 2004 Week: 1 0 1
Jul 2004 Week: 2 0 3
Jul 2004 Week: 2 1 0
Jul 2004 Week: 3 1 0
Jun 2004 Week: 1 0 3
Jun 2004 Week: 1 6 0
Jun 2004 Week: 2 0 3
Jun 2004 Week: 2 6 0
Jun 2004 Week: 3 0 3
Jun 2004 Week: 4 0 7
Jun 2004 Week: 4 3 0
Jun 2004 Week: 5 4 0
May 2004 Week: 3 0 1
May 2004 Week: 3 1 0
May 2004 Week: 4 0 1
May 2004 Week: 4 1 0
May 2004 Week: 5 0 1
May 2004 Week: 5 5 0
Oct 2003 Week: 3 1 0

The data displayed above is correct but the weeks appear twice if they
occur in the same week. It doesn't put both opencount/closedcount
beside each other evenly. Also the Months are not in order. Do you
know what I could add for this. Thanks.

The two queries look like this:

ClosedByDate Query

SELECT Format(([Closure Date]),"mmm") & " " & Format(([Closure
Date]),"yyyy") AS MonthYear, "Week: " & GetWeekNumberIn Month([Closure
Date]) AS CheckWeek, Count(Closeddat es.[Closure Date]) AS CheckCount
FROM Closeddates
GROUP BY Year([Closure Date]), Month([Closure Date]), Format(([Closure
Date]),"mmm") & " " & Format(([Closure Date]),"yyyy"), "Week: " &
GetWeekNumberIn Month([Closure Date])
ORDER BY Year([Closure Date]), Month([Closure Date]);

OpenByDate Query

SELECT Format(([Opened Date]),"mmm") & " " & Format(([Opened
Date]),"yyyy") AS MonthYear, "Week: " & GetWeekNumberIn Month([Opened
Date]) AS CheckWeek, Count(Openeddat es.[Opened Date]) AS CheckCount
FROM Openeddates
GROUP BY Year([Opened Date]), Month([Opened Date]), Format(([Opened
Date]),"mmm") & " " & Format(([Opened Date]),"yyyy"), "Week: " &
GetWeekNumberIn Month([Opened Date])
ORDER BY Year([Opened Date]), Month([Opened Date]);

You could add theYear and theMonth {e.g., Year([Opened Date]) As
theYear} to your ClosedByDate and OpenByDate queries. Then you could
use those to help sort your results. The weeks appearing twice can be
handled by creating an intermediate table consisting of what you show
currently as results. I think you would 'Group By' theYear, theMonth
and CInt(Right([CheckWeek],1)) and SUM your two counts to combine
values from your auxiliary table. To get everything to print out
evenly, use an Access Report or use the Space() and Len() functions
together to pad spaces in the output where needed. The string 'week:'
can even be added at the 'output to file' step if required. Note that
if you have theYear and theMonth you can create a string that looks
like Oct 2003 by Format$([theMonth] & '/1/' & [theYear], 'mmm yyyy')
so your MonthYear field would not be needed until the final totals
query.

James A. Fortune

[Don Leverton]

Hi Alicia,

Well . I'm not very familiar with UNION queries either, so I couldn't get
the *exact* results you wanted.

Now everything works as it should, with the small (but irritating) exception
of not being able to combine the "OpenedCoun t" and "ClosedCoun t on a single
line. I even tried using the "Star Trek" . 2004.06 J date method to get the
union query to group on identical values. (i.e. Jun 2004, Week 1)

I also designed a couple of "Test" queries to see if the tally of
"OpenedCoun t / ClosedCount" matched the number of records returned by
criteria, and they do. 27 and 19, respectively.

So anyway . like I said . it's not perfect, but it does accomplish what (I
think) you want/need.

qryUnion

MonthYear
WeekNo
OpenedCount
ClosedCount
FirstDate

Apr 2003
Week: 3
1

2003.04

Jul 2003
Week: 2

1
2003.07

Oct 2003
Week: 3
1

2003.1

Apr 2004
Week: 4
1

2004.04

May 2004
Week: 4

1
2004.05

May 2004
Week: 4
1

2004.05

May 2004
Week: 5
4

2004.05

Jun 2004
Week: 1

1
2004.06

Jun 2004
Week: 1
5

2004.06

Jun 2004
Week: 2

3
2004.06

Jun 2004
Week: 2
5

2004.06

Jun 2004
Week: 3

3
2004.06

Jun 2004
Week: 4

6
2004.06

Jun 2004
Week: 4
3

2004.06

Jun 2004
Week: 5
4

2004.06

Jul 2004
Week: 1

1
2004.07

Jul 2004
Week: 2

3
2004.07

Jul 2004
Week: 2
1

2004.07

Jul 2004
Week: 3
1

2004.07


I began by deleting about 600 blank (junk) records from the sample data
table that you sent me. I was also getting confused by the "Queries based on
Queries" concept, so I deleted your existing ones and designed 2 new ones of
my own. (qryOpenedCount and qryClosureCount ) Both of these queries ignore
the Nulls, and calculate the "MonthYear" , "WeekNo" etc. I used "First" of
the dates involved, because "GroupBy" causes one record for each date.

These 2 queries seem to return the desired results individually, so I tried
to get the UNION thing to work using that.

Here is the SQL from these 2 queries, followed by the SQL for the UNION
query:

--------------------------------------------------

** qryClosureCount **

SELECT Format([Closure Date],"mmm yyyy") AS MonthYear, "Week: " &
GetWeekNumberIn Month([Closure Date]) AS WeekNo, Count(newAPARda ta.[Closure
Date]) AS ClosedCount, First(CDbl(Form at([Closure Date],"yyyy\.mm") )) AS
FirstDate

FROM newAPARdata

WHERE (((newAPARdata.[Closure Date]) Is Not Null))

GROUP BY Format([Closure Date],"mmm yyyy"), "Week: " &
GetWeekNumberIn Month([Closure Date])

ORDER BY First(CDbl(Form at([Closure Date],"yyyy\.mm") ));

--------------------------------------------------

** qryOpenedCount **

SELECT Format([Opened Date],"mmm yyyy") AS MonthYear, "Week: " &
GetWeekNumberIn Month([Opened Date]) AS WeekNo, Count(newAPARda ta.[Opened
Date]) AS OpenedCount, First(CDbl(Form at([Opened Date],"yyyy\.mm") )) AS
FirstDate

FROM newAPARdata

WHERE (((newAPARdata.[Opened Date]) Is Not Null))

GROUP BY Format([Opened Date],"mmm yyyy"), "Week: " &
GetWeekNumberIn Month([Opened Date])

ORDER BY First(CDbl(Form at([Opened Date],"yyyy\.mm") ));

--------------------------------------------------

** qryUnion **

SELECT MonthYear, WeekNo, OpenedCount, "" As ClosedCount, FirstDate

FROM qryOpenedCount

UNION SELECT ALL MonthYear, WeekNo, "" As OpenedCount, ClosedCount,
FirstDate

FROM qryClosureCount

GROUP BY MonthYear, WeekNo, ClosedCount, FirstDate

ORDER BY FirstDate;

--------------------------------------------------

I am also attaching a revised Alicia2.MDB (to the e-mail only)

Don

P.S.

CC'd to the newsgroup, in case someone else can either learn from, and/or
improve on this.

Hopefully I won't be chastised too hard for posting in HTML, which is
necessary in order to display the table.

-----Original Message-----
From: Alicia Keller LetsDeleteThis@ SpamMeNot

Sent: Sunday, July 18, 2004 9:44AM
To: LetsDeleteThis@ SpamMeNot
Subject: RE: Trying to combine Two queries into One Query.. Need Help!

[I wanted] both

APAROPENDATES and APARCLOSEDDATES to combine their data together into one

query with 4 columns.

Such as

APARCLOSEDQUERY

MonthYear CheckWeek CheckCount

Jul 2003 Week: 2 1

May 2004 Week: 4 1

Jun 2004 Week: 1 1

Jun 2004 Week: 2 2

Jun 2004 Week: 3 3

Jun 2004 Week: 4 6

Jul 2004 Week: 1 1

Jul 2004 Week: 2 2

and

APAROPENQUERY

MonthYear CheckWeek CheckCount

Apr 2003 Week: 3 1

Oct 2003 Week: 3 1

Apr 2004 Week: 4 1

May 2004 Week: 4 1

May 2004 Week: 5 3

Jun 2004 Week: 1 5

Jun 2004 Week: 2 4

Jun 2004 Week: 4 3

Jun 2004 Week: 5 3

Jul 2004 Week: 2 1

Jul 2004 Week: 3 1

to combine their data side by side. I guess that is where the UNION thought

came in.

like

MonthYear CheckWeek OpenCount ClosedCount

Jun 2004 Week: 1 5 1

Jun 2004 Week: 2 4 2

Jun 2004 Week: 3 blank or 0 3

Jun 2004 Week: 4 3 6

Jun 2004 Week: 5 3 a blank or 0

etc by month...

Thanks

<snip>

[Don Leverton]

> Hopefully I won't be chastised too hard for posting in HTML, which is

necessary in order to display the table.

Oh well, I guess I don't have to worry about that. I guess I have OE set up
to post as plain text. Hopefully everyone will be able to envision the table
anyway. :)

[Bob Quintal]

"Don Leverton" <le************ ****@telusplane t.net> wrote in
news:e21Lc.9958 1$eO.95853@edtn ps89:

Hi Alicia,

Well . I'm not very familiar with UNION queries either, so I
couldn't get the *exact* results you wanted.

Alicia and Don, you're sorta on the right track.
What you want to do is first create a union query with the date
information, then left join on summary queries to add the
numerical data.

qryMyUnion
SELECT Format(([Closure Date]),"mmm") & " "
& Format(([Closure Date]),"yyyy") AS MonthYear,
"Week: " & GetWeekNumberIn Month([Closure Date]) AS CheckWeek,
year([closure date]) & month([closure date]) &
GetWeekNumberIn Month([Closure Date]) AS SortKey
FROM Closeddates
UNION
SELECT Format(([Opened Date]),"mmm") & " "
& Format(([Opened Date]),"yyyy") AS MonthYear,
"Week: " & GetWeekNumberIn Month([Opened Date]) AS CheckWeek,
year([opened date]) & month([opened date]) & GetWeekNumberIn Month
([opened Date]) AS SortKey
FROM Openeddates;

then

SELECT qryMyUnion.[MonthYear],
qryMyUnion.[CheckWeek],
opencount+close dcount as [CheckCount],
Opencount,
ClosedCount
FROM qryMyUnion
LEFT JOIN [OpenByDate]
ON qryMyUnion.mont hyear = openbydate.mont hyear
AND qryMyUnion.week = openbydate.week
LEFT JOIN [ClosedByDate]
ON qryMyUnion.mont hyear = Closedbydate.mo nthyear
AND qryMyUnion.week = Closedbydate.we ek
ORDER BY SortKey;

--
Bob Quintal

PA is y I've altered my email address.