newbie formula question 2

[buffy13]

Hello,
I'm a newbie and I have the following problem:

Below are records that workers have to fill in every day about there activities from that week. If a person has to travel to get to a client, he or she has to fill in those hours too. A certain week would look like this then:

project activity description date hours worked

proj_01 xxx ffdfdfd 11/03/2003 8
proj_01 trav ffdfdfd 11/03/2003 4
proj_01 xxx ffdfdfd 11/04/2003 8
proj_01 trav ffdfdfd 11/04/2003 2
proj_01 xxx ffdfdfd 11/05/2003 8
proj_01 xxx ffdfdfd 11/06/2003 8
proj_01 xxx ffdfdfd 11/07/2003 8

(activities called 'trav' are traveling hours)

What I would like to do now is to put the traveling dates behind the corresponding working hours (of the same day) in stead of putting them in a seperate row like the example below. What would be the formula?

project activity description date hours travel

proj_01 xxx ffdfdfd 11/03/2003 8 4
proj_01 xxx ffdfdfd 11/04/2003 8 2
proj_01 xxx ffdfdfd 11/05/2003 8
proj_01 xxx ffdfdfd 11/06/2003 8
proj_01 xxx ffdfdfd 11/07/2003 8

Can anyone help me please?

 

[dGrouse]

Well, I'm sure there are a number of ways to handle this, but one that comes to mind is to group by project, day, and activity code, and write formula to do running totals of the non-trav and trav hours (in the detail section), displaying the different values in the appropriate group footer.

Another way is the "database way": assuming (for sake of argument) that what you've got up there is flat file, join it against itself, doing a left outer join, on project = project, date = date. In the first table instance, filter out the "trav" records. In the second, filter out everything but "trav" records and "null"'s. (If you don't permit NULLs on the right, you'll filter out all projects without a trav record.) Then, you'll have, basically, two different field - non-trav and trav - which you can do whatever you want with.

Jeff Prenevost
IS Administrator
Emergency Physicians Medical Group, PC
Ann Arbor, MI

 

[lbass]

I would group by project and then date. Then create two formulas:

//{@travel}:
if {table.activity} = "Travel" then {table.hours}

//{@hours}:
if {table.activity} <> "Travel" then {table.hours}

Right click on each formula and insert a summary (sum). Then suppress the detail section.

-LB

 

[buffy13]

thank you for your help, it works now!