How to Create a MULTIPLEX Query?! (1 Viewer)

veshand

New member
Local time
Today, 05:00
Joined
Jun 10, 2018
Messages
9
Hello Dears! :cool::cool::cool:

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?!
:D
 
Last edited:

plog

Banishment Pending
Local time
Today, 07:00
Joined
May 11, 2011
Messages
11,611
Walk me through the logic behind Project A having an amount of 100 for 1/1//1? Explain the 'some kind of ratio'.
 

Ranman256

Well-known member
Local time
Today, 08:00
Joined
Apr 9, 2015
Messages
4,337
yes, make a Q1 to sum : person hrs/proj/day
then in Q2: use Q1 joined to tPersonRates to get: $/Person/Proj/Day
then Q3: use Q2 to sum all $/Proj.
 

Users who are viewing this thread

Top Bottom