CODEWITHSUNDEEP
ms-accessms-access-2007union
Srinivas Raja
Srinivas Raja

Reputation: 51

MS Access error :the number of columns in the two selected tables does not match

I got this error in MS Access query : The number of columns in the two selected tables or queries of a union query do not match.

The number of columns match clearly and when I run the UNION query picking any 2 sets at a time, it works fine. When I include more that 2 select in my query it shows this error. 

SELECT  "Applied" as Application_Status, Count(*) AS [CountOfApplication Status]
FROM [EDB Applicants - ALL]
WHERE [EDB Applicants - ALL].[Application Year].Value Like "2012" And [EDB Applicants - ALL].[Application Date]<=Date()-366 And [EDB Applicants - ALL].[Dec Date]>Date()-366

UNION

SELECT  "Hold" as Application_Status, Count(*) AS [CountOfApplication Status]
FROM [EDB Applicants - ALL]
WHERE [EDB Applicants - ALL].[Application Year].Value Like "2012" And [EDB Applicants - ALL].[Application Date]<=Date()-366 And [EDB Applicants - ALL].[Dec Date]<=Date()-366 And [EDB Applicants - ALL].[Application Status]="Hold"

UNION

SELECT  "Withdraw" as Application_Status, Count(*) AS [CountOfApplication Status]
FROM [EDB Applicants - ALL]
WHERE [EDB Applicants - ALL].[Application Year].Value Like "2012" And [EDB Applicants - ALL].[Application Date]<=Date()-366 And [EDB Applicants - ALL].[Date of Enrollment Change]<=Date()-366 And [EDB Applicants - ALL].[Application Status] In ("Declined","Withdrew Application","Withdrew After Enrollment")

Upvotes: 5

Views: 1361

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I don't know the cause of your original problem, but you can rewrite your query to eliminate the unions:

select Application_Status, COUNT(*)
from (select (case when  [EDB Applicants - ALL].[Application Year].Value Like "2012" And [EDB Applicants - ALL].[Application Date]<=Date()-366 And [EDB Applicants - ALL].[Dec Date]>Date()-366
                   then 'Applied'
                   when [EDB Applicants - ALL].[Application Year].Value Like "2012" And [EDB Applicants - ALL].[Application Date]<=Date()-366 And [EDB Applicants - ALL].[Dec Date]<=Date()-366 And [EDB Applicants - ALL].[Application Status]="Hold"
                   then 'Hold'
                   when [EDB Applicants - ALL].[Application Year].Value Like "2012" And [EDB Applicants - ALL].[Application Date]<=Date()-366 And [EDB Applicants - ALL].[Date of Enrollment Change]<=Date()-366 And [EDB Applicants - ALL].[Application Status] In ("Declined","Withdrew Application","Withdrew After Enrollment")
                   then 'Withdraw'
              end) as Application_Status, [EDB Applicants - ALL].*
      from [EDB Applicants - ALL]
     ) t
where Application_Status is not null
group by Application_Status

That's right, in MS Access you have to use IIF(), but the same idea applies:

select Application_Status, COUNT(*)
from (select iif([EDB Applicants - ALL].[Application Year].Value Like "2012" And [EDB Applicants - ALL].[Application Date]<=Date()-366 And [EDB Applicants - ALL].[Dec Date]>Date()-366,
                  'Applied',
                  iif([EDB Applicants - ALL].[Application Year].Value Like "2012" And [EDB Applicants - ALL].[Application Date]<=Date()-366 And [EDB Applicants - ALL].[Dec Date]<=Date()-366 And [EDB Applicants - ALL].[Application Status]="Hold",
                       'Hold',
                       iif([EDB Applicants - ALL].[Application Year].Value Like "2012" And [EDB Applicants - ALL].[Application Date]<=Date()-366 And [EDB Applicants - ALL].[Date of Enrollment Change]<=Date()-366 And [EDB Applicants - ALL].[Application Status] In ("Declined","Withdrew Application","Withdrew After Enrollment")
                        'Withdraw', '')))) as Application_Status, [EDB Applicants - ALL].*
      from [EDB Applicants - ALL]
     ) t
where Application_Status <> ''
group by Application_Status

Upvotes: 1

Related Questions