Union Query Problem 1

[houstonbill]

I have looked at multiple examples and postings and I still cannot get my Union Query to work. Below is what I have at the current time. Can someone please take a look at it and tell me what I am missing, or perhaps where I went wrong? When I run the query, I get the following error: "Syntax error in Union Query".

SELECT IIf(Weekday(Date())=2,Date()-3,Date()-1) AS [Date], Count(tblOpenCases.[Desk Cd]) AS Profess
FROM tblOpenCases
WHERE (((tblOpenCases.[Resp Cd]) Like "*1*" Or (tblOpenCases.[Resp Cd]) Like "*3*" Or (tblOpenCases.[Resp Cd]) Like "*5*" Or (tblOpenCases.[Resp Cd]) Like "*7*" Or (tblOpenCases.[Resp Cd]) Like "*9*"))
GROUP BY IIf(Weekday(Date())=2,Date()-3,Date()-1)
HAVING (((tblOpenCases.[Desk Cd])="00"))
UNION SELECT IIf(Weekday(Date())=2,Date()-3,Date()-1) AS [Date], Count(tblOpenCases.[Desk Cd]) AS Facility
FROM tblOpenCases
WHERE (((tblOpenCases.[Res Cd]) Like "*0*" Or (tblOpenCases.[Resp Cd]) Like "*2*" Or (tblOpenCases.[Resp Cd]) Like "*4*" Or (tblOpenCases.[Resp Cd]) Like "*6*" Or (tblOpenCases.[Resp Cd]) Like "*8*"))
GROUP BY IIf(Weekday(Date())=2,Date()-3,Date()-1)
HAVING (((tblOpenCases.[Desk Cd])="00");

 

[r937]

in the second subselect, you have a typo in the column name

WHERE (((tblOpenCases.[Res Cd])

should be

WHERE (((tblOpenCases.[Resp Cd])

however, you also have another problem, which is that you cannot tell from your query results which rows are profess and which rows are facility, because these counts would not have different column names (column names in a union result set are taken from the first SELECT only)

try this instead --

SELECT IIf(Weekday(Date())=2,Date()-3,Date()-1) AS [Date]
     , SUM(IIF(
       [Resp Cd] Like "*1*" 
    Or [Resp Cd] Like "*3*" 
    Or [Resp Cd] Like "*5*" 
    Or [Resp Cd] Like "*7*" 
    Or [Resp Cd] Like "*9*", 1,0))  AS Profess
     , SUM(IIF(
       [Resp Cd] Like "*0*" 
    Or [Resp Cd] Like "*2*" 
    Or [Resp Cd] Like "*4*" 
    Or [Resp Cd] Like "*6*" 
    Or [Resp Cd] Like "*8*", 1,0))  AS Facility
  FROM tblOpenCases
 WHERE [Desk Cd] = "00"
GROUP 
    BY IIf(Weekday(Date())=2,Date()-3,Date()-1)

r937.com | rudy.ca

 

[houstonbill]

I looked for mis spells forever and could not see it. Sometimes the 2nd set of eyes helps. Also, knowing that column names come from the 1st select statement makes a big difference. I did have to add in the Count of Desk Cd and this was my final result:


Date CountOfDesk Cd Facility Profess
12/21/2007 3566 1162 2404

Thanks so much for your help.

 

[PHV]

A simpler way:

SELECT IIf(Weekday(Date())=2,Date()-3,Date()-1) AS [Date]
, Count(*) AS [CountOfDesk Cd]
, Sum(II([Resp Cd] Like "*[02468]*",1,0)) AS Facility
, Sum(II([Resp Cd] Like "*[13579]*",1,0)) AS Profess
FROM tblOpenCases
WHERE [Desk Cd] = "00"
GROUP BY IIf(Weekday(Date())=2,Date()-3,Date()-1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

ah yes, the famous "short form" II function

just pokin' at ya, PHV

happy holidays

r937.com | rudy.ca

 

[houstonbill]

Although the query is working perfectly, I have run across a problem which has come to light as a result of our current holidays and figured I would raise the question now. I am re-designing my database for 2008 and trying to eliminate my Make Table Queries that provide me a history by date of the shelf numbers. I have found them to be somewhat inconsistant over the last year, particularily when the users did not open the database on a given date. That is what prompted the query in this post. The date portion in this query does not work when the holidays occur.
For example, if I run it today, the date that shows up is 12/25 but the totals are for the last work day of 12/21. If I try to put the query in this post along with my pre-populated tbl_date today (2007 work days minus holidays) and align the dates for reporting, I get no results. THat is because the pre-populated date today tbl correctly shows 12/21 but does not align with the 12/25 date. If I were to remove the date details from the query, how can I manage to set up the historical record without a make table query?

 

[r937]

i see only one table in the query, so where is this "pre-populated tbl_date" table of dates? what does it look like?

the calendar table concept is definitely the way to go here ...

r937.com | rudy.ca

 

[houstonbill]

Here is what the date table looks like. It is simple and straight forward.

DateToday
12/21/2007
12/20/2007
12/19/2007
12/18/2007
12/17/2007
12/14/2007
12/13/2007
12/12/2007
12/11/2007
12/10/2007

How can this be used in place of "IIf(Weekday(Date())=2,Date()-3,Date()-1) AS [Date]" and provide me the day to day history that I need?

 

[r937]

it is extremely straightforward

how are you marking the holidays? by their absence?

r937.com | rudy.ca

 

[houstonbill]

Yes, is that a problem?

 

[r937]

no, it's fine

i had not really stopped to think about that IIF expression for the date

why are you grouping on it?

it's almost as if you want the entire contents of the table summed/counted, but only have the latest date workdate displayed...

how does that relate to your problem "correctly shows 12/21 but does not align with the 12/25 date"?

r937.com | rudy.ca

 

[houstonbill]

This is the grouping that I used on my make table format. Other than holidays, it would correctly put my sum totals by the appropriate date. So, on a Monday, it would sum up what was open and set the date as the previous Friday. Tues through Friday, it would again group and sum but enter the previous days date on the table.

As I built reports and combined differnt types of data, I would utilize the tbl_Date Today and make sure that each date on that table would show and the same date on the other tables/queries I used would line up with it. All my data is based on the previous days results. Had Monday and Tuesday of this week been regular work days there would be no probelm. But when I ran it today on Wednesday, it picked up the previous days date as if it were a work day.

If I were to eliminate the IIF statement all together and run the query I will get my current totals based on previous business day results but I have no date to go with it and match up to other data for the same time period.

 

[r937]

so, if i could just confirm my understanding of this, you could run the same query without the IIF in the SELECT and without the GROUP BY altogether, and it would give you the correct sums, just no date to go with them?

r937.com | rudy.ca

 

[houstonbill]

Yes, you are correct. Here is the SQL minus the IIF and Group by:

SELECT Count(*) AS [CountOfDesk Cd], Sum(IIf([Resp Cd] Like "*[02468]*",1,0)) AS Facility, Sum(IIf([Resp Cd] Like "*[13579]*",1,0)) AS Profess
FROM tblOpenCases
WHERE (((tblOpenCases.[Desk Cd])="00"));

with the following results:

CountOfDesk Cd Facility Profess
3566 2404 1162

 

[r937]

okay, this should work...

select *
  from ( SELECT Count(*) AS [CountOfDesk Cd]
              , Sum(IIf([Resp Cd] Like '*[02468]*',1,0)) AS Facility
              , Sum(IIf([Resp Cd] Like '*[13579]*',1,0)) AS Profess
           FROM tblOpenCases
          WHERE [Desk Cd] = '00' ) AS x
     , ( SELECT Max(DateToday) as Latest_business_date
           FROM tbl_date
          WHERE DateToday < Date() ) AS y

r937.com | rudy.ca

 

[houstonbill]

You got it! now I will see how it goes over the next few days but I think that this will be just the ticket. I have a few more make table queries that I will convert using the same type of set up as you have here. I just knew there had to be a way to accomplish this but couldn't come up with the right combination.

Thanks so much for your help. This one post has taught me good lessons.