[AccessD] Learned Something Today

Charlotte Foust cfoust at infostatsystems.com
Mon Feb 10 10:59:01 CST 2003


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/20aedc0a/attachment-0002.html>


More information about the AccessD mailing list