Union Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two or more tables (example):

Table 1:
Date Time Data1 Data2
17.04.2007 12.20 10
18.04.2007 10:30 15 20

Table 2:
Date Time Data1 Data2
18.04.2007 10:30 25 50
19.04.2007 13.30 30

----------------------------------------------------------------
I need result:
Date Time Data1 Data2 Data1 Data2
17.04.2007 12.20 10
18.04.2007 10:30 15 20 25 50
19.04.2007 13.30 30
 
SELECT Table1.[Date], Table1.Data1, Table1.Data2, Table2.Data1, Table2.Data2
FROM Table1 LEFT JOIN Table2
ON Table1.[Date] = Table2.[Date]
UNION
SELECT Table2.[Date], Table1.Data1, Table1.Data2, Table2.Data1, Table2.Data2
FROM Table2 LEFT JOIN Table1
ON Table1.[Date] = Table2.[Date]


Note that Date is a bad choice of field name: it's a reserved word, and
using reserved words for your own purposes can lead to problems. If you
cannot (or will not) change it, at least enclose it in square brackets, as
above.

Note, too, that the 4th and 5th columns will be likely be named Expr0000 and
Expr0001 unless you alias them (using the AS keyword)
 
Hello.

Douglas, could you please give a help here? I also need something like this,
with little diferent. I have two similar/equal tables. the
00_tbl_Badge_Actual table has the currenter recordset/register and the
01_tbl_Badge_Historico has the history registers.

What I need is to build in chronological order the registers. The column
with the date is MicroDate and the Employee number is EmployeeNumber

What I need it something like this:

For employee number 10

EmployeeNumber Microdate
10 01-02-2003
10 02-02-2003

After this, I'll still have to to controll to ohter table from another
program. :(

Could you please help?

Regards in advanced,
Marco Silva



Douglas J. Steele said:
SELECT Table1.[Date], Table1.Data1, Table1.Data2, Table2.Data1, Table2.Data2
FROM Table1 LEFT JOIN Table2
ON Table1.[Date] = Table2.[Date]
UNION
SELECT Table2.[Date], Table1.Data1, Table1.Data2, Table2.Data1, Table2.Data2
FROM Table2 LEFT JOIN Table1
ON Table1.[Date] = Table2.[Date]


Note that Date is a bad choice of field name: it's a reserved word, and
using reserved words for your own purposes can lead to problems. If you
cannot (or will not) change it, at least enclose it in square brackets, as
above.

Note, too, that the 4th and 5th columns will be likely be named Expr0000 and
Expr0001 unless you alias them (using the AS keyword)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Igor G. said:
I have two or more tables (example):

Table 1:
Date Time Data1 Data2
17.04.2007 12.20 10
18.04.2007 10:30 15 20

Table 2:
Date Time Data1 Data2
18.04.2007 10:30 25 50
19.04.2007 13.30 30

----------------------------------------------------------------
I need result:
Date Time Data1 Data2 Data1 Data2
17.04.2007 12.20 10
18.04.2007 10:30 15 20 25 50
19.04.2007 13.30 30
Click to expand...
Click to expand...
 
Perhaps what you want is the following

SELECT EmployeeNumber, MicroDate
FROM 00_tbl_Badge_Actual
UNION
SELECT EmployeeNumber, MicroDate
FROM 01_tbl_Badge_Historico
ORDER BY MicroDate

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

Marco said:
Hello.

Douglas, could you please give a help here? I also need something like
this,
with little diferent. I have two similar/equal tables. the
00_tbl_Badge_Actual table has the currenter recordset/register and the
01_tbl_Badge_Historico has the history registers.

What I need is to build in chronological order the registers. The column
with the date is MicroDate and the Employee number is EmployeeNumber

What I need it something like this:

For employee number 10

EmployeeNumber Microdate
10 01-02-2003
10 02-02-2003

After this, I'll still have to to controll to ohter table from another
program. :(

Could you please help?

Regards in advanced,
Marco Silva



Douglas J. Steele said:
SELECT Table1.[Date], Table1.Data1, Table1.Data2, Table2.Data1,
Table2.Data2
FROM Table1 LEFT JOIN Table2
ON Table1.[Date] = Table2.[Date]
UNION
SELECT Table2.[Date], Table1.Data1, Table1.Data2, Table2.Data1,
Table2.Data2
FROM Table2 LEFT JOIN Table1
ON Table1.[Date] = Table2.[Date]


Note that Date is a bad choice of field name: it's a reserved word, and
using reserved words for your own purposes can lead to problems. If you
cannot (or will not) change it, at least enclose it in square brackets,
as
above.

Note, too, that the 4th and 5th columns will be likely be named Expr0000
and
Expr0001 unless you alias them (using the AS keyword)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Igor G. said:
I have two or more tables (example):

Table 1:
Date Time Data1 Data2
17.04.2007 12.20 10
18.04.2007 10:30 15 20

Table 2:
Date Time Data1 Data2
18.04.2007 10:30 25 50
19.04.2007 13.30 30

----------------------------------------------------------------
I need result:
Date Time Data1 Data2 Data1 Data2
17.04.2007 12.20 10
18.04.2007 10:30 15 20 25 50
19.04.2007 13.30 30
Click to expand...
Click to expand...
Click to expand...
 
Hi.

Yes, thanks, it was it.

Regards,
Marco

John Spencer said:
Perhaps what you want is the following

SELECT EmployeeNumber, MicroDate
FROM 00_tbl_Badge_Actual
UNION
SELECT EmployeeNumber, MicroDate
FROM 01_tbl_Badge_Historico
ORDER BY MicroDate

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

Marco said:
Hello.

Douglas, could you please give a help here? I also need something like
this,
with little diferent. I have two similar/equal tables. the
00_tbl_Badge_Actual table has the currenter recordset/register and the
01_tbl_Badge_Historico has the history registers.

What I need is to build in chronological order the registers. The column
with the date is MicroDate and the Employee number is EmployeeNumber

What I need it something like this:

For employee number 10

EmployeeNumber Microdate
10 01-02-2003
10 02-02-2003

After this, I'll still have to to controll to ohter table from another
program. :(

Could you please help?

Regards in advanced,
Marco Silva



Douglas J. Steele said:
SELECT Table1.[Date], Table1.Data1, Table1.Data2, Table2.Data1,
Table2.Data2
FROM Table1 LEFT JOIN Table2
ON Table1.[Date] = Table2.[Date]
UNION
SELECT Table2.[Date], Table1.Data1, Table1.Data2, Table2.Data1,
Table2.Data2
FROM Table2 LEFT JOIN Table1
ON Table1.[Date] = Table2.[Date]


Note that Date is a bad choice of field name: it's a reserved word, and
using reserved words for your own purposes can lead to problems. If you
cannot (or will not) change it, at least enclose it in square brackets,
as
above.

Note, too, that the 4th and 5th columns will be likely be named Expr0000
and
Expr0001 unless you alias them (using the AS keyword)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two or more tables (example):

Table 1:
Date Time Data1 Data2
17.04.2007 12.20 10
18.04.2007 10:30 15 20

Table 2:
Date Time Data1 Data2
18.04.2007 10:30 25 50
19.04.2007 13.30 30

----------------------------------------------------------------
I need result:
Date Time Data1 Data2 Data1 Data2
17.04.2007 12.20 10
18.04.2007 10:30 15 20 25 50
19.04.2007 13.30 30
Click to expand...
Click to expand...
Click to expand...
 
Back
Top