?
Solved

Output a crosstab query to excel

Posted on 2003-03-20
16
Medium Priority
?
883 Views
Last Modified: 2010-05-18
When I execute the ouput for a crosstab, I get an error of too many columns (I guess going over 255 is the problem here), so I need to be able to count columns to a defined limit and then carriage return to the next line, before resuming with the next record during the output.

I am trying to turn a two column vertical query result into a horizontal sheet that I then use to upload into another application. But as shown in the example, my profile field actually has alot more than 255 values so regardless of how many profiles are assigned to a user, the crosstab will create enough columns for each unique profile value. Ideally I need this output to carriage return after 99 columns

e.g base data
Select Query
UserID | Profile
ABCDEF | prof1
ABCDEF | prof2
ABCDEF | prof3
GHIJKL | prof1
GHIJKL | prof4

Wanted Result
similar to Crosstab Query but with no column header and no gaps
ABCDEF | prof1 | prof2 | prof3
GHIJKL | prof1 | prof4


So not
ABCDEF | prof1 | prof2 | prof3
GHIJKL | prof1 |       |       | prof4

and if there is more than 99 results for a particular UserID, then it should look like
ABCDEF | prof1 | prof2 | prof3......| prof99
ABCDEF | prof100 | prof101..... etc..
GHIJKL | prof1 | prof4


I'm only a beginner at writing code so nothing here is too fancy. This is all I know as far as launching the export goes, but now it does nothing at all because I have too much data for it to handle

Private Sub Command41_Click()

    DoCmd.OpenQuery "Q_RP_UJRCMP_Crosstab"
    DoCmd.OutputTo acOutputQuery, "Q_RP_UJRCMP_Crosstab", acFormatXLS, , True
    DoCmd.Close acForm, "F_SW08"
   
End Sub


The Crosstab in this code is based on an initial select query and it would be nice if what I need could be included as part of my Click() event

The Crosstab in SQL is this
TRANSFORM First(Q_RP_UJRCMP.[ZP AG Name]) AS [FirstOfZP AG Name1]
SELECT Q_RP_UJRCMP.User
FROM Q_RP_UJRCMP
GROUP BY Q_RP_UJRCMP.User
PIVOT Q_RP_UJRCMP.[ZP AG Name];


The base select query is this in SQL
SELECT DISTINCT T_D001.User, T_JD2D3.[ZP AG Name]
FROM (T_D002 INNER JOIN (T_D001 INNER JOIN T_JD1D2 ON T_D001.User = T_JD1D2.User) ON T_D002.[Job Role Name] = T_JD1D2.[Job Role Name]) INNER JOIN T_JD2D3 ON T_D002.[Job Role Name] = T_JD2D3.[Job Role Name];

And yes my naming convention is not conventional, but I had done most of my development before even knowing there was a convention, Hah!! the pitfalls of being self taught

Any help would be greatly appreciated.
djeden..


0
Comment
Question by:djeden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 14

Expert Comment

by:mgrattan
ID: 8175518
Instead of using the XLS format for your output could you use comma-delimited text instead?  Most programs that can import data will support text files; either delimited or fixed width.
0
 

Author Comment

by:djeden
ID: 8175550
I problem is I can not have commas. I need tab delimited which is why I send it to excel first and then resave and if it has too many columns, I can not edit it in excel either
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 8175808
You can export tab delimited using the DoCmd.TransferText method.  First, however, you need to manually export the table/query by using the File, Export menu -- using the Export Text Wizard, set up Tab delimited export and then click the Advanced button; save the settings as an Export Specification and give it a name.  Once you have saved the Export Specification you can use the TransferText method as follows:

DoCmd.TransferText acExportDelim, [specification name], [table name],[file name],[has field names]

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 1

Expert Comment

by:rknowledge
ID: 8176336
I think I see what you are trying to do.

Why don't you run your crosstab query in a function by opening a recordset.  Once there write the procedure to group your data and the formatting it needs and export just the finished data?

0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 8177596
why dont you write the target file direct ?

e.g. build a form with the two fields(field1,field2) and a commandbutton
and write a function like:

*******************
Private Sub TestOut_Click()

Dim varFile As Variant
Dim strFile, strText, lastField As String
Dim i As Integer

varFile = FreeFile
strFile = "your path and filename.txt"

Open strFile For Append As #varFile

For i = 1 To Me.NumberOfRecords 'I take this from a field in the form

strText=""
  If field1 = lstField Then Goto ContN  'no new user, append profile
  If i < 1 Then Print #varFile, strText 'write the last user to file
  strText = field1 & Chr(XX) 'start new user
  End If
ContN:
  strText = strText & field1 & Chr(XX) 'append profile
DoCmd.GoToRecord
Next i

Print #varFile 'write the last user
Close #varFile
End Sub

******************
 (Take Care:  Chr(XX) .. XX stands for the ASCII Code of the TAB you need, I do not have it in mind for the moment)
0
 

Author Comment

by:djeden
ID: 8180016
All, Thanks for the suggestions, but it seems to me that you are all offering thoughts on exporting my crosstab, but the main issue is I can not run the crosstab because of the column count being too large, hence the need to loop and carriage return the query result, and this has to be achieved before I can think of exporting. Also As I said, I'm new at using code so I need examples.

bonjour-aut, thanks for the code. I'm having trouble getting it to work. I do not understand why you have included print in the code. I get a syntax error, so I remove it and it runs as I have it. Also the End if was in error so I removed it.

For some reason it loops to record 26 and stops, and how is this supposed to be transposing the data?

I setup my query as a datasheet form

Private Sub Command41_Click()

DoCmd.OpenForm "F_UZP"
   
Dim varFile As Variant
Dim strFile, strText, lastField As String
Dim i As Integer

varFile = FreeFile
strFile = "c:\testexp.txt"

Open strFile For Append As #varFile

For i = 1 To Me.Count 'I take this from a field in the form

strText = ""
 If User = lstField Then GoTo ContN  'no new user, append profile
   
    If i < 1 Then Print #varFile, strText 'write the last user to file
    strText = User & Chr(99) 'start new user
 
ContN:
 strText = strText & User & Chr(99) 'append profile
DoCmd.GoToRecord
Next i

Close #varFile
End Sub
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 8180320
hallo djeden

I will post a more detailed comment in a few hours.
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 8181247
i worked on the code a little bit
sorry, that my first proposal was slobby

that i have tested:
Variables: ProfileCount is a Field on the Form with the number of records
User,Profile are the two fields we are interested in.
the result is a "your path and filename.txt" file with horzontal Tab sperated entries for the profiles, where the first entry per line is the user.
i imported to Excel. Excel cuts of after the element 255.
as far as i understand, you dont want the target file to be limited to 255 entries per line.

*************************************************
Private Sub Output_Click()
Dim varFile As Variant
Dim strFile, strText, lastField As String
Dim i As Integer

varFile = FreeFile
strFile = "your path and filename.txt"
DoCmd.GoToRecord , , acFirst

Open strFile For Append As #varFile
strText = User & Chr(9)
lastField = User
For i = 1 To Me.ProfileCount 'I take this from a field in the form
 If User = lastField Then GoTo ContN  'no new user, append profile
 Print #varFile, strText 'write user & profiles to file
 strText = User & Chr(9)  'start new user
 lastField = User
ContN:
 strText = strText & Profile & Chr(9)  'append profile
DoCmd.GoToRecord
Next i
Print #varFile, strText 'write last user & profile to file

Close #varFile
End Sub
****************************************************+
0
 

Author Comment

by:djeden
ID: 8197081
I used DCount to count the number of records, and it does loop the correct number but then tries to go one more and I get an error on the GoToRecord Command. If I set the count to be -1, it loops to the end of the form and stops, but the file is created but is empty.

The Code as I have it.

Private Sub Command41_Click()

DoCmd.OpenForm "F_UZP", acFormDS
   
Dim varFile As Variant
Dim strFile, strText, lastField As String
Dim i As Integer
Dim ProfileCount As Integer

varFile = FreeFile
strFile = "C:\tempexp.txt"
DoCmd.GoToRecord , , acFirst

ProfileCount = DCount("[ZP AG Name]", "Q_RP_UJRCMP")

Open strFile For Append As #varFile
strText = User & Chr(9)
lastField = User
For i = 1 To ProfileCount 'I take this from a field in the form
If User = lastField Then GoTo ContN  'no new user, append profile
Print #varFile, strText 'write user & profiles to file
strText = User & Chr(9)  'start new user
lastField = User
ContN:
strText = strText & Profile & Chr(9)  'append profile
DoCmd.GoToRecord
Next i
Print #varFile, strText 'write last user & profile to file

Close #varFile

End Sub

0
 

Author Comment

by:djeden
ID: 8197626
The other thing is I can't get [User] to pickup a value. I've tried all I know of, but its value is balnk which why the file is blank. If I turn User into a string and provide a value, it does write the value to file ????
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 8198486
i tested the usage of DCount - it works as well in my example see integer-variable "uct"

***************
Private Sub Output_Click()
Dim varFile As Variant
Dim strFile, strText, lastField As String
Dim i, uct As Integer

varFile = FreeFile
strFile = "your path and filename.txt"
uct = DCount("[Profile]", "UserProfiles")
DoCmd.GoToRecord , , acFirst

Open strFile For Append As #varFile
strText = User & Chr(9)
lastField = User
For i = 1 To uct 'I take this from a field in the form
*****************************

In my example "user" is a text-field.
This is necessary anyway, because in: strText = User & Chr(9) it is a textvariable
For conversion you can use the Str$() Function.
This will add a blank beforthe converted value !!
This you will get rid with Right(Str$(User),Len(Str&(User)-1))
It would be more convenient to have the User already as string in the table
0
 

Author Comment

by:djeden
ID: 8201411
I realized that I needed to put the code on the open event of the datasheet form, and not on the button of the menu form, so now it work.

I've increased the points on this because there is one more thing I need to finish this.

I need the output (which I thought I explained in my original question) to carriage return after a defined limit. I would like if a User has more than 99 profiles, to then start a new line with the same User ID and the remainder of the profiles, then new line if new user. This eliminates the issue of number of columns and would allow me to check the output file in excel

If this limit could be a input variable then I could choose to limit depending on the version of the application I was going to load to. One is 99 columns and the other is 40 columns.

0
 

Author Comment

by:djeden
ID: 8201417
I realized that I needed to put the code on the open event of the datasheet form, and not on the button of the menu form, so now it work.

There is one more thing I need to finish this.

I need the output (which I thought I explained in my original question) to carriage return after a defined limit. I would like if a User has more than 99 profiles, to then start a new line with the same User ID and the remainder of the profiles, then new line if new user. This eliminates the issue of number of columns and would allow me to check the output file in excel

If this limit could be a input variable then I could choose to limit depending on the version of the application I was going to load to. One is 99 columns and the other is 40 columns.

0
 

Author Comment

by:djeden
ID: 8201421
I also need the file to refresh not append.
0
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 2000 total points
ID: 8201942
Now it does a new line basd on the variable "llim" (linelimit). In the code i put in the value direct. You can change that to a Form-Field-Content or a Public Varible or you make two buttons with 40 and 60 profiles

The output file now names automatically to yymmdd and hhmmss so it makes always a new file and the filename is selfexplanatory

I hope you like that

******************************************************
Private Sub Output_Click()
Dim varFile As Variant
Dim strFile, strText, lastField As String
Dim i, uct, llim As Integer

varFile = FreeFile
strFile = "userprofiles" & Str$(Format(Date, "yymmdd")) & Str$(Format(Time, "hhmmss")) & ".txt"
uct = DCount("[Profile]", "UserProfiles")
llim = 40
DoCmd.GoToRecord , , acFirst

Open strFile For Append As #varFile
strText = User & Chr(9)
lastField = User
For i = 1 To uct
 If User = lastField Then GoTo ContN  'no new user, append profile
 Print #varFile, strText 'write user & profiles to file
 strText = User & Chr(9)  'start new user
 lastField = User
ContN:
 strText = strText & Profile & Chr(9)  'append profile
 If i / llim <> i \ llim Then GoTo NextRec:
 Print #varFile, strText 'write user & profiles to file
 strText = User & Chr(9)  'start new line with same user
NextRec:
DoCmd.GoToRecord
Next i
Print #varFile, strText 'write last user & profile to file

Close #varFile
End Sub
**********************************************************

I do not recommend to put the code on the formOpen-Event, because then you will not have the prossibility to have a field for the line length.
In my Test-mdb it is on a commandbutton
0
 

Author Comment

by:djeden
ID: 8209387
This is an excellent solution to the problem, now that the output does carriage return, it will save a lot of time trying to manually prepare my upload files. This has helped make my DB a much more powerful tool.

Many Thanks
Phil..

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question