issue with crosstab query

[brickboy]

i have a passthrough query that pulls both text and numbers but when i try to
create a crosstab query i can only get one or the other is there any way to
get the crosstab to display both the data i am useing is coming from a SQL
query

 

[Jerry Whittle]

Show us the SQL of what comes close to working. Open the query in design
view. Next go to View, SQL View and copy and past it here. Example of the
data returend by the passthrough would be nice also.

 

[brickboy]

TRANSFORM Max(qryQCShiftReport.Value) AS MaxOfValue
SELECT qryQCShiftReport.Sample_ID
FROM qryQCShiftReport
GROUP BY qryQCShiftReport.Sample_ID
PIVOT qryQCShiftReport.VariableName;
there is another field i need called result it contains the text that i need


Sample_ID Result Value VariableName
10/1/2008 6:05:00 AM "Edgelift." frmQCSample.
txtComments
10/1/2008 6:05:00 AM 0 frmQCSample.txtEdgeLitesRight
10/1/2008 6:05:00 AM DAYS frmQCSample.cboShift
10/1/2008 6:05:00 AM 0 frmQCSample.txtZebraLeft
10/1/2008 6:05:00 AM 0 frmQCSample.txtZebraCenter
10/1/2008 6:05:00 AM 0.222 frmQCSample.txtAverageCutThickness
10/1/2008 6:05:00 AM 2.1555 frmQCSample.txtTrimLoss
10/1/2008 6:05:00 AM 3 frmQCSample.txtEdgeLitesLeft
10/1/2008 6:05:00 AM 0.4838 frmQCSample.txtProfileLoss
10/1/2008 6:05:00 AM 144 frmQCSample.txtCutWidth
10/1/2008 6:05:00 AM 153 frmQCSample.txtRibbonWidth
10/1/2008 6:05:00 AM 6.0 mmst1 frmQCSample.cboProduct
10/1/2008 6:05:00 AM 686.62 frmQCSample.txtTonnage
10/1/2008 6:05:00 AM 0 frmQCSample.txtEdgeLitesCenter
10/1/2008 6:05:00 AM MIRROR frmQCSample.cboQuality
10/1/2008 6:05:00 AM 147.25 frmQCSample.txtUsableGlassWidth
10/1/2008 6:05:00 AM 0 frmQCSample.txtZebraRight
10/1/2008 6:05:00 AM 0.223 frmQCSample.txtMaximumProfile
10/1/2008 6:05:00 AM 311 frmQCSample.txtLehrSpeed
10/1/2008 6:05:00 AM 0.22 frmQCSample.txtMinimumProfile



I hope this helps

 

[brickboy]

it came through messed up but the Result is all the text and the Values are
numbers it should look like this

Sample_ID Result Value VariableName
Date/Time Text Number Tag
name

 

[Jerry Whittle]

You can only have one thing transformed. In your SQL statement it's Value.
Therefore you can't get result up there directly.

I noticed that Result and Value seem to be exclusive in your example data.
If there is a Result, there's no value and visa versa. Maybe you could
combine those two fields together in a query then make a crosstab out of
them. Something like below:

ResultValue: Result & Value

You must use the ampersand (&) to join the two fields as the plus sign (+)
will mess up due to the null values.

 

[brickboy]

Thank you very much that works like a charm