Export to CSV

[darinmc]

Hi
I have a query, the code is:
query Name = SagePaymentHours
I have used Chr(13) & Chr(10) and am wanting to open an excel.csv file.

SELECT tblEmployee.EmpNo, 1 AS Pay1, Sum(tblPayInv.BasicEmpHours) AS SumOfBasicEmpHours, Chr(13) & Chr(10), tblEmployee.EmpNo, 2 AS Pay2, Sum(tblPayInv.OT1EmpHours) AS SumOfOT1EmpHours, Chr(13) & Chr(10), tblEmployee.EmpNo, 3 AS Pay3, Sum(tblPayInv.OT2EmpHours) AS SumOfOT2EmpHours, Chr(13) & Chr(10), tblEmployee.EmpNo, 7 AS Pay7, Sum(tblPayInv.HolHr) AS SumOfHolHr
FROM tblEmployee INNER JOIN tblPayInv ON tblEmployee.EmpRegNo=tblPayInv.EmpRegNo
GROUP BY tblEmployee.EmpNo, 1, tblEmployee.EmpNo, 2, tblEmployee.EmpNo, 3, tblEmployee.EmpNo, 7, tblPayInv.EmpRegNo, tblPayInv.WEdate
HAVING (((tblEmployee.EmpNo)>63) AND ((tblPayInv.WEdate)=[W/end Date = d/m]))
ORDER BY tblEmployee.EmpNo;

The code below is to place the results in a csv File when clicking on a command button

I have also used transferText, would like it if this works, but the result appears different
e.g.

Private Sub cmdOutputTo_Click()

DoCmd.OutputTo acOutputQuery, "SagePaymentHours", acFormatXLS, "C:\1\test5.xls"

DoCmd.TransferText acExportDelim, , "SagePaymentHours", "C:\1\test5.csv", False

End Sub

However the reults are coming out in the format

64 1 25 (Empty Column) 64 2 3.5 (EmptyCol) 64 3 0
81 1 24.5 81 2 0 81 3 3

I would like the results to be:
64 1 25
64 2 3.5
64 3 0
81 1 24.5
etc

Plz can some1 help
Thx
D

 

[PHV]

Use an UNION query instead.

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

 

[darinmc]

Thx
How would I be able to do this in code, so that I didnt have to create all 3 query objects?
could you help with the code using the sql string, using the dim statements etc All under a command button. Would I have to use queryDef?

Thx Darin

 

[lespaul]

The UNION works like this:

SELECT Field1, Field2, Field3 FROM SomeTable WHERE Field1 = "Something"
UNION
SELECT Field4, Field5, Field6 FROM SomeOtherTable WHERE Field4 = "SomethingElse"

Will result in the following:
[tt]
1stField 2ndField 3rdField
SomeTable_Data SomeTable_Data SomeTable_Data
SomeTable_Data SomeTable_Data SomeTable_Data
SomeTable_Data SomeTable_Data SomeTable_Data
SomeOtherTable_Data SomeOtherTable_Data SomeOtherTable_Data
SomeOtherTable_Data SomeOtherTable_Data SomeOtherTable_Data
SomeOtherTable_Data SomeOtherTable_Data SomeOtherTable_Data[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

The SQL code of SagePaymentHours could be:
SELECT E.EmpNo, 1 AS Pay, Sum(P.BasicEmpHours) AS SumOfHours
FROM tblEmployee AS E INNER JOIN tblPayInv AS I ON E.EmpRegNo=P.EmpRegNo
WHERE E.EmpNo>63 AND P.WEdate=[W/end Date = d/m] GROUP BY E.EmpNo
UNION SELECT E.EmpNo, 2, Sum(tblPayInv.OT1EmpHours)
FROM tblEmployee AS E INNER JOIN tblPayInv AS I ON E.EmpRegNo=P.EmpRegNo
WHERE E.EmpNo>63 AND P.WEdate=[W/end Date = d/m] GROUP BY E.EmpNo
UNION SELECT E.EmpNo, 3, Sum(tblPayInv.OT2EmpHours)
FROM tblEmployee AS E INNER JOIN tblPayInv AS I ON E.EmpRegNo=P.EmpRegNo
WHERE E.EmpNo>63 AND P.WEdate=[W/end Date = d/m] GROUP BY E.EmpNo
UNION SELECT E.EmpNo, 7, Sum(tblPayInv.HolHr)
FROM tblEmployee AS E INNER JOIN tblPayInv AS I ON E.EmpRegNo=P.EmpRegNo
WHERE E.EmpNo>63 AND P.WEdate=[W/end Date = d/m] GROUP BY E.EmpNo
ORDER BY 1, 2;

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

 

[darinmc]

Thx
Will give it a go
Darin