[AccessD] Learned Something Today

I thought they had to be the same name too. Chock another one up.
 
Va.

-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Monday, February 10, 2003 10:55 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Learned Something Today


Virginia,
 
In a union query, field order is everything.  The fields don't have to have
the same name, but they have to be the same datatype and in the same order.
 
Charlotte Foust

-----Original Message-----
From: Hollis,Virginia [mailto:HollisVJ at pgdp.usec.com] 
Sent: Saturday, February 08, 2003 6: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/20030210/5e985c7d/attachment-0002.html>