Simple (?) report?

[John Favaro]

I'm wondering what the simplest way to do this might be:

Two simple tables. First table is just a list of designations of "Tasks"
worked on:

ID Task
0 T1
1 T2
2 T3

(Arbitrary number of tasks)

The second table is just a series of triplets:

Date Task Hours worked
D1 ID <some number>

(Arbitrary number of triplets)

This is pretty clearly your standard kind of timekeeping database, where
the "task" field points back to the task table.

I want to do a report where I just have the dates running down the left
column, and the tasks running across the top, with the the hours worked
on each task in the proper place and perhaps some sums - just like you'd
do it in Excel, say. Something like:

T1 T2 T4 SUM
D1 3 2 5
D2 2 6 8
D3 2 2 4
SUM 4 5 8 17


The part I can't quite get (because I'm new to reports) is the fact that
the tasks table is "dynamic", so that the number of tasks going across
the top will vary with the tasks actually in the table and the tasks
that were actually worked on. (Although I'd be perfectly willing to list
all tasks in the task table across the top, putting zeros were a
particular task was never worked on in a particular period.)

Can this be done with straightforward reporting mechanisms?

Thanks

John

 

[Ken Sheridan]

John:

Base your report on a crosstab query with the dates as the row headings, the
tasks as the column headings and the sum of the hours worked as the values.
Be sure to include an IN clause in the query so that it returns a fixed
number of columns of the tasks in the correct order. Or you can enter them
as the ColumnHeadings property in the query's properties sheet, which will
add the IN clause to the SQL.

In the query be sure to return NZ(SUM([Hours Worked]),0) so that a zero is
returned for the Nulls.

You can easily compute the total hours per date and per task in unbound
controls, the first in the detail section with a ControlSource of
=[T1]+[T2]+[T3] etc. The latter would go in the report footer and have
ControlSource properties of Sum([T1]), Sum([T2]) etc.

Ken Sheridan
Stafford, England

 

[John Favaro]

Many thanks, Ken -- I'll follow your indications.

Cheers,

John

John:

Base your report on a crosstab query with the dates as the row headings, the
tasks as the column headings and the sum of the hours worked as the values.
Be sure to include an IN clause in the query so that it returns a fixed
number of columns of the tasks in the correct order. Or you can enter them
as the ColumnHeadings property in the query's properties sheet, which will
add the IN clause to the SQL.

In the query be sure to return NZ(SUM([Hours Worked]),0) so that a zero is
returned for the Nulls.

You can easily compute the total hours per date and per task in unbound
controls, the first in the detail section with a ControlSource of
=[T1]+[T2]+[T3] etc. The latter would go in the report footer and have
ControlSource properties of Sum([T1]), Sum([T2]) etc.

Ken Sheridan
Stafford, England

I'm wondering what the simplest way to do this might be:

Two simple tables. First table is just a list of designations of "Tasks"
worked on:

ID Task
0 T1
1 T2
2 T3

(Arbitrary number of tasks)

The second table is just a series of triplets:

Date Task Hours worked
D1 ID <some number>

(Arbitrary number of triplets)

This is pretty clearly your standard kind of timekeeping database, where
the "task" field points back to the task table.

I want to do a report where I just have the dates running down the left
column, and the tasks running across the top, with the the hours worked
on each task in the proper place and perhaps some sums - just like you'd
do it in Excel, say. Something like:

T1 T2 T4 SUM
D1 3 2 5
D2 2 6 8
D3 2 2 4
SUM 4 5 8 17


The part I can't quite get (because I'm new to reports) is the fact that
the tasks table is "dynamic", so that the number of tasks going across
the top will vary with the tasks actually in the table and the tasks
that were actually worked on. (Although I'd be perfectly willing to list
all tasks in the task table across the top, putting zeros were a
particular task was never worked on in a particular period.)

Can this be done with straightforward reporting mechanisms?

Thanks

John