Geek Answers Handbook

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

I got this error in a 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 matches exactly, and when I run a UNION query that selects any 2 sets at a time, it works fine. When I include more than 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")
+4
source share
1 answer

I do not know the cause of your original problem, but you can rewrite your request to eliminate the joins:

 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 need 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
+1
source

More articles:


All Articles