Using a Union All and Transform on memo field crashes Access

[Shane Holder]

Long story short:

Adding the All predicate to a union query causes the
dependant transform query to crash Access, a memo field is
the value in the transform.

Long story:
I generate 2 .mdb's with identical tables which I then
link through a "master" .mdb which uses a union query to
unify the tables and present a single view of the data.

Query for Union of Tables:

SELECT UDAValues.id_number, UDAValues.uda_name,
UDAValues.uda_value
FROM UDAValues
UNION SELECT UDAValues1.id_number, UDAValues1.uda_name,
UDAValues1.uda_value
FROM UDAValues1;

These tables represent User Definable Attributes which
need to be "transformed" so that the uda_name is a column
and the data can be joined with other tables on the
id_number, the uda_value is a memo.

Query for Transform:

TRANSFORM First(UDAUnion.uda_value) AS firstvalue
SELECT UDAUnion.id_number
FROM UDAUnion
GROUP BY UDAUnion.id_number
PIVOT UDAUnion.uda_name;

This worked beautifully until I realized that my data in
the "Unioned" table was being truncated to 256 characters,
a quick search on the Internet suggested adding the All
predicate to the query which resolved the truncation issue
but causes the transform query to crash Access.

Any suggestions on how to remedy this situation and get
all of the data in the value field returned?

(Just as an asside, the end-result data will be sucked
into an Excel spreadsheet with "Get External Data" so Jet
will be involved)

Thanks,
Shane

 

[Gary Walter]

Hi Shane,

What version of Access?

If Access 200x, I might try creating a temp table,
feed your union data into it,
then run transform on the temp table.

Crosstabs are pretty "fussy" on what they
start out with.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter

 

[Shane Holder]

What version of Access?

I'm using 2003.

If Access 200x, I might try creating a temp table,
feed your union data into it,
then run transform on the temp table.

I tried the following query:

SELECT UDAValues.id_number, UDAValues.uda_name,
UDAValues.uda_value Into TmpUDAValues
FROM UDAValues
UNION ALL SELECT UDAValues1.id_number,
UDAValues1.uda_name, UDAValues1.uda_value
FROM UDAValues1;

I also tried "Into TmpUDAValues" after both selects, no
joy.

But I get an error from Access stating that I can not use
an action query as a row source, which kind of makes sense
since a union query doesn't require the column data types
to be the same, only the number of columns. But in my
query the data types are the same.

Crosstabs are pretty "fussy" on what they
start out with.

Yeah, I'm figuring that out, but they are very cool.

Shane

 

[Shane Holder]

Ok, so I broke the query up into 2 queries, one as a
select into and the other as an insert into, a pain in the
butt, but it's a solution. Is there a better way to do
this? I really don't want to run 2 queries before I can
deliver the data to Excel.

Shane

 

[Ken Snell]

To use a union query as an action query, you create and save the union
query, then use the union query as the source table for the action query.
So, it sounds as if you've done it correctly.

 

[Gary Walter]

Hi Shane,

If I had a temp table called "UDAUnion"
with the 3 fields discussed earlier, this
might be one (simplified) way I would do it:

Private Sub cmdTransferToExcel_Click()
On Error GoTo Err_cmdTransferToExcel_Click

Dim strSQL As String
Dim varResponse As Variant
Dim strMsg As String
Dim strXLSFile As String
Dim strWorksheetName As String

strXLSFile = "C:\UDA.xls"
strWorksheetName = "UDA"

'clear temp table "UDAUnion"
CurrentDb.Execute "DELETE * FROM UDAUnion", dbFailOnError

'insert UDAValues
CurrentDb.Execute "INSERT INTO UDAUnion SELECT * FROM UDAValues", dbFailOnError

'insert UDAValues1
CurrentDb.Execute "INSERT INTO UDAUnion SELECT * FROM UDAValues1", dbFailOnError

'assume "qryxtabUDA" is following:
'strSQL = "TRANSFORM First(UDAUnion.uda_value) As firstvalue " _
& "SELECT UDAUnion.id_number " _
& "FROM UDAUnion " _
& "GROUP BY UDAUnion.id_number " _
& "PIVOT UDAUnion.uda_name;"

'transfer qryxtabUDA to strXLSFile.xls
strMsg = "Do you want to delete " & strXLSFile _
& " (if it exists) and transfer data to it?" _
& vbCrLf & strXLSFile & " must not be open!"
varResponse = MsgBox(strMsg, vbOKCancel)
If varResponse = vbOK Then
'delete it if it exists
If Len(Dir(strXLSFile)) > 0 Then
Kill strXLSFile
End If
strSQL = "SELECT * " _
& "INTO [Excel 8.0;database=" _
& strXLSFile & "]." & strWorksheetName _
& " FROM qryxtabUDA"
'Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
MsgBox "Have successfully transferred data to " & strXLSFile
Else
MsgBox "You chose to cancel data transfer to " & strXLSFile
End If

Exit_cmdTransferToExcel_Click:
Exit Sub

Err_cmdTransferToExcel_Click:
MsgBox Err.Description
Resume Exit_cmdTransferToExcel_Click
End Sub

So...that might be one *simplified* way.

The only stored query would be "qryxtabUDA"

It doesn't check that UDAUnion actually
has any records.

One might use a different way of assigning
xls filename and worksheet name.

Many variations....

Good luck,

Gary Walter