Year Totals

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

Guest

How can I calculate annual totals to fit into a chart that looks like this

I am trying to format a report so that it looks like this

1994 1995 1996 1997
Task
Person A 27 Hrs 103 Hrs 2.5 Hrs 7 Hr
Person B 526 Hrs 115 Hrs 26 Hrs 7 Hrs
Person C 3 Hrs 2 Hrs 6 Hrs 2 Hr
Task
Person A 7 Hrs 2 Hrs 1 Hrs 6 Hr
Person B ...

I have a huge amount of information separated into [EmployeeName], [BillDate], [Task], [HoursBilled]. The hours billed are entered on a weekly basis, and when I try to do a crosstab query I am told that there are too many column headers, which I'm assuming means that I have to narrow down my dates. I am trying to total the dates into Year columns, and I can group them into year, but I can't figure out how to sum them. I do not want to show every single entry detail for the whole year, just the sum of hours
Sorry about the rambling explanation, coffee hasn't kicked in yet
Thank you in advance for your help, if anyone can interpret this.
 
Experiment with the "where clause" and Date Functions. You can select task,
employee, year([#datefield#]), sum([hoursfield]). In your query the year()
function turns all dates #01/01/2003# through #12/21/2003# into 2003 and so
on for all years in the data. Work on the query with the expression
builder. I'd suggest experimenting with select queries using these
functions first to reduce the query result. Then convert the select query
into a crosstab query or create a crosstab query that uses the select query
as a rowsource.
 
Back
Top