The Union of 2 Queries

[cross5900]

I have a question regarding union queries, for the most part I understand them. You have to have the same amount of fields in both tables/queries, but what I do not know is do both tables/queries need to mimic each other 100%.

For example I have 2 Queries, both match each other identically in terms of fields and the order they are placed. What does not match is that I have one query with a "true" criteria under one field and the other query has a "true" in a seperate field.

When the query is ran I get "The number of colums in the two selected tables or queries of a union query do no match."

What is the purpose of the query, I am needing to combine these 2 queries into 1 for a report. If you need the code just ask and I shall post it.

Thanks ahead of time guys.

 

[pbaldy]

What is the SQL?

 

[Pat Hartman]

Picture a multi-lane highway. Picture an intersection. What would happen if the lanes on one side of the intersection didn't match exactly with the lanes on the otherside? With a union query, the "lanes" need to match exactly. If the lane on one side of the intersection is numeric, it must match up with a similarly described numeric field on the other side and so forth. It doesn't matter what you name the "lanes". Only their position and data type matter.

When you need to union two recordsets with similar but not identical structures, you need to make the structures identical by inserting dummy fields into the queries.

Select fldA, fldB, "somestring" AS dummyC, fldE From tbl1
Union Select fldA, fldB, fldC, 0 AS dummyE from tbl2;

tbl1 has E but not C and tbl2 has C but not E so you have to create "dummy" colums of the correct data type. Usually 0 for numeric fields and zero-length strings for text fields. A ZLS is represented as "". Null is also an option and it is represented as the word Null.