Formatting Excel Worksheet

[SHAWTY721]

I have results of a query that I exported into excel from access that I need to have be able to display in an excel worksheet in particular fields. Where I am running into trouble is how can I have the values from multiple columns populate one column in the formatted spreadsheet that I need to transfer the data to. I need to have the values that are in the Tax and Ins column insert after the last record in the account field. The results of the query vary depending on criteria entered by the user. So I was trying to automate this process. But I'm not exactly sure the best way to accomplish my goal.

Acount|Sub Account|Ins #| Tax#| Sub
60060 0100 62500 62200 0100
60060 0100 62500 62200 0100
60105 0100 62500 62200 0100
60105 0100 62500 62200 0100
60170 0100 62500 62200 0000
60170 0100 62500 62200 0000
60320 0000 62530 62230 0000
60320 0000 62530 62230 0000
60450 0100 62500 62200 0100
60450 0100 62500 62200 0100
60570 0000 62520 62220 0000
60570 0000 62520 62220 0000
60600 0200 62500 62200 0100
60600 0200 62500 62200 0100
60620 0100 62500 62200 0100

So after the Tax and Ins field have been added to the end of the Account and Sub Account columns it would look like this.

Acount|Sub Account|
60060 0100
60060 0100
60105 0100
60105 0100
60170 0100
60170 0100
60320 0000
60320 0000
60450 0100
60450 0100
60570 0000
60570 0000
60600 0200
60600 0200
60620 0100
62200 0100
62200 0100
62200 0100
62200 0100
62200 0000
62200 0000
62230 0000
62230 0000
62200 0100
62200 0100
62200 0000
62200 0000
62200 0100
62200 0100
62200 0100
62500 0100
62500 0100
62500 0100
62500 0000
62500 0000
62530 0000
62530 0000
62500 0100

 

[SkipVought]

Hi,

I am TOTALLY confused!

What does Acount|Sub Account|Ins #| Tax#| Sub have to do with the data in the rows below it? It seem, NOTHING. No Ins #, No Tax #...

Please opost your query SQL. It seems that the problem is in this query.



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SHAWTY721]

Those numbers are how the information is received and those are the titles for those fields. I just need to figure out how to get them to write into the Account column after the last record.

 

[SkipVought]

Where is your SQL???

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SHAWTY721]

They are the column headers.

 

[SHAWTY721]

This is my query that uses DAO to export the query results to Excel.

"SELECT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, tblGLAllCodes.Sub, tblAllADPCoCodes.BranchNumber, Sum(tblAllPerPayPeriodEarnings.GROSS) As GROSS, tblGLAllCodes.Ins, tblGLAllCodes.Tax, tblAllPerPayPeriodEarnings.CHECK_DT FROM (tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept=tblAllPerPayPeriodEarnings.GLDEPT) INNER JOIN tblAllADPCoCodes ON (tblAllPerPayPeriodEarnings.PG=tblAllADPCoCodes.ADPCompany) AND (tblAllPerPayPeriodEarnings.[LOCATION#]=tblAllADPCoCodes.LocationNumber)  WHERE  PG = '" & Forms("frmJE").Controls("cboADPCompany").Value & "' AND [LOCATION#] = '" & Forms("frmJE").Controls("cboLocationNo").Value & "' AND BranchNumber = " & Forms("frmJE").Controls("txtBranchNo").Value & " AND CHECK_DT Between #" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "# " & _
"GROUP BY tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGlAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, tblGLAllCodes.Ins, tblGLAllCodes.Tax, tblGLAllCodes.Sub, tblAllADPCoCodes.BranchNumber, tblAllPerPayPeriodEarnings.CHECK_DT ORDER BY tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct;"

 

[SHAWTY721]

Here is the whole Export Function that I created.

Public Function ExportQuery() As String 'Function creates query result set the exports results to excel based on form selections
On Error GoTo Err_Handler

'Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sOutput As String 'Output string to build up

Dim dbs As DAO.Database 'This is the database
Dim rst As DAO.Recordset 'Retrieves value of field
Dim sSQL As String 'SQL Statement that generates output
Dim IRecords As Long 'Counts # of records returned by query
Dim J As Long 'Initializes line where data entry begins

Dim strPeriod As String
Dim strYear As String

DoCmd.Hourglass True

'Set to break on all errors
On Error Resume Next

'Start with clean file built from template file
sTemplate = CurrentProject.Path & "\JournalEntry.xls" 'file that spreadsheets are based off of
sOutput = CurrentProject.Path & "\Journal Entry " & txtBranchNo & " " & txtDescription & " .xls" 'This is how the result of the query is saved by excel
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput

'Create the Excel Application, Workbook and Worksheet and Database object
Set appExcel = New Excel.Application 'Assigns objects to variables
appExcel.Visible = True 'Makes Excel session visible
Set wbk = appExcel.Workbooks.Open(sOutput)

sSQL = "SELECT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, tblGLAllCodes.Sub, tblAllADPCoCodes.BranchNumber, Sum(tblAllPerPayPeriodEarnings.GROSS) As GROSS, tblGLAllCodes.Ins, tblGLAllCodes.Tax, tblAllPerPayPeriodEarnings.CHECK_DT FROM (tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept=tblAllPerPayPeriodEarnings.GLDEPT) INNER JOIN tblAllADPCoCodes ON (tblAllPerPayPeriodEarnings.PG=tblAllADPCoCodes.ADPCompany) AND (tblAllPerPayPeriodEarnings.[LOCATION#]=tblAllADPCoCodes.LocationNumber) WHERE PG = '" & Forms("frmJE").Controls("cboADPCompany").Value & "' AND [LOCATION#] = '" & Forms("frmJE").Controls("cboLocationNo").Value & "' AND BranchNumber = " & Forms("frmJE").Controls("txtBranchNo").Value & " AND CHECK_DT Between #" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "# " & _
"GROUP BY tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGlAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, tblGLAllCodes.Ins, tblGLAllCodes.Tax, tblGLAllCodes.Sub, tblAllADPCoCodes.BranchNumber, tblAllPerPayPeriodEarnings.CHECK_DT ORDER BY tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct;"

Set dbs = CurrentDb 'Opens database
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot) 'Sets the record set to the query

J = 15 'Sets J equal to first row where data is to be entered

Do Until rst.EOF
With wbk.Sheets("JournalEntryTemplate")
IRecords = IRecords + 1
.Range("G3") = rst.Fields("BranchNumber").Value 'Company
.Range("G6") = Year(rst.Fields("CHECK_DT").Value) 'Year from Check Date txt box
.Range("G7") = Month(rst.Fields("CHECK_DT").Value) 'Month from Check Date txt box
.Cells(J, 11).Value = rst.Fields("GL_Acct").Value 'Account
.Cells(J, 12).Value = rst.Fields("GL_Subacct").Value 'Sub Account
.Cells(J, 15).Value = rst.Fields("GROSS").Value 'Amount
.Cells(J, 17).Value = rst.Fields("AccountDescription").Value 'Description
.Cells(J, 3).Value = IRecords 'Line
.Cells(J, 4).Value = .Range("G6") 'Year(Now()) Year
.Cells(J, 5).Value = .Range("G4") 'System
.Cells(J, 6).Value = .Range("G5") 'Journal
.Cells(J, 7).Value = .Range("G7") 'Month(Now()) - 1 Period
.Cells(J, 8).Value = .Range("G8") 'Type
.Cells(J, 9).Value = rst.Fields("BranchNumber").Value 'To Company
.Cells(J, 10).Value = .Range("G3") & "" & ("600") 'Accounting Unit
.Cells(J, 18).Value = .Range("G10") 'Auto Reverse
.Cells(J, 2).Value = ("A") 'Func Code
.Cells(J, 20).Value = rst.Fields("Tax").Value 'Ins
.Cells(J, 21).Value = rst.Fields("Ins").Value 'Tax
.Cells(J, 22).Value = rst.Fields("Sub").Value 'Sub

End With

J = J + 1 'Moves to next row each time through loop
rst.MoveNext
Loop

'Saves & closes workbook
wbk.Close True

ExportQuery = "Total of " & IRecords & " rows processed." 'Displays total number of records processed & that were sent to excel

exit_Here:

'Cleanup all objects (resume next on errors)
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

Err_Handler:
ExportQuery = Err.Description
Resume exit_Here
End Function

 

[SkipVought]

That is NOT the SQL that produces...
[tt]
Acount|Sub Account|Ins #| Tax#| Sub
[/tt]
You have the following headings ...
[tt]
GLDEPT | GL_Acct | GL_Subacct | GL_Dept | AccountDescription | Sub | BranchNumber | GROSS | Ins | Tax | CHECK_DT
[/tt]
Where's the disconnect here???

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SHAWTY721]

Those column headings are already written into the excel template that I have created, to hold this data.

 

[SkipVought]

It's not just column heading but the DATA.

Where is the QUERY that produces...
[tt]
Acount|Sub Account|Ins #| Tax#| Sub
60060 0100 62500 62200 0100
60060 0100 62500 62200 0100
60105 0100 62500 62200 0100
60105 0100 62500 62200 0100
60170 0100 62500 62200 0000
60170 0100 62500 62200 0000
60320 0000 62530 62230 0000
60320 0000 62530 62230 0000
60450 0100 62500 62200 0100
60450 0100 62500 62200 0100
60570 0000 62520 62220 0000
60570 0000 62520 62220 0000
60600 0200 62500 62200 0100
60600 0200 62500 62200 0100
60620 0100 62500 62200 0100
[/tt]


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SHAWTY721]

This is the query that produces the data, it is based off the selections made on a form in the database that holds this information.

SELECT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, tblGLAllCodes.Sub, tblAllADPCoCodes.BranchNumber, Sum(tblAllPerPayPeriodEarnings.GROSS) As GROSS, tblGLAllCodes.Ins, tblGLAllCodes.Tax, tblAllPerPayPeriodEarnings.CHECK_DT FROM (tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept=tblAllPerPayPeriodEarnings.GLDEPT) INNER JOIN tblAllADPCoCodes ON (tblAllPerPayPeriodEarnings.PG=tblAllADPCoCodes.ADPCompany) AND (tblAllPerPayPeriodEarnings.[LOCATION#]=tblAllADPCoCodes.LocationNumber)  WHERE  PG = '" & Forms("frmJE").Controls("cboADPCompany").Value & "' AND [LOCATION#] = '" & Forms("frmJE").Controls("cboLocationNo").Value & "' AND BranchNumber = " & Forms("frmJE").Controls("txtBranchNo").Value & " AND CHECK_DT Between #" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "# " & _
"GROUP BY tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGlAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, tblGLAllCodes.Ins, tblGLAllCodes.Tax, tblGLAllCodes.Sub, tblAllADPCoCodes.BranchNumber, tblAllPerPayPeriodEarnings.CHECK_DT ORDER BY tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct;
[\CODE]

 

[SkipVought]

Your posted SQL has 11, count 'em, columns.

Your posted example has 5, count 'em, columns.

Where's the disconnect???!!!

Do you really want some help? Please post the relevant data!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SHAWTY721]

I didn't write all the columns out in my question because I don't need to have special formatting for those. I have the columns linked to the excel spreadsheet that the users will be using. I need to combine columns.

 

[SkipVought]

Well you do you get an output that has repeated in the same row?

You have to help me here!!! I can't see what is apparent ot you.

Exactly which FIELDS in the SQL correspond to the data that you posted???

Please be CLEAR, CONCISE and COMPLETE.
[tt]
Acount|Sub Account|Ins #| Tax#| Sub
60060 0100 62500 62200 0100
60060 0100 62500 62200 0100
60105 0100 62500 62200 0100
60105 0100 62500 62200 0100
60170 0100 62500 62200 0000
60170 0100 62500 62200 0000
60320 0000 62530 62230 0000
60320 0000 62530 62230 0000
60450 0100 62500 62200 0100
60450 0100 62500 62200 0100
60570 0000 62520 62220 0000
60570 0000 62520 62220 0000
60600 0200 62500 62200 0100
60600 0200 62500 62200 0100
60620 0100 62500 62200 0100[/tt]


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SHAWTY721]

No the data isn't repeated. Each record is unique and generated from data in the database. I am trying to figure out how to get multiple columns from the access query to be able to populate the same column in the excel spreadsheet.

 

[SkipVought]

Change your query something like ...

Select Field1, Field2
From MyTable
UNION
Select Field7, Field8
From MyTable
UNION
Select Field11, Field12
From MyTable

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SHAWTY721]

What will that do if I change the query.

 

[SkipVought]

Changes the way the data appears on the sheet: apparently the way you want it to appear. Puts all the accts in one column and the sub accts in the adjacent column, if you get the correct fields stacked up, one over the other.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}