New column on redundant field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is oversimplified, but I need to export records from a 3 column table in
this format:

EmpName|WorkHours|WorkDate
DoeJohn|7.0|6/1/2007
DoeJane|8.0|6/1/2007
BrownBob|6.0|6/2/2007
JonesJoe|5.0|6/2/2007

into an excel sheet so that the data appears as:
DateWorked|HoursWorked...|SumofHoursWorked
6/1/2007|7.0|8.0|15.0
6/2/2007|6.0|5.0|11.0

Do I solve this with SQL or VBA? The automation part I can do, once I have
the correct approach.

Thanks in advance.
 
It seems that you want a row for each date, a column for each employee, and
a total column as well. A crosstab query will give this approach.

1. Create a query using this table.

2. Change it to a Crosstab query (Crosstab on Query menu.)

3. Set up your fields like this:
- WorkDate as a Row Heading
- EmpName as a Column Heading
- WorkHours as the Value.
(Use Sum or First in the Total row under WorkHours.)

4. Once you have that working, this will show you how to add the Total
column:
Crosstab query techniques: Display row totals
at:
http://allenbrowne.com/ser-67.html#RowTotal
 
Back
Top