Export Query csv Transpose Data? 2

[misscrf]

I have a query with the following SQL source:

SELECT tblProcessing.PKProcessingID, tblKeyHBCaseAsset.txtCaseAssetTag AS HBAssetTag, tblCustodian.txtCustodianAlias AS Custodian, tblAsset.dtDateCollected AS DateCollected, tblAsset.txtCollectedFrom AS CollectedFrom, tblAsset.txtCollectedLocation AS CollectedLocation, tblAsset.txtSourceDesc AS DeviceSource, tblProcessing.dtDateProcessed AS DateProcessed
FROM (((tblProcessing LEFT JOIN tblKeyHBCaseAssetCust ON tblProcessing.FKHBCaseAssetCustodian=tblKeyHBCaseAssetCust.PKHBCaseCustodianAssetKeyID) LEFT JOIN tblKeyHBCaseCust ON tblKeyHBCaseAssetCust.FKHBCaseCustodian=tblKeyHBCaseCust.PKHBCaseCustodianKeyID) LEFT JOIN tblCustodian ON tblKeyHBCaseCust.FKCustodian=tblCustodian.PKCustodianID) LEFT JOIN (tblKeyHBCaseAsset LEFT JOIN tblAsset ON tblKeyHBCaseAsset.FKAsset=tblAsset.PKAssetID) ON tblKeyHBCaseAssetCust.FKCaseAsset=tblKeyHBCaseAsset.PKHBCaseAssetKeyID
WHERE (((tblProcessing.PKProcessingID)=Forms!frmHBCase!frmProcessing.Form!PKProcessingID));

When I run the query from a command button on a main entry form it shows the data as one would expect.

column1,column2,column3,etc
data1,data2,data3,etc

It will only show 1 record as it is pull for the current record of a subform.

I need to take this a few steps further.

I need to transpose the data so that I get the following:

Name,Value
ColumnName1,Data1
ColumnName2,Data2
ColumnName3,Data3

I need the Name,Value at the top.

I then need this to be save to a csv, with a prompt to the end user for where to save it. One of the columns will be the file name.

Anyone have any idea how to go about doing this?

If it is too hard, I will have to instruct users in the manual step of copying to excel and transposing data and then saving out to directory location.

Thanks!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I expect you could transform the data with a union query.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks for the reply, dhookom. That is an interesting thought. Would you be able to give me an example or direct me to an FAQ on building a union that would appear to transpose the results of a query?

This might be a great solution!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Here is a start:

SELECT PrimaryKeyField, "PKProcessingID" as ColName, [PKProcessingID] as ColValue
FROM [your query Name]
UNION ALL
SELECT PrimaryKeyField, "HBAssetTag", txtCaseAssetTag 
FROM [your query Name]
UNION ALL\
SELECT PrimaryKeyField, "Custodian", txtCustodianAlias 
FROM [your query Name]
UNION ALL
-- etc --

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

That's brilliant! Thanks a bunch!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

So here is the final union query:

SELECT "Name" AS ColName,"Value" AS ColValue
FROM qryCustomMetadata
UNION ALL
SELECT "AssetTag" AS ColName, qryCustomMetadata.AssetTag AS ColValue
FROM qryCustomMetadata
UNION ALL
SELECT "CollectedFrom" AS ColName, qryCustomMetadata.CollectedFrom AS ColValue 
FROM qryCustomMetadata
UNION ALL
SELECT "CollectedLocation" AS ColName, qryCustomMetadata.CollectedLocation AS ColValue
FROM qryCustomMetadata
UNION ALL
SELECT "CopyHDDSerial" AS ColName, qryCustomMetadata.CopyHDDSerial AS ColValue
FROM qryCustomMetadata
UNION ALL
SELECT "Custodian" AS ColName, qryCustomMetadata.Custodian AS ColValue
FROM qryCustomMetadata
UNION ALL
SELECT "DateCollected" AS ColName, qryCustomMetadata.DateCollected AS ColValue
FROM qryCustomMetadata
UNION ALL
SELECT "DateProcessed" AS ColName, qryCustomMetadata.DateProcessed AS ColValue
FROM qryCustomMetadata
UNION ALL
SELECT "NuixCase" AS ColName, qryCustomMetadata.NuixCase AS ColValue
FROM qryCustomMetadata
UNION ALL
SELECT "NuixEvidenceFileBatch" AS ColName, qryCustomMetadata.NuixEvidenceFileBatch AS ColValue
FROM qryCustomMetadata
UNION ALL
SELECT "OriginalHDDSerial" AS ColName, qryCustomMetadata.OriginalHDDSerial AS ColValue
FROM qryCustomMetadata
UNION ALL
SELECT "SourceDescription" AS ColName, qryCustomMetadata.SourceDescription AS ColValue
FROM qryCustomMetadata
UNION ALL SELECT "UserNameFolder" AS ColName, qryCustomMetadata.UserNameFolder AS ColValue
FROM qryCustomMetadata;

It is called "qryUnionCustomMetadataTrans"

Now I am on to a new step. Please let me know if I should make a new post for this one.

I have this run from a command button on a form, and it filters to the current record that the is on a sub form.

I am looking to export this record to a csv.

Here is the deal,
I made the column headings the first row, so no column headings.
Comma delimited with double quotes for the text.
One of the field values should be the file name.
The path should be prompted or can be set (I can add a field) on the main form.

Here is the code that I tried to accomplish this task:

Private Sub cmdRunCustomMetadata_Click()
On Error GoTo Err_cmdRunCustomMetadata_Click

    Dim strDoc As String
    Dim strFileName As String
    Dim strPath As String
    Dim strExportFile As String
    
    strDoc = "qryUnionCustomMetadataTrans"
    strFileName = qryUnionCustomMetadataTrans.NuixEvidenceFileBatch
    strPath = ["Please specify path to save CSV"]
    strExportFile = strPath & strFileName

    DoCmd.TransferText acExportDelim, NuixMetadataExport, strDoc, strExportFile, False
    MsgBox ("Your data file is saved as: " & Chr(13) & strFileName & Chr(13) & Chr(13) & "Here: " & Chr(13) & strExportFile)

Exit_cmdRunCustomMetadata_Click:
    Exit Sub

Err_cmdRunCustomMetadata_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunCustomMetadata_Click
    
End Sub

Right now I get Object Required, when I click the command button. I tried opening the query, but even if it is open, I still get that error.

any thoughts?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

ok, well I already have an update. I tried to simplify it and explicitly set the path and file name for troubleshooting. That seems to work:

Private Sub cmdRunCustomMetadata_Click()
On Error GoTo Err_cmdRunCustomMetadata_Click

    Dim strDoc As String
    Dim strExportFile As String
    
    strDoc = "qryUnionCustomMetadataTrans"
    strExportFile = "C:\Documents and Settings\misscrf\Desktop\TestExport.csv"
    DoCmd.TransferText acExportDelim, NuixMetadataExport, strDoc, strExportFile, False

Exit_cmdRunCustomMetadata_Click:
    Exit Sub

Err_cmdRunCustomMetadata_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunCustomMetadata_Click
    
End Sub

Now I need to figure out how to replace that path and file name with parameters. I think I will set the path for the metadata in a field in the main form. It is constant for the whole record and all custom metadata records, anyway.
The field is on the current record in the subform and in the union query that is being exported. I will post back if I figure it out!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

This line of code will surely fail:

strFileName = qryUnionCustomMetadataTrans.NuixEvidenceFileBatch

You might need to use DLookup() to grab the value.

Also, I don't know where NuixMetadataExport comes from.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

ok, I added a field to the main form (it's table) so the path could be set there for that whole record (meaning all metadata sub records for that main record all go in one place).

I am also now calling the file name from the sub form as well -

Private Sub cmdRunCustomMetadata_Click()
On Error GoTo Err_cmdRunCustomMetadata_Click

    Dim strDoc As String
    Dim strFileName As String
    Dim strPath As String
    Dim strExportFile As String
    
    strDoc = "qryUnionCustomMetadataTrans"
    strFileName = Forms![frmHBCase]![frmProcessing].Form![txtNuixEvidenceFileBatch]
    strPath = Me.txtUNCMetadataPath
    strExportFile = strPath & strFileName & ".csv"

    DoCmd.TransferText acExportDelim, NuixMetadataExport, strDoc, strExportFile, False

Exit_cmdRunCustomMetadata_Click:
    Exit Sub

Err_cmdRunCustomMetadata_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunCustomMetadata_Click
    
End Sub

Works!

My next is to trap some warnings if the unc path isn't filled out, and let them know when it is done.
Then... I want to be able to expand this. Let the user shift select the rows on the sub form (it is datasheet view), or pop-up a form with a listbox of the records, allowing the user to ctrl click and select the desired records. Then have the code above loop through the selected records and create each csv for each one in that directory.

Sound good? lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

trap some warnings if the unc path isn't filled out
A starting point:

If Dir(Me!txtUNCMetadataPath & "", vbDirectory) = "" Then
  MsgBox "Invalid Path !"
  Me!txtUNCMetadataPath.SetFocus
  Exit Sub
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886