export to excel in vertical format

[mcarlyle via AccessMonster.com]

OK I read through every post I can find; however, I do not believe anyone
else has tried to do what I am doing. I need to export records with more
than 255 fields to excel. To do this it has to be vertical. I am merging 3
tables into one export file.

I need to send data that is all tied with one ID field from 3 tables. The
trick is I need to send it out vertically instead of the normal horizontal
fashion.

Example

Table 1
ID FirstName LastName Address City State Zip ...........

Table 2
ID CreditorName AccountNumber Balance

Table 3
ID Note Date Rep

I would like it to output like this

IDFirstName John
LastName
Address
City
State
Zip
.. (lots of other fields)
.. (lots of other fields)
.. (lots of other fields)
CreditorName
AccountNumber
Balance
Note
Date
Rep

I tried to create a cross tab query to do this;however, it will not handle
the number of rows I have. Also it counts the data instead of listing it

 

[mcarlyle via AccessMonster.com]

Well as I was editing the post it submitted it... so I will try to finish
editing on this post


OK I read through every post I can find; however, I do not believe anyone
else has tried to do what I am doing. I need to export records with more
than 255 fields to excel. To do this it has to be vertical. I am merging 3
tables into one export file.
I need to send data that is all tied with one ID field from 3 tables. The
trick is I need to send it out vertically instead of the normal horizontal
fashion.

Example

Table 1
ID FirstName LastName Address City State Zip ...........

Table 2
ID CreditorName AccountNumber Balance

Table 3
ID Note Date Rep

I would like it to output like this

ID 1224523512
FirstName John
LastName Smith
Address 123 Main St
City anytown
State anystate
Zip 12345
.. (lots of other fields) ...
.. (lots of other fields) ...
.. (lots of other fields) ...
CreditorName Chase
AccountNumber 4428461564678461894
Balance 1500
Note TT client said call me back
Date 8/29/2006
Rep jsalerep

I tried to create a cross tab query to do this;however, it will not handle
the number of rows I have. Also it counts the data instead of listing it.
Any ideas on how to do this. Can I just invert the axis' somehow?

 

[Duane Hookom]

It isn't clear if/how you would want to output more than one record. If you
have more than 254 records, you will hit a wall.

I think you would first need to create a union query like:
SELECT ID, "FirstName" as TheField, FirstName as TheValue
FROM tblYourTable
UNION ALL
SELECT ID, "LastName", LastName
FROM tblYourTable
UNION ALL
SELECT ID, "Address", Address
FROM tblYourTable
UNION ALL
---etc---
FROM tblYourTable;

You can then create a crosstab based on the union query that uses TheField
as the Row Heading, ID as the Column Heading, and First of TheValue as the
Value. You may have to do some conversion of dates and numbers to make this
work.

 

[John Nurick]

Hi Duane,

PMFJI but are you sure this will work? ISTM this will need more than 255
SELECT statements unioned together, and I'm wondering whether this will
hit or evade the limit of 255 fields in a query.

If it doesn't do the job, I'd be inclined to work round it by exporting
the data to a text file that Excel can import. Maybe this:

1) use two or more crosstab queries each containing a subset of the
fields

2) export each to a text file

3) concatenate the files in the right order

4) import into Excel.

Or this:

1) export the records as they stand from the three tables into three
text files (all with the records in same order, so that line N in each
file refers to the same ID)

2) transpose the three files (e.g. with the function at
http://www.j.nurick.dial.pipex.com/Code/VBA/TransposeFile.htm)

3) concatenate them in the right order

4) import the result.

The Note field in the third table could complicate matters if it
includes carriage returns...

 

[Duane Hookom]

John,
I believe you are correct with hitting a limit well before field 255. I
would probably keep most everything in Access and split the union query into
several union queries that append to a temporary table. This could also be
done with function like:

Function NormalFormit(strSQL As String, strTableName As String)
'expects a table named strTableName with fields:
' RecNum long integer
' TheField text 60
' TheValue text 255
' The strSQL would be a sql statement that doesn't include
' memo fields or OLE objects etc.
'All field values will be stored as text in the target table
'This code also requires a reference to the DAO library
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim fld As Field
Dim strFldName As String
Dim lngRecNum As Long
Dim strAppendSQL As String
Set db = CurrentDb
Set rsSource = db.OpenRecordset(strSQL)
With rsSource
Do Until .EOF
lngRecNum = lngRecNum + 1
For Each fld In .Fields
strAppendSQL = "INSERT INTO [" & strTableName & _
"] (RecNum, TheField, TheValue) " & _
"Values( " & lngRecNum & ", '" & _
fld.Name & "', " & Chr(34) & _
fld.Value & Chr(34) & ")"
db.Execute strAppendSQL
Next
.MoveNext
Loop
.Close
End With
Set rsSource = Nothing
Set db = Nothing

End Function