Cross tab on query results error -1038

[NJP]

I'm having trouble with a crosstab query which I need to transpose to the
results of my initial query.

This is an example of my initial queries result
Key FieldName Psuedo
Results
11-74100 Event Date Event Date (mm/dd/yy): 7/10/06
11-74100 Event Time Event Time (e.g., 2315): 0900
11-74100 Summary Event Summary: #1 Hyper tripped

on HiHi Rod

Runout on #1

throw inboard

cylinder
11-74113 Comments Comments: << 7/22/2006

5:01:09 PM

**AutoUpdate*

All follow-up

actions are

complete.

Status changed

to 3 Closed.
11-74113 reli Reliability? (Y if yes): Y

Whay I nee it to look like
Key Event Date Event Time Summary Comments
reli
11-74100 7/10/06 0900 #1 Hyper tripped
on HiHi Rod
Runout on #1
throw inboard
cylinder
11-74113 : << 7/22/2006
5:01:09 PM Y

**AutoUpdate*

All follow-up

actions are

complete.

Status changed

to 3 Closed
Here's and example of the crosstab sql
TRANSFORM Max(qryParseMemo.results) AS MaxOfresults
SELECT qryParseMemo.KEY, Max(qryParseMemo.results) AS [Total Of results]
FROM qryParseMemo
GROUP BY qryParseMemo.KEY
PIVOT qryParseMemo.FieldName;


I need to transpose this data to then update a table

I need the key for the row and field for the colum and the text as the data.
I get a -1038 error. I know it's data related - If I get the original
query results small enough it works - but don't know what to do
about it, the data in the results field form the original query can be null
to memo"esque". Each Key has over 50 fields and this key is equivelent to a
record in a table of thousands in a years time.

If not possible, how about some some record set update to an existing table
that already has all as field names all of the possibilites under the
Original queries FieldName

 

[Michel Walsh]

Hi,


Not quite clear what the data looks like, but to "un-pivot" a table, in Jet,
you can use a sequence of UNION ALL :



SELECT key, #7/10/2006# As theDate, [7/10/06] As As TheEvent FROM myXTab
UNION ALL
SELECT key, #8/10/2006# , [8/10/06] FROM myXTab
UNION ALL
SELECT key, #9/10/2006# , [9/10/06] FROM myXTab
UNION ALL
....



Sure, you can add a WHERE clause, such as WHERE [7/10/06] NOT IS NULL, or
similar.


Hoping it may help,
Vanderghast, Access MVP

 

[NJP]

Michael - I knew my issue was data related, but did not realize I'd created
#Error fields in the results field (field to transform) for certain records -
it was a large dataset. I refined the expression to elinimate the errors,
and then the crosstab worked. Thanks for your effort on my behalf.
--
Nita J. Perez

Hi,


Not quite clear what the data looks like, but to "un-pivot" a table, in Jet,
you can use a sequence of UNION ALL :



SELECT key, #7/10/2006# As theDate, [7/10/06] As As TheEvent FROM myXTab
UNION ALL
SELECT key, #8/10/2006# , [8/10/06] FROM myXTab
UNION ALL
SELECT key, #9/10/2006# , [9/10/06] FROM myXTab
UNION ALL
....



Sure, you can add a WHERE clause, such as WHERE [7/10/06] NOT IS NULL, or
similar.


Hoping it may help,
Vanderghast, Access MVP

I'm having trouble with a crosstab query which I need to transpose to the
results of my initial query.

This is an example of my initial queries result
Key FieldName Psuedo
Results
11-74100 Event Date Event Date (mm/dd/yy): 7/10/06
11-74100 Event Time Event Time (e.g., 2315): 0900
11-74100 Summary Event Summary: #1 Hyper
tripped

on HiHi Rod

Runout on #1

throw inboard

cylinder
11-74113 Comments Comments: <<
7/22/2006

5:01:09 PM

**AutoUpdate*

All follow-up

actions are

complete.

Status changed

to 3 Closed.
11-74113 reli Reliability? (Y if yes):
Y

Whay I nee it to look like
Key Event Date Event Time Summary Comments
reli
11-74100 7/10/06 0900 #1 Hyper tripped
on HiHi Rod
Runout on
#1
throw
inboard
cylinder
11-74113 : << 7/22/2006
5:01:09 PM Y

**AutoUpdate*

All follow-up

actions are

complete.

Status changed

to 3 Closed
Here's and example of the crosstab sql
TRANSFORM Max(qryParseMemo.results) AS MaxOfresults
SELECT qryParseMemo.KEY, Max(qryParseMemo.results) AS [Total Of results]
FROM qryParseMemo
GROUP BY qryParseMemo.KEY
PIVOT qryParseMemo.FieldName;


I need to transpose this data to then update a table

I need the key for the row and field for the colum and the text as the
data.
I get a -1038 error. I know it's data related - If I get the original
query results small enough it works - but don't know what to do
about it, the data in the results field form the original query can be
null
to memo"esque". Each Key has over 50 fields and this key is equivelent to
a
record in a table of thousands in a years time.

If not possible, how about some some record set update to an existing
table
that already has all as field names all of the possibilites under the
Original queries FieldName