Duane's suggestion of combining the [Result] and [Error] values into a
single string is a clever one, but you may want to make your column
headings be a bit more descriptive.
Since this appears to be a class project (and if so, I hope you're looking
carefully at the details, so you can do all this yourself later, on other
projects), let me back up a bit and suggest another way to collect your
data.
Going back to your original Tables, [Task A], [Task B], and [Task C] (and
in a real project, I hope you would come up with more suggestive names),
you can grab different fields from each Table in different parts of your
Union Query. As Duane Hookom, suggested, you would want to add a text
field to label each of these. You might even want to use 2 such fields,
one for the name of the Table and another for the name of the field, but
I'm suggesting a combined name, such as "ResultA" for the [Result] field
of Table [Task A].
(Incidentally, I would probably not want to maintain separate Tables with
such similar structures, either, unless I were forced to do so. I'd
probably combine them and add a field that identifies the source of each
record. But sometimes you don't have a choice about that.)
Anyway, the Union Query now has 6 SELECT parts, not 3, and each one
includes just one of either [Result] or [Error] values.
[Q_RawValues]
SELECT ID,Client,Date, ResultA as Value1, "ResultA" as Task
FROM [Task A]
UNION ALL
SELECT ID,Client,Date, ErrorA, "ErrorA"
FROM [Task A]
UNION ALL
SELECT ID,Client,Date, ResultB, "ResultB"
FROM [Task B]
UNION ALL
SELECT ID,Client,Date, ErrorB ,"ErrorB"
FROM [Task B]
UNION ALL
SELECT ID,Client,Date, ResultC, "ResultC"
FROM [Task C]
UNION ALL SELECT ID,Client,Date, ErrorC ,"ErrorC"
FROM [Task C]
ORDER BY Client, ID;
... and it looks like this:
[Q_RawValues]
ID Client Date Value1 Task
-- ------ -------- ------ --------
1 P 1/1/2006 3.39 ErrorB
1 P 1/1/2006 0.83 ErrorC
1 P 1/1/2006 5 ErrorA
1 P 1/1/2006 333 ResultC
1 P 1/1/2006 677 ResultB
1 P 1/1/2006 500 ResultA
1 Q 4/1/2006 358 ResultA
1 Q 4/1/2006 3.58 ErrorA
1 Q 4/1/2006 667 ResultB
1 Q 4/1/2006 3.34 ErrorB
3 Q 3/1/2006 1.17 ErrorB
3 Q 3/1/2006 233 ResultB
6 Q 3/1/2006 0.28 ErrorC
6 Q 3/1/2006 111 ResultC
2 R 5/1/2006 5 ErrorB
2 R 5/1/2006 7.89 ErrorA
2 R 5/1/2006 789 ResultA
2 R 5/1/2006 999 ResultB
5 R 2/1/2006 222 ResultC
5 R 2/1/2006 0.56 ErrorC
Notice that the [Task] field identifies the source of each datum. (Be
sure, as I did, that the [Task] values are all different from each other!)
These are now easy to stuff into a Crosstab Query. Be careful -- I'm
assuming that the values in the body of the Crosstab are unique. If they
aren't, you'll get just one of them. In a real-world project, you'd need
to do something to check that there really aren't any duplicates in your
dataset. (There's a "Find Duplicates" Query Wizard in Access that will
help you do that.)
In your Crosstab, you used the Sum() function. With unique values, that
would probably work, but it logically makes little sense -- why would you
want to see the sum of two [Error] values? Instead of that, I used the
First() function, which just chooses a random value from those available,
and of course there is only one to choose from (we hope). Last() would
work just as well.
The column heading labels you can specify in Query Design View by
right-clicking in the upper window, choosing Properties, and entering the
list of names into the "Column Headings" property. You don't have to list
them all, and you can specify the order in which you want them to appear
there.
[Q_MyXtab]
TRANSFORM First(Q_RawValues.Value1) AS FirstOfValue1
SELECT Q_RawValues.ID, Q_RawValues.Client,
Q_RawValues.Date
FROM Q_RawValues
GROUP BY Q_RawValues.ID, Q_RawValues.Client,
Q_RawValues.Date
ORDER BY Q_RawValues.ID, Q_RawValues.Client
PIVOT Q_RawValues.Task
In ("ResultA","ErrorA","ResultB","ErrorB","ResultC","ErrorC");
[Q_MyXtab]
ID Client Date Result Error Result Error Result Error
A A B B C C
-- ------ -------- ------ ----- ------ ----- ------ -----
1 P 1/1/2006 500 5 677 3.39 333 0.83
1 Q 4/1/2006 358 3.58 667 3.34
2 R 5/1/2006 789 7.89 999 5
3 Q 3/1/2006 233 1.17
5 R 2/1/2006 222 0.56
6 Q 3/1/2006 111 0.28
One advantage that Duane's suggestion of combining two values into one
text field, is that you could use text functions like Format() to specify
different numbers of decimal places for the different fields. You can't do
that in the version I'm showing you here -- one size fits all for the
TRANSFORM field, and I chose just a general number. So an error value of
"5.00" gets rendered here as just "5". But if you want to be fancy, use
Duane's design, and format the values to look really nice. You'd perhaps
still want to specify the column headings, instead of just letting Access
choose them for you.
Another way to do that (I'm leaving this as an exercise for you) is to
define another Query that gets its data from this Crosstab Query, and in
which you can specify the formatting for each of the fields.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
PA wrote:
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
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"
wrote: Click to show or hide original message or reply text.
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 --
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
