[AccessD] Learned Something Today

To which I would add, this is a risk of SELECT *. Downstream you may one day
decide to change the structure of one of the tables, inserting a new column.
This would break any UNION statement, since the tables would no longer
match. 

 

I have learned the hard way to avoid SELECT * like the plague. Ask for
exactly what you want, and ye shall get it :-)

 

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com] On Behalf Of Hollis,Virginia
Sent: February 8, 2003 9:46 AM
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] Learned Something Today

 

I learned something today that most of you probably already know, but just
in case I thought I would pass it along - especially since it took me 2 days
to figure out what I was doing wrong.

 

I created a Union query using SELECT * from both tables. The Union query
worked when I viewed it, but when I tried using it to create a report &
joined it to the evaluator table, I kept getting a type mismatch in join.
The main table had the EvaluatorID as a number & the Evaluator table had
EvaluatorID as autonumber (the tables were joined by EvaluatorID). So there
should not have been any problem. All the other joins worked fine.

 

So after eliminating all the possible combinations and trying different
tables I compared the 2 tables used in the Union to see if one of the fields
might have been wrong. Here is where the learning comes in! The table fields
have to be in the same Order. In one table I had EvaluatorID listed 3rd in
the list & the other it was 2nd.

 

Another way I found this is that I switched the order of the tables in the
query, SELECT * from table 1 UNION ALL table 2. When I did SELECT from table
2 UNION ALL table 1, I had text in the EvaluatorID field.

 

Just thought I would pass that along.

 

Virginia

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030208/0cd0fda2/attachment-0002.html>