By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,027 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,027 IT Pros & Developers. It's quick & easy.

Need some help fixing my UNION statement

P: n/a
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: " & GetWeekNumberInMonth([Closure
Date]) AS CheckWeek, Count(Closeddates.[Closure Date]) AS CheckCount
FROM Closeddates
GROUP BY Year([Closure Date]), Month([Closure Date]), Format(([Closure
Date]),"mmm") & " " & Format(([Closure Date]),"yyyy"), "Week: " &
GetWeekNumberInMonth([Closure Date])
ORDER BY Year([Closure Date]), Month([Closure Date]);

OpenByDate Query

SELECT Format(([Opened Date]),"mmm") & " " & Format(([Opened
Date]),"yyyy") AS MonthYear, "Week: " & GetWeekNumberInMonth([Opened
Date]) AS CheckWeek, Count(Openeddates.[Opened Date]) AS CheckCount
FROM Openeddates
GROUP BY Year([Opened Date]), Month([Opened Date]), Format(([Opened
Date]),"mmm") & " " & Format(([Opened Date]),"yyyy"), "Week: " &
GetWeekNumberInMonth([Opened Date])
ORDER BY Year([Opened Date]), Month([Opened Date]);
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
al******@hotmail.com (Alicia) wrote in message news:<d3**************************@posting.google. 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.
Nov 13 '05 #2

P: n/a
al******@hotmail.com (Alicia) wrote in message news:<d3**************************@posting.google. 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: " & GetWeekNumberInMonth([Closure
Date]) AS CheckWeek, Count(Closeddates.[Closure Date]) AS CheckCount
FROM Closeddates
GROUP BY Year([Closure Date]), Month([Closure Date]), Format(([Closure
Date]),"mmm") & " " & Format(([Closure Date]),"yyyy"), "Week: " &
GetWeekNumberInMonth([Closure Date])
ORDER BY Year([Closure Date]), Month([Closure Date]);

OpenByDate Query

SELECT Format(([Opened Date]),"mmm") & " " & Format(([Opened
Date]),"yyyy") AS MonthYear, "Week: " & GetWeekNumberInMonth([Opened
Date]) AS CheckWeek, Count(Openeddates.[Opened Date]) AS CheckCount
FROM Openeddates
GROUP BY Year([Opened Date]), Month([Opened Date]), Format(([Opened
Date]),"mmm") & " " & Format(([Opened Date]),"yyyy"), "Week: " &
GetWeekNumberInMonth([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
Nov 13 '05 #3

P: n/a
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 "OpenedCount" and "ClosedCount 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
"OpenedCount / 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: " &
GetWeekNumberInMonth([Closure Date]) AS WeekNo, Count(newAPARdata.[Closure
Date]) AS ClosedCount, First(CDbl(Format([Closure Date],"yyyy\.mm"))) AS
FirstDate

FROM newAPARdata

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

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

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

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

** qryOpenedCount **

SELECT Format([Opened Date],"mmm yyyy") AS MonthYear, "Week: " &
GetWeekNumberInMonth([Opened Date]) AS WeekNo, Count(newAPARdata.[Opened
Date]) AS OpenedCount, First(CDbl(Format([Opened Date],"yyyy\.mm"))) AS
FirstDate

FROM newAPARdata

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

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

ORDER BY First(CDbl(Format([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>
Nov 13 '05 #4

P: n/a
> 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. :)
Nov 13 '05 #5

P: n/a
"Don Leverton" <le****************@telusplanet.net> wrote in
news:e21Lc.99581$eO.95853@edtnps89:
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: " & GetWeekNumberInMonth([Closure Date]) AS CheckWeek,
year([closure date]) & month([closure date]) &
GetWeekNumberInMonth([Closure Date]) AS SortKey
FROM Closeddates
UNION
SELECT Format(([Opened Date]),"mmm") & " "
& Format(([Opened Date]),"yyyy") AS MonthYear,
"Week: " & GetWeekNumberInMonth([Opened Date]) AS CheckWeek,
year([opened date]) & month([opened date]) & GetWeekNumberInMonth
([opened Date]) AS SortKey
FROM Openeddates;

then

SELECT qryMyUnion.[MonthYear],
qryMyUnion.[CheckWeek],
opencount+closedcount as [CheckCount],
Opencount,
ClosedCount
FROM qryMyUnion
LEFT JOIN [OpenByDate]
ON qryMyUnion.monthyear = openbydate.monthyear
AND qryMyUnion.week = openbydate.week
LEFT JOIN [ClosedByDate]
ON qryMyUnion.monthyear = Closedbydate.monthyear
AND qryMyUnion.week = Closedbydate.week
ORDER BY SortKey;

--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.