report based on two fields

[kreftan]

Hello.

I has a table that has a similar look as this:

job, cost, employee1, employee2, percentage

I want to create a report where I got the sum of a unique employee. The trouble is that he can be employee1 or employee2. I can get the sum of every employee1, and the sum of every employee2. What I want is to add the corresponding employee from the two queries.

I think I need to give an example:

employee1 employee2
Adam, 500 Nick, 300
Nick, 750 Steve, 550
Steve, 300

As you can see, there arent always all employee at booth tables.

Now i want a query, where I get the folowing result
Adam, 500
Nick, 1050
Steve, 850

Hope i made myself clear. Perhaps I have made the thing more difficult than it is, but please help me.

Regards Kenta

 

[PaulBricker]

Kenta, I don't understand how you get two costs in one record
Adam, 500 Nick, 300
when you only have one Cost field? Is this two Tables that you have or two Employee fields and one Cost field in one Table? Please explain a little more.

Paul

 

[kreftan]

Hello... Thanks for your response.

Ohh, i forgot to describe, that the two employees had the "cost" based on the records cost, multiplied by the percentage. ie a cost of 800 in a record, and a percentage of 62,5 percent will give Adam a part of 500, and Nick a part of 300

ii reality this record describes a sale, where two people are involved, and the percentage describe the active part of the two employees. This part then adds throug the month and is the foundation for their paycheck. Sometimes there are only one employee involved, and I have solved thet part. Now I need to handle this situation, where two employees are involved.

 

[PaulBricker]

Sorry I haven't gotten back to you on this. I'm still unclear how these values are assigned. I understand that 62.5% of 800 is 500 and Adam gets that but Nick get 300 and that's calculated as 800 - (800 * 62.5%) or 300. This makes what you want to do more difficult and I haven't come up with a good solution yet. Could you take a moment and tell me what fields are in the table exactly. This will help sort out some of the questions I have.

Thanks

Paul