TRANSFORM X AND Y

[Pato-chan]

Can a crosstab query have two values in its TRANSFORM statement? I am trying
to create a table that would have a structure that looks something like the
following:

EE Name Date of Occurrence Date of Occurrence

Hrs Worked/Units Produced Hrs Worked/ Units
Produced

11/21/07
11/22/07
Sara King 8 / 15 7 / 13
Bev Smith 5 / 9 10 / 19
Ryan Ringold 8 / 12 9 / 10
Peter Beam 4 / 6 12 / 20

I need to be able to see and edit both the numerator and denominator of the
"fraction." I don't want to calculate its value though.

Is this even possible in Access?

 

[Michel Walsh]

something like:


TRANSFORM LAST( [Hrs Worked] & " / " & [Units Produced])
SELECT [EE name]
FROM somewhere
GROUP BY [EE name]
PIVOT [Date of Occurence]



Sure, each 'cell' now holds a string, not two values.

You can also give a try to

TRANSFORM SUM( [Hrs Worked]) & " / " SUM( [Units Produced] )
SELECT ...


if there is more than one record per [date of occurence], per [EE name].





Vanderghast, Access MVP

 

[Michel Walsh]

You can't with the first solution because there is a simple principle:
there is only one value under a field. A string *is* one value, but TWO
numbers cannot be ONE value.

A simple way to get what you want by JOINing TWO crosstabs, on their common
[EE Name]. One of the crosstab produces pivot on dates and transform
SUM([Hrs Worked]) and the second crosstab pivot on dates and transform
SUM([Units Produced] ).



Vanderghast, Access MVP

So there's probably not a way to do this without making the cell contents
a
string....?

What about something like this instead:

EE Name Date of Occurrence Date of
Occurrence
Hrs Worked Units Produced Hrs Worked
Units Produced
11/21/07 11/21/07 11/22/07
11/22/07
Sara King 8 15
7
13
Bev Smith 5 9
10
19
Ryan Ringold 8 12 9
10
Peter Beam 4 6 12
20

something like:


TRANSFORM LAST( [Hrs Worked] & " / " & [Units Produced])
SELECT [EE name]
FROM somewhere
GROUP BY [EE name]
PIVOT [Date of Occurence]



Sure, each 'cell' now holds a string, not two values.

You can also give a try to

TRANSFORM SUM( [Hrs Worked]) & " / " SUM( [Units Produced] )
SELECT ...


if there is more than one record per [date of occurence], per [EE name].





Vanderghast, Access MVP

Can a crosstab query have two values in its TRANSFORM statement? I am
trying
to create a table that would have a structure that looks something like
the
following:

EE Name Date of Occurrence Date of
Occurrence

Hrs Worked/Units Produced Hrs Worked/
Units
Produced

11/21/07
11/22/07
Sara King 8 / 15 7
/
13
Bev Smith 5 / 9 10
/
19
Ryan Ringold 8 / 12 9 /
10
Peter Beam 4 / 6 12
/
20

I need to be able to see and edit both the numerator and denominator of
the
"fraction." I don't want to calculate its value though.

Is this even possible in Access?