Learning to write SQL-joining 2 queries 3

[kentwoodjean]

While watching a great UM/MSU football game, I have also been trying to better understand and learn how to write SQL statements when joining queries. I am using the same database I got assistance on eariler this week but now I am doing this for learning purposes. I have 2 queries I would like to join into one. Following are the 2 SQL statements:

SELECT Count(tblLastUpdated.Date1) AS CountOfDate1, tblLastUpdated.Date1, tblLastUpdated.[Resp Cd]
FROM tblLastUpdated
GROUP BY tblLastUpdated.Date1, tblLastUpdated.[Resp Cd];

SELECT Count(tblClosedCases.[Closed Dt]) AS [CountOfClosed Dt], tblClosedCases.[Closed Dt], tblClosedCases.[Resp Cd]
FROM tblClosedCases
GROUP BY tblClosedCases.[Closed Dt], tblClosedCases.[Resp Cd];

Since the date is how I want these files sorted and the 2 date fields are not equal, I guess I cannot use a Union query. So, can an SQL statement be created that will join these 2 queries? The closed date and Date1 will need to be equal so there is only one date field. It is possible that there could be a date where there are only closes [represented by CountOfClosedDt or only receipts represented by CountOfDate1. These records should be grouped by Resp Cd. Final result would look like:

Date CountOfDate1 CountOfClosedDt RespCd

 

[PHV]

Something like this ?
SELECT U.Date, Sum(C1) AS CountOfDate1, Sum(C2) AS CountOfClosedDt, RespCd
FROM (
SELECT Count(Date1) AS C1, 0 AS C2, Date1 AS [Date], [Resp Cd] As RespCd
FROM tblLastUpdated GROUP BY Date1, [Resp Cd]
UNION SELECT 0, Count([Closed Dt]), [Closed Dt], [Resp Cd]
FROM tblClosedCases GROUP BY [Closed Dt], [Resp Cd]
) U
GROUP BY U.Date, RespCd

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

wasn't that a fabulous game??!!! triple overtime!

i thought the last 6 minutes of regulation was fantastic

i wonder which nfl team is gonna draft edwards next spring

oh, and phv? nice sum of a union, man

star for you

rudy

http://r937.com/

SQL Consulting

 

[kentwoodjean]

Yes Rudy, it was a fabulous game!! Edwards made a wise choice in coming back for his senior year. He increases his value week by week.

PHV - used your suggested SQl and the query runs just fine. Problem is that I cannot save it without getting that irritating MS Access message box (MS Access has encountered a problem and needs to close)and when it closes, my information is lost. Any idea what would be causing that? Also, to help me understand, why is there just a single ( with the first FROM statement. What is meant by that?

Thanks.

 

[RoyVidar]

Since it runs, but crashes on save, I suspect the crashing might be related to some corruption. I'd try creating a new database, importing all objects, then try again.

One ( in the from. I'm not entirely sure what you mean, but if you work your way from the "inner" statement, which is the union query, the parentheses surround that statement:

[tt]SELECT U.Date, Sum(C1) AS CountOfDate1, Sum(C2) AS CountOfClosedDt, RespCd
FROM ( <the union query> ) U
GROUP BY U.Date, RespCd[/tt]

- which means the "main select", selects from the results of the union query.

Roy-Vidar

 

[kentwoodjean]

Thanks for the clarification. I did open a new DB and imported all of the objects and it still crashes upon saving. I even did the compact/Repair to see if that would hep but to no avail. Everything else works fine within the database and I can create/save items with no problem. Minus the Union components this join query can be saved, linking by the Resp Cd. The result of the Union query is correct, but cannot saved. Below is exactly what I have keyed in as my SQL where I also put in a date parameter to make sure I was getting the right totals. With or without the parameter it will not save.

SELECT U.Date, Sum(U.C1) AS CountOfDate1, Sum(U.C2) AS CountOfClosedDt, U.RespCd
FROM [SELECT Count(Date1) AS C1, 0 AS C2, Date1 AS [Date], [Resp Cd] As RespCd
FROM tblLastUpdated GROUP BY Date1, [Resp Cd]
UNION SELECT 0, Count([Closed Dt]), [Closed Dt], [Resp Cd]
FROM tblClosedCases GROUP BY [Closed Dt], [Resp Cd]
]. AS U
GROUP BY U.Date, U.RespCd
HAVING (((U.Date) Between #10/18/2004# And #10/23/2004#));

Any other thoughts?

 

[kentwoodjean]

UPDATE: Some how or another while running the query it changed your format. Below is what I input and have done it again to get the crash results described above. Didn't want you to think that I changed it all around.

SELECT U.Date, Sum(C1) AS CountOfDate1, Sum(C2) AS CountOfClosedDt, RespCd
FROM (
SELECT Count(Date1) AS C1, 0 AS C2, Date1 AS [Date], [Resp Cd] As RespCd
FROM tblLastUpdated GROUP BY Date1, [Resp Cd]
UNION SELECT 0, Count([Closed Dt]), [Closed Dt], [Resp Cd]
FROM tblClosedCases GROUP BY [Closed Dt], [Resp Cd]
) U
GROUP BY U.Date, RespCd
HAVING (((U.Date) Between #10/18/2004# And #10/23/2004#));

 

[RoyVidar]

I don't know, by I'd try saving the union query as a separate query, then base a new query on that.

Roy-Vidar

 

[JonFer]

Try this:

SELECT U.Date1, U.RespCd,
Sum(U.C1) AS CountOfDate1, Sum(U.C2) AS CountOfClosedDt FROM (
SELECT Date1, [Resp Cd] As RespCd, Count(Date1) AS C1, 0 AS C2 
FROM tblLastUpdated GROUP BY Date1, [Resp Cd]
UNION SELECT [Closed Dt], [Resp Cd]
0, Count([Closed Dt]) 
FROM tblClosedCases GROUP BY [Closed Dt], [Resp Cd]
) U
GROUP BY U.Date1, U.RespCd
HAVING (((U.Date1) Between #10/18/2004# And #10/23/2004#));

I added the table alias to all fields, put the group by fields right after the SELECT, and changed the name of field "Date" to "Date1". If you still crash, remove the HAVING clause and see if that fixes it. If not, change the first Select to "Select * FROM ...". If it still crashes, the problem is in reading the Union results so simplify that next.




John

http://www.interautomateconsulting.com

 

[r937]

the HAVING condition should be a WHERE condition

SELECT * will not work with a GROUP BY query

that "square bracket with period" syntax is the way access stores a query containing a derived table, so don't be alarmed when you see it

rudy

http://r937.com/

SQL Consulting

 

[kentwoodjean]

Found a way to make the query work as well as save and thought I would share for future reference. Went to Microsoft.com Community Newsgroups and found 1 post with the same problem as mine. Used the steps indicated in the response and it works great now. Thanks again for your help and helping with the SQL. Here is the post:

Post 8/5/04

It may be related to the AutoCorrect feature. Go to the Database window,
click Tools | Options | General tab, and uncheck AutoCorrect option. That
option does all kinds of strange things to databases, and this may be one
more of the strange things it does.

Also be sure that you've installed all the latest patches. Office XP on
your PC should now be at SP3.

Ken Snell
<MS ACCESS MVP>

"Hieu Han" <anonymous@discussions.microsoft.com> wrote in message
news:0f9501c47b32$0c745bb0$a401280a@phx.gbl...
> I am trying to save a query in Access 2002, but instead of
> saving the query, Access 2002 closes instead. Has anyone
> gotten this error? Do you know how to fix it.