Union Query is giving me problems please help (1 Viewer)

[amunafc]

Beloved Gurus

Attached is an MS Access database zipped one. This db has two table with few common fields and few uncommon ones. Please help me to come up with a Union Query for these tables, am desperate.

Regards


Amuna

 

[Alansidman]

You can't do a Union Query on these two tables. They must have the same number of fields and the fields must have the same format. What do you want to really do with these two tables. You've shown us what you have, now tell us what you are trying to do. Try to be specific.


Alan

 

[amunafc]

Let me go back and see how I can handle these tables them I will upload it again.. I just want a query that can put the data from both tables together,

 

[Alansidman]

There does not seem to be a unique common field to join them on. Is each record a project and have a project number that can be associated in both tables?

Alan

 

[pbaldy]

FWIW, you can union tables with differing fields using placeholders. Given one table with 2 fields and another with 3

SELECT Field1, Field2, Field3
FROM TableWith3
UNION ALL
SELECT Field1, Field2, Null As Field3
FROM TableWith2

In place of Null you could use 0, "", "None", or whatever is appropriate to your needs.

 

[Alansidman]

Paul;
I looked at the tables and they do not look like they relate in any fashion that a Union would be appropriate. I, of course, could be mistaken as it is not my db.

Alan

 

[pbaldy]

True Alan, but I mainly wanted to address the issue of whether tables have to have the same number of fields to be in a union query. In this case, it appears they don't have a common field for a tradition join, but they have several common fields that would seem to make a union query appropriate (ie to stack the data vertically, not join them horizontally). In other words:

SELECT Vote, Type, Program, Estimate
FROM tProgramOutputRecurrent
UNION ALL
SELECT Vote, Type, Program, Estimate
FROM tHistoricalRecords;

 

[Alansidman]

Paul;
We agree. Lets hope that the OP can get what he needs from this.
Alan