Hello Dears!
here is the question! i have some people, working on different projects, for specific amount of time per day. so far, i have a table (tblTimeSheet) just like this:
[date]_____[Name]___[Project]__[hours]
1/1/1________x________a________1
1/1/1________x________b________2
2/2/2________x________c ________3
1/1/1________y________a________4
2/2/2________y________b________5
1/1/1________z________a________6
Also, each employee gets paid which is registered in whole another table (tblPayment):
[date]_____[Name]____[amount]
1/1/1________x________100
2/2/2________x________101
1/1/1________y________200
2/2/2________y________201
1/1/1________z________300
as a good sense, i wanna see how much i am spending for each specific project per day! a Multiplex, an allotment if you may! something like following table (tblMultiplex):
[date]____[Project]_____[amount]
1/1/1________a________100
1/1/1________b________200
2/2/2________c________300
2/2/2________b________400
i know ive gotta use different queries to
first, sum the projects up from different people per day
second, create some kind of ratio
and third, use the ratio to define the share of money spent for each project!
well, the first step is easy! all iv gotta do is make a select query, group it by [project] and [day], and then sum it up by the value of [hours].
on the other hand the second and third step are baffling! :banghead:
anyone to help?!
here is the question! i have some people, working on different projects, for specific amount of time per day. so far, i have a table (tblTimeSheet) just like this:
[date]_____[Name]___[Project]__[hours]
1/1/1________x________a________1
1/1/1________x________b________2
2/2/2________x________c ________3
1/1/1________y________a________4
2/2/2________y________b________5
1/1/1________z________a________6
Also, each employee gets paid which is registered in whole another table (tblPayment):
[date]_____[Name]____[amount]
1/1/1________x________100
2/2/2________x________101
1/1/1________y________200
2/2/2________y________201
1/1/1________z________300
as a good sense, i wanna see how much i am spending for each specific project per day! a Multiplex, an allotment if you may! something like following table (tblMultiplex):
[date]____[Project]_____[amount]
1/1/1________a________100
1/1/1________b________200
2/2/2________c________300
2/2/2________b________400
i know ive gotta use different queries to
first, sum the projects up from different people per day
second, create some kind of ratio
and third, use the ratio to define the share of money spent for each project!
well, the first step is easy! all iv gotta do is make a select query, group it by [project] and [day], and then sum it up by the value of [hours].
on the other hand the second and third step are baffling! :banghead:
anyone to help?!
Last edited: