UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Conditional Iif In Union Query Testing For Column Existence, Access 2016    
 
   
stephenmcnutt
post Apr 20 2017, 12:48 PM
Post#1



Posts: 2
Joined: 20-April 17



I have a UNION query to put together data from several linked Excel tables. These tables do NOT have identical columns, but I've found the following works. I'll simplify in my example here to show getting just three columns from only two Excel tables.


SELECT [Column1], [Column2], [Column3]
FROM Table1

UNION

SELECT [Column1], [Column2], '' AS [Column3]
FROM Table2;


Note the part that reads: '' AS [Column3] (those are two single quotation marks). In this case, Column3 doesn't exist in Table2. The UNION query runs fine.

Even this is a huge simplification over how I first tackled the problem of putting data from separate linked Excel tables together, which was to write Excel macros for the various Excel tables to make it so all the tables DID have identical columns. Then my UNION query in Access was simple, just using SELECT * for each table. Because inputting this data from Excel tables is something I have to do regularly, I found my UNION query above, with the '' AS inserted wherever a column doesn't exist in a given table, is much easier to maintain than the separate Excel macros for each table.

So my solution is good, but it would be so much better if I didn't have to pay attention to which columns in each table exist. Next week, the guy who makes these tables might HAVE the data for Column3 in Table2, so now that column DOES exist. No big deal, I just have to strip out the '' AS.

BUT if I could put in a conditional for EVERY column, I'd never have to check which columns exist in which tables. I mean something like the following.


SELECT IIF([Column1], [Column1], ''), IIF([Column2], [Column2], ''), IIF([Column3], [Column3], '')
FROM Table1

UNION

SELECT IIF([Column1], [Column1], ''), IIF([Column2], [Column2], ''), IIF([Column3], [Column3], '')
FROM Table2;


I've tried lots of variations on the IIF part with no success...

IIF(IsNull([Column3]), '', [Column3])
IIF(IsMissing([Column3]), '', [Column3])
IIF(IsError([Column3]), '', [Column3])
IIF(IsObject([Column3]), [Column3], '')

Is what I'm trying to do possible?
Go to the top of the page
 
ngins
post Apr 20 2017, 01:30 PM
Post#2



Posts: 300
Joined: 18-August 05
From: DFW, TX, USA


No, you can't do that. You can't refer to a field that doesn't exist and expect to get a Null or a False value if it doesn't exist. You just can't do that.

If you want an automated solution, you'd have to build your SQL in VBA, getting the tabledef of each table you want to evaluated, looping through the fields to see if a particular field exists, and then writing your SQL one way or the other based on whether or not the field exists.

Then, once your union query SQL is built, you can either apply to a form or report's recordsource, or you can set it as the SQL of a query by getting the query's querydef and modifying the SQL property with your built SQL.

That's all you can do in this matter.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
projecttoday
post Apr 20 2017, 01:35 PM
Post#3


UtterAccess VIP
Posts: 8,684
Joined: 10-February 04
From: South Charleston, WV


There are some other ways to approach this. You could require the people who create the spreadsheets to use the same format. You could read the field names off of row 1 of the spreadsheets. Or you could develop an Access form for them to use (the best way).

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
ngins
post Apr 20 2017, 02:13 PM
Post#4



Posts: 300
Joined: 18-August 05
From: DFW, TX, USA


Either way, it's still going to require a solution in VBA, not something that can be embedded into the union query itself.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
projecttoday
post Apr 20 2017, 02:23 PM
Post#5


UtterAccess VIP
Posts: 8,684
Joined: 10-February 04
From: South Charleston, WV


That is correct. A query cannot be made the way ngins suggested.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
ngins
post Apr 20 2017, 03:14 PM
Post#6



Posts: 300
Joined: 18-August 05
From: DFW, TX, USA


You mean "the way stephenmcnutt suggested" (I'm ngins). :-)

--------------------
Neil
Accessing since '96
Go to the top of the page
 
projecttoday
post Apr 20 2017, 07:54 PM
Post#7


UtterAccess VIP
Posts: 8,684
Joined: 10-February 04
From: South Charleston, WV


My apology. My head must be screwed on sideways today.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
ngins
post Apr 21 2017, 05:19 AM
Post#8



Posts: 300
Joined: 18-August 05
From: DFW, TX, USA


No problem! Hopefully you were able to remove it and get it screwed on the correct way today. I find that if you line up the little arrows on the neck, it helps to get the head into the proper groove when screwing it on. Just a tip from someone who's "been there, done that."

--------------------
Neil
Accessing since '96
Go to the top of the page
 
stephenmcnutt
post Apr 21 2017, 12:29 PM
Post#9



Posts: 2
Joined: 20-April 17



Thanks to both of you. I suspected it was impossible.
Go to the top of the page
 
ngins
post Apr 21 2017, 12:30 PM
Post#10



Posts: 300
Joined: 18-August 05
From: DFW, TX, USA


On the bright side, this is a good chance for you to learn some VBA! :-)

--------------------
Neil
Accessing since '96
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 08:53 AM