UNION Querie

[Marco]

Hello. I think my brain is not too good. Because I want to make a union
querie and something is not going to well.

I have table1 and table2

In Table1 I have this fields and data:
ID Body1 Cover1
1 1.50 1.30
2 1.50 1.70
3 1.80 1.60
4 1.25 1.40

and in Table2
ID Body2 Cover2
5 2.10 1.30
6 1.60 1.75
7 1.30 1.80
8 1.25 1.40

And I need to make a union querie to get this:
ID Body1 Cover1 Body2 Cover2
1 1.50 1.30
2 1.50 1.70
3 1.80 1.60
4 1.25 1.40
5 2.10 1.30
6 1.60 1.75
7 1.30 1.80
8 1.25 1.40

How can I do it?

Regards,
Marco

 

[David Cox]

Hello. I think my brain is not too good. Because I want to make a union
querie and something is not going to well.

I have table1 and table2

In Table1 I have this fields and data:
ID Body1 Cover1
1 1.50 1.30
2 1.50 1.70
3 1.80 1.60
4 1.25 1.40

and in Table2
ID Body2 Cover2
5 2.10 1.30
6 1.60 1.75
7 1.30 1.80
8 1.25 1.40

And I need to make a union querie to get this:
ID Body1 Cover1 Body2 Cover2
1 1.50 1.30
2 1.50 1.70
3 1.80 1.60
4 1.25 1.40
5 2.10 1.30
6 1.60 1.75
7 1.30 1.80
8 1.25 1.40

How can I do it?

Regards,
Marco

Without a computer at hand to try it on:-

put both tables into the query grid without linking them. select all of the
fields. In query properties, obtained by right clicking on the query window,
select unique records.

If I am thinking straight that will do it.

it is possible to do it with a Union query, you have to create two dummy
fields in each section of the query to create the null fields.

 

[John Spencer]

SELECT Body1, Cover1, IIF(True,Null,1.50) as Body2, IIF(True, Null,1.30) as
Cover2
FROM Table1
UNION
SELECT Null, Null, Body2, Cover2
FROM Table2


The reason for the IIF statements is to force the field type to numeric
instead of text.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marco]

Hello John,

This is want I want. There was an error. The ID 4 should be in under Body1
and Cover1.

What I want is a union between two other queries.

ID Body1 Cover1 Body2 Cover2
1 1.50 1.30
2 1.50 1.70
3 1.80 1.60
4 1.25 1.40
5 2.10 1.30
6 1.60 1.75
7 1.30 1.80
8 1.25 1.40

 

[John W. Vinson]

And I need to make a union querie to get this:
ID Body1 Cover1 Body2 Cover2
1 1.50 1.30
2 1.50 1.70
3 1.80 1.60
4 1.25 1.40
5 2.10 1.30
6 1.60 1.75
7 1.30 1.80
8 1.25 1.40

(edited per your later post)

This seems like a VERY odd thing to want to do. What do you want to see if
Table1 and Table2 both have records with ID 13?

That said...

SELECT Table1.ID, Table1.Body1, Table1.Cover1, IIf(True, Null, 1.00) AS Body2,
IIf(True, Null, 1.00) As Cover2 FROM Table1
UNION ALL
SELECT Table1.ID, IIf(True, Null, 1.00) AS Body1, IIf(True, Null, 1.00) As
Cover1, Table2.Body2, Table2.Cover2 FROM Table2;

John W. Vinson [MVP]

 

[Marco]

Hi. Why you use this condition: "IIf(True, Null, 1.00)"

Regards.
Marco

 

[John W. Vinson]

Hi. Why you use this condition: "IIf(True, Null, 1.00)"

It's a trick to include a Number/Double field in a UNION query with a NULL
value. The IIF() function will return a value of the datatype of any non-NULL
argument. In this case the second argument won't be returned, but it will
return a NULL Number value. If you just use

SELECT Field1, Field2, Null, Null

in one step of a UNION query, Access has no way to know the datatype of the
third and fourth fields, and it will give an error when you try to UNION it
with a field which does have data.

You can use

Iif (True, Null, CCur(0))

to get a Currency field, etc.

John W. Vinson [MVP]