Query with Multi Column view ( 8 columns datasheet view)

G

Guest

Dear All,

I have 3 tables below:


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 want to have different column for Task A, Task B & Task C(as they are
all independent things and can not be combined/add together),some thing
like the following "multicolumn(8 column)" format:

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

....an so on..

Instead of just have 2 columns ResultA ErrorA.


Would anyone please help to advise any solution?

Many thanks for your help,


PA
 
D

Duane Hookom

Add a column to your union query:

SELECT ID,Client,Date, ResultA as Result, ErrorA as Error, "A" as Task
FROM A
UNION
SELECT ID,Client,Date, ResultB, ErrorB ,"B"
FROM B
UNION
SELECT ID,Client,Date, ResultC, ErrorC ,"C"
FROM C;

Then you can create a crosstab query using [Task] as the column heading. If
you need two values, you can use [Result] & " - " & [Error] as the Value.
ID, Client, and [Date] would be the Row Headings.
 
G

Guest

Duane

Thanks for your help. It does help, but I need further help.


Duane or Anyone who may help as well( as Duane may be not on line at this
time),while I need to finalise this assignment as soon as practical

I have updated the Union Query per Duane guidance below, and got the result-
Query (called Query1) datasheet view as follows


ID Client Date Result Error Task
1 P 01-Jan-06 333 0.83 C
1 P 01-Jan-06 500 5.00 A
1 P 01-Jan-06 677 3.39 B
1 Q 04-Jan-06 358 3.58 A
1 Q 04-Jan-06 667 3.34 B
2 R 05-Jan-06 789 7.89 A
2 R 05-Jan-06 999 5.00 B
3 Q 03-Jan-06 233 1.17 B
5 R 02-Jan-06 222 0.56 C
6 Q 03-Jan-06 111 0.28 C


From this Query1 result, I try to create Cross tab query per
Duane's guidance as as well.
But I can not get the two values as I can not locate / find where in the SQL
statement /View that I can insert
-[Result] & " - " & [Error] as the Value.

Here is the the SQL View from Crosstab query that I got so far:

TRANSFORM Sum(Query1.Result) AS SumOfResult
SELECT Query1.ID, Query1.Client, Query1.Date, Sum(Query1.Result) AS [Total
Of Result]
FROM Query1
GROUP BY Query1.ID, Query1.Client, Query1.Date
PIVOT Query1.Task;

With Crosstabquery view as follows:

Query1_Crosstab
ID Client Date Total Of Result A B C
1 P 1/01/2006 1510 500 677 333
1 Q 4/01/2006 1025 358 667
2 R 5/01/2006 1788 789 999
3 Q 3/01/2006 233 233
5 R 2/01/2006 222 222
6 Q 3/01/2006 111 111


I want to get the multicolumn datasheet view result as follows:
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 0 0
Click to expand...


Would you please help to advise the modification of SQL statement(cross tab)
above so I can get the multicolumn datasheetview (8 columns)as above?


Many thanks for your help,

PA

=====================================================

Duane Hookom said:
Add a column to your union query:

SELECT ID,Client,Date, ResultA as Result, ErrorA as Error, "A" as Task
FROM A
UNION
SELECT ID,Client,Date, ResultB, ErrorB ,"B"
FROM B
UNION
SELECT ID,Client,Date, ResultC, ErrorC ,"C"
FROM C;

Then you can create a crosstab query using [Task] as the column heading. If
you need two values, you can use [Result] & " - " & [Error] as the Value.
ID, Client, and [Date] would be the Row Headings.


--
Duane Hookom
MS Access MVP
--

PA said:
Dear All,

I have 3 tables below:



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 want to have different column for Task A, Task B & Task C(as they are
all independent things and can not be combined/add together),some thing
like the following "multicolumn(8 column)" format:

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

...an so on..

Instead of just have 2 columns ResultA ErrorA.


Would anyone please help to advise any solution?

Many thanks for your help,


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

Duane Hookom

PA,
Please don't start a new thread with each reply.

--
Duane Hookom
MS Access MVP
--

PA said:
Duane

Thanks for your help. It does help, but I need further help.


Duane or Anyone who may help as well( as Duane may be not on line at this
time),while I need to finalise this assignment as soon as practical

I have updated the Union Query per Duane guidance below, and got the
result-
Query (called Query1) datasheet view as follows


ID Client Date Result Error Task
1 P 01-Jan-06 333 0.83 C
1 P 01-Jan-06 500 5.00 A
1 P 01-Jan-06 677 3.39 B
1 Q 04-Jan-06 358 3.58 A
1 Q 04-Jan-06 667 3.34 B
2 R 05-Jan-06 789 7.89 A
2 R 05-Jan-06 999 5.00 B
3 Q 03-Jan-06 233 1.17 B
5 R 02-Jan-06 222 0.56 C
6 Q 03-Jan-06 111 0.28 C


From this Query1 result, I try to create Cross tab query per
Duane's guidance as as well.
But I can not get the two values as I can not locate / find where in the
SQL
statement /View that I can insert
-[Result] & " - " & [Error] as the Value.

Here is the the SQL View from Crosstab query that I got so far:

TRANSFORM Sum(Query1.Result) AS SumOfResult
SELECT Query1.ID, Query1.Client, Query1.Date, Sum(Query1.Result) AS [Total
Of Result]
FROM Query1
GROUP BY Query1.ID, Query1.Client, Query1.Date
PIVOT Query1.Task;

With Crosstabquery view as follows:

Query1_Crosstab
ID Client Date Total Of Result A B C
1 P 1/01/2006 1510 500 677 333
1 Q 4/01/2006 1025 358 667
2 R 5/01/2006 1788 789 999
3 Q 3/01/2006 233 233
5 R 2/01/2006 222 222
6 Q 3/01/2006 111 111


I want to get the multicolumn datasheet view result as follows:
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 0
0
Click to expand...


Would you please help to advise the modification of SQL statement(cross
tab)
above so I can get the multicolumn datasheetview (8 columns)as above?


Many thanks for your help,

PA

=====================================================

Duane Hookom said:
Add a column to your union query:

SELECT ID,Client,Date, ResultA as Result, ErrorA as Error, "A" as Task
FROM A
UNION
SELECT ID,Client,Date, ResultB, ErrorB ,"B"
FROM B
UNION
SELECT ID,Client,Date, ResultC, ErrorC ,"C"
FROM C;

Then you can create a crosstab query using [Task] as the column heading.
If
you need two values, you can use [Result] & " - " & [Error] as the Value.
ID, Client, and [Date] would be the Row Headings.


--
Duane Hookom
MS Access MVP
--

PA said:
Dear All,

I have 3 tables below:

Table1 :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

Table2: 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

Table3 : 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 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 want to have different column for Task A, Task B & Task C(as they
are
all independent things and can not be combined/add together),some thing
like the following "multicolumn(8 column)" format:

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

...an so on..

Instead of just have 2 columns ResultA ErrorA.


Would anyone please help to advise any solution?

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

Cross Tab Query with two value=-[Result] & " - " & [Error] as the 8
Query to combine diff tables 6
Relative months In reports with cross tab query 1
Outlook crashes with Outlook connector 2002 0
Outlook connector crashes outlook 0
Runtime Error when cursor is on an html file in Windows Explorer 1
Multiple Users, Problem with 1 fao Allen Browne 1
K-Meleon Tab Missing ! 1

Top