Query to combine diff tables

G

Guest

Dear All,

I have 5 tables as follows:

Table 1: ID Number
Table 2:Client Name
Table 3: Task A (with fields ID #,Client Name,Date, ResultA,ErrorA)
Table 4: Task B (with fields ID #,Client Name,Date, ResultB,ErrorB)
Table 5: Task C (with fields ID #,Client Name,Date, ResultC,ErrorC)


Table 1: ID TABLE 2:Client
ID Name
1 P
2 Q
3 R
4
5

Table3 :Task A
ID Client Date ResultA ErrorA
1 P 1/1/06 500 5
1 Q 4/1/06 358 3.58
2 R 5/1/06 789 7.89

Table4 : TaskB
ID Client Date ResultB ErrorB
1 P 1/1/06 677 3.39
3 Q 3/1/06 233 1.17
1 Q 4/1/06 667 3.34
2 R 5/1/06 999 5.00

Table5 : TaskC
ID Client Date ResultC ErrorC
1 P 1/1/06 333 0.83
5 R 2/1/06 222 0.56
6 Q 3/1/06 111 0.28




I try to build a query to combine those table together (especially table 3
to table 5), with result (something like this):

Combined-Query Result
ID Client Date ResultA ErrorA ResultB ErrorB ResultC ErrorC
1 P 1/1/06 500.0 5.0 677.0 3.4 333.0 0.8
5 R 2/1/06 0.0 0.0 0.0 0.0 222.0 0.6
3 Q 3/1/06 0.0 0.0 233.0 1.2 111.0 0.3
1 Q 4/1/06 358.0 3.6 667.0 3.3 0.0 0.0
2 R 5/1/06 789.0 7.9 999.0 5.0 0.0 0.0




Would anyone help me with some solution as usual? Is there any additional
query\table needed to combined those tables together?

Many thanks for your help,

PA
 
G

Guest

Julian,

That combined query is just on the spreadsheet..I try to build it, but so
far is not succesful.

Would you please help?

Many thanks
 
J

Julian

Have a look at:

UNION Operation
Creates a union query, which combines the results of two or more independent
queries or tables.

Syntax
query1 UNION [ALL]
query2 [UNION [ALL]
queryn [
.... ]]

The UNION operation has these parts:

Part Description
query1-n A SELECT statement, the name of a stored query, or the name
of a stored table preceded by the TABLE keyword.


Remarks
You can merge the results of two or more queries, tables, and SELECT
statements, in any combination, in a single UNION operation. The following
example merges an existing table named New Accounts and a SELECT statement:

TABLE [New Accounts] UNION ALL

SELECT *

FROM Customers

WHERE OrderAmount > 1000;


By default, no duplicate records are returned when you use a UNION
operation; however, you can include the ALL predicate to ensure that all
records are returned. This also makes the query run faster.

All queries in a UNION operation must request the same number of fields;
however, the fields do not have to be of the same size or data type.

Use aliases only in the first SELECT statement because they are ignored in
any others. In the ORDER BY clause, refer to fields by what they are called
in the first SELECT statement.
 
G

Guest

Many Thanks, Julian


Julian said:
Have a look at:

UNION Operation
Creates a union query, which combines the results of two or more independent
queries or tables.

Syntax
query1 UNION [ALL]
query2 [UNION [ALL]
queryn [
.... ]]

The UNION operation has these parts:

Part Description
query1-n A SELECT statement, the name of a stored query, or the name
of a stored table preceded by the TABLE keyword.


Remarks
You can merge the results of two or more queries, tables, and SELECT
statements, in any combination, in a single UNION operation. The following
example merges an existing table named New Accounts and a SELECT statement:

TABLE [New Accounts] UNION ALL

SELECT *

FROM Customers

WHERE OrderAmount > 1000;


By default, no duplicate records are returned when you use a UNION
operation; however, you can include the ALL predicate to ensure that all
records are returned. This also makes the query run faster.

All queries in a UNION operation must request the same number of fields;
however, the fields do not have to be of the same size or data type.

Use aliases only in the first SELECT statement because they are ignored in
any others. In the ORDER BY clause, refer to fields by what they are called
in the first SELECT statement.


--
|
+-- Julian
|


PA said:
Julian,

That combined query is just on the spreadsheet..I try to build it, but so
far is not succesful.

Would you please help?

Many thanks
Click to expand...
Click to expand...
 
G

Guest

Julian
Thanks for your tips for this Union Query, which new thing for me.

I have tried to use this union query, with the query statement (at SQL view
) as follows:

SELECT ID,Client,Date, ResultA, ErrorA FROM A
UNION
SELECT ID,Client,Date, ResultB, ErrorB FROM B
UNION
SELECT ID,Client,Date, ResultC, ErrorC FROM C

The result as datasheet view as follows:

Union Query Results
ID Client Date ResultA ErrorA
1 P 1/1/06 333 0.83 ==> for task C
1 P 1/1/06 500 5.00 ==> for task A
1 P 1/1/06 677 3.39 ==> for task B
1 Q 4/1/06 358 3.58
1 Q 4/1/06 667 3.34
2 R 5/1/06 789 7.89
2 R 5/1/06 999 5.00
3 Q 3/1/06 233 1.17
5 R 2/1/06 222 0.56
6 Q 3/1/06 111 0.28

If I wish to have different column for Task A, Task B & Task C,some thing
like the following "multicolumn" format:

ID Client Date ResultA ErrorA ResultB ErrorB ResultC ErrorC
1 P 1/1/06 500 5 677 3.39 333
0.83
1 Q 4/1/06 358 3.6 667 3.39 Null
Null
....an soo on..

Instead of just have 2 columns ResultA ErrorA.

Many thanks for your help,


Do you have any suggestion to get the multicolumn format as above?




PA said:
Many Thanks, Julian


Julian said:
Have a look at:

UNION Operation
Creates a union query, which combines the results of two or more independent
queries or tables.

Syntax
query1 UNION [ALL]
query2 [UNION [ALL]
queryn [
.... ]]

The UNION operation has these parts:

Part Description
query1-n A SELECT statement, the name of a stored query, or the name
of a stored table preceded by the TABLE keyword.


Remarks
You can merge the results of two or more queries, tables, and SELECT
statements, in any combination, in a single UNION operation. The following
example merges an existing table named New Accounts and a SELECT statement:

TABLE [New Accounts] UNION ALL

SELECT *

FROM Customers

WHERE OrderAmount > 1000;


By default, no duplicate records are returned when you use a UNION
operation; however, you can include the ALL predicate to ensure that all
records are returned. This also makes the query run faster.

All queries in a UNION operation must request the same number of fields;
however, the fields do not have to be of the same size or data type.

Use aliases only in the first SELECT statement because they are ignored in
any others. In the ORDER BY clause, refer to fields by what they are called
in the first SELECT statement.


--
|
+-- Julian
|


PA said:
Julian,

That combined query is just on the spreadsheet..I try to build it, but so
far is not succesful.

Would you please help?

Many thanks


:

Show us the query you used to get thus far.

Regards

Julian

Dear All,

I have 5 tables as follows:

Table 1: ID Number
Table 2:Client Name
Table 3: Task A (with fields ID #,Client Name,Date, ResultA,ErrorA)
Table 4: Task B (with fields ID #,Client Name,Date, ResultB,ErrorB)
Table 5: Task C (with fields ID #,Client Name,Date, ResultC,ErrorC)


Table 1: ID TABLE 2:Client
ID Name
1 P
2 Q
3 R
4
5

Table3 :Task A
ID Client Date ResultA ErrorA
1 P 1/1/06 500 5
1 Q 4/1/06 358 3.58
2 R 5/1/06 789 7.89

Table4 : TaskB
ID Client Date ResultB ErrorB
1 P 1/1/06 677 3.39
3 Q 3/1/06 233 1.17
1 Q 4/1/06 667 3.34
2 R 5/1/06 999 5.00

Table5 : TaskC
ID Client Date ResultC ErrorC
1 P 1/1/06 333 0.83
5 R 2/1/06 222 0.56
6 Q 3/1/06 111 0.28




I try to build a query to combine those table together (especially
table 3
to table 5), with result (something like this):

Combined-Query Result
ID Client Date ResultA ErrorA ResultB ErrorB ResultC ErrorC
1 P 1/1/06 500.0 5.0 677.0 3.4 333.0 0.8
5 R 2/1/06 0.0 0.0 0.0 0.0 222.0 0.6
3 Q 3/1/06 0.0 0.0 233.0 1.2 111.0 0.3
1 Q 4/1/06 358.0 3.6 667.0 3.3 0.0 0.0
2 R 5/1/06 789.0 7.9 999.0 5.0 0.0 0.0




Would anyone help me with some solution as usual? Is there any
additional
query\table needed to combined those tables together?

Many thanks for your help,

PA
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Julian
Thanks for your tips for this Union Query, which new thing for me.

I have tried to use this union query, with the query statement (at SQL view
) as follows:

SELECT ID,Client,Date, ResultA, ErrorA FROM A
UNION
SELECT ID,Client,Date, ResultB, ErrorB FROM B
UNION
SELECT ID,Client,Date, ResultC, ErrorC FROM C

The result as datasheet view as follows:

Union Query Results
ID Client Date ResultA ErrorA
1 P 1/1/06 333 0.83 ==> for task C
1 P 1/1/06 500 5.00 ==> for task A
1 P 1/1/06 677 3.39 ==> for task B
1 Q 4/1/06 358 3.58
1 Q 4/1/06 667 3.34
2 R 5/1/06 789 7.89
2 R 5/1/06 999 5.00
3 Q 3/1/06 233 1.17
5 R 2/1/06 222 0.56
6 Q 3/1/06 111 0.28

If I wish to have different column for Task A, Task B & Task C,some thing
like the following "multicolumn" format:

ID Client Date ResultA ErrorA ResultB ErrorB ResultC ErrorC
1 P 1/1/06 500 5 677 3.39 333
0.83
1 Q 4/1/06 358 3.6 667 3.39 Null
Null
....an soo on..

Instead of just have 2 columns ResultA ErrorA.

Many thanks for your help,


Do you have any suggestion to get the multicolumn format as above?




PA said:
Many Thanks, Julian


Julian said:
Have a look at:

UNION Operation
Creates a union query, which combines the results of two or more independent
queries or tables.

Syntax
query1 UNION [ALL]
query2 [UNION [ALL]
queryn [
.... ]]

The UNION operation has these parts:

Part Description
query1-n A SELECT statement, the name of a stored query, or the name
of a stored table preceded by the TABLE keyword.


Remarks
You can merge the results of two or more queries, tables, and SELECT
statements, in any combination, in a single UNION operation. The following
example merges an existing table named New Accounts and a SELECT statement:

TABLE [New Accounts] UNION ALL

SELECT *

FROM Customers

WHERE OrderAmount > 1000;


By default, no duplicate records are returned when you use a UNION
operation; however, you can include the ALL predicate to ensure that all
records are returned. This also makes the query run faster.

All queries in a UNION operation must request the same number of fields;
however, the fields do not have to be of the same size or data type.

Use aliases only in the first SELECT statement because they are ignored in
any others. In the ORDER BY clause, refer to fields by what they are called
in the first SELECT statement.


--
|
+-- Julian
|


PA said:
Julian,

That combined query is just on the spreadsheet..I try to build it, but so
far is not succesful.

Would you please help?

Many thanks


:

Show us the query you used to get thus far.

Regards

Julian

Dear All,

I have 5 tables as follows:

Table 1: ID Number
Table 2:Client Name
Table 3: Task A (with fields ID #,Client Name,Date, ResultA,ErrorA)
Table 4: Task B (with fields ID #,Client Name,Date, ResultB,ErrorB)
Table 5: Task C (with fields ID #,Client Name,Date, ResultC,ErrorC)


Table 1: ID TABLE 2:Client
ID Name
1 P
2 Q
3 R
4
5

Table3 :Task A
ID Client Date ResultA ErrorA
1 P 1/1/06 500 5
1 Q 4/1/06 358 3.58
2 R 5/1/06 789 7.89

Table4 : TaskB
ID Client Date ResultB ErrorB
1 P 1/1/06 677 3.39
3 Q 3/1/06 233 1.17
1 Q 4/1/06 667 3.34
2 R 5/1/06 999 5.00

Table5 : TaskC
ID Client Date ResultC ErrorC
1 P 1/1/06 333 0.83
5 R 2/1/06 222 0.56
6 Q 3/1/06 111 0.28




I try to build a query to combine those table together (especially
table 3
to table 5), with result (something like this):

Combined-Query Result
ID Client Date ResultA ErrorA ResultB ErrorB ResultC ErrorC
1 P 1/1/06 500.0 5.0 677.0 3.4 333.0 0.8
5 R 2/1/06 0.0 0.0 0.0 0.0 222.0 0.6
3 Q 3/1/06 0.0 0.0 233.0 1.2 111.0 0.3
1 Q 4/1/06 358.0 3.6 667.0 3.3 0.0 0.0
2 R 5/1/06 789.0 7.9 999.0 5.0 0.0 0.0




Would anyone help me with some solution as usual? Is there any
additional
query\table needed to combined those tables together?

Many thanks for your help,

PA
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query with Multi Column view ( 8 columns datasheet view) 3
Cross Tab Query with two value=-[Result] & " - " & [Error] as the 8
Automatically create query for weeks worth of info 4
Scrabble Value calculation for Welsh words 0
Transpose rows to columns in Access. 3
Please help with converting from Sql to MS Access 2003 0
How to avoid Duplicate values .. but want the first value to added in group totals 0
URGENT!!! Query text & number values from the other tables 0

Top