Calculate cost of project against time worked

[TracyM via AccessMonster.com]

Hi everyone

Have had a good look through various discussion groups for this and can't
seem to find what I am looking for, I hope someone can help.

I have a access 2000 database which captures projects coming into the
department, each project is then resourced to a team member. When they have
finished their part they put in how long it has taken them, from 1 hour (up
to max of 3hours) then it goes up in 1/2 day increments to 15 days.

At the moment I just have a drop downlist allowing them to pick, for example,
1/2 day, 1 day, 1.5 days etc, but now we have a set daily and hourly rate for
the department, and I need to be able to calculate how much a project cost to
complete.

I think I will need to change the drop down list to numbers and not a mix of
numbers & text for the calculation to work and that I would need to group the
totals worked in the query before getting it to work out the money, but I am
not sure how to do the calculation?

Rates are £119 for a day or £17 per hour.

Any advice would be appreciated
Thanks

 

[Michel Walsh]

Not necessary. I mean, you can use eval and replace.



? eval( Replace(Replace( myString, "day", "*119 "), "hour", "*17"))

can do, if there is no s after day and hour.


Example:

? eval( Replace(Replace( "6 day", "day", "*119 "), "hour", "*17"))
714

? eval( Replace(Replace( "1.5 hour", "day", "*119 "), "hour", "*17"))
25.5


That is not how I would have done it, though. I would "save" the data
through a lookup that gives me the equivalence, in hour:



Display RealHour
1/2 h 0.5
1 h 1.0
....
1 day 7.0
2 day 14.0 ' data sample



and thus, I would simply, through a lookup, read the number of hours the
display correspond to, and multiply by the hourly cost (actually of 17 ).



Hoping it may help,
Vanderghast, Access MVP

 

[TracyM via AccessMonster.com]

Hi

Thanks for the info, I will give it a go and see what happens

Thanks Again

Not necessary. I mean, you can use eval and replace.

? eval( Replace(Replace( myString, "day", "*119 "), "hour", "*17"))

can do, if there is no s after day and hour.

Example:

? eval( Replace(Replace( "6 day", "day", "*119 "), "hour", "*17"))
714

? eval( Replace(Replace( "1.5 hour", "day", "*119 "), "hour", "*17"))
25.5

That is not how I would have done it, though. I would "save" the data
through a lookup that gives me the equivalence, in hour:

Display RealHour
1/2 h 0.5
1 h 1.0
...
1 day 7.0
2 day 14.0 ' data sample

and thus, I would simply, through a lookup, read the number of hours the
display correspond to, and multiply by the hourly cost (actually of 17 ).

Hoping it may help,
Vanderghast, Access MVP

Hi everyone

[quoted text clipped - 28 lines]

Any advice would be appreciated
Thanks