HI

how do i create a query where i want a sum of commission of a particular person?

eg of records.
personID = 123456
salary = 3000
commission = 100 (one row in CommTable)
commission = 200 (2nd row in CommTable)

sum = 3300

i have try to create query with expression like:
Total : Sum(([salary]+[commission]))

but it gives me 3100 in a row 3200 in another row, which is not what i want.

pls advice
nono

Hi, Nono.

My suggestion is you redesign your table structure.
For example:
PersonID(Number)
Salary(Currency)
CommissionFirst(Currency)
CommissionSecond(Currency), etc

I think Access would always return the amount "3000"+"xxx"(that you input in the second or third row and so on, because Access recognize the second/third commissions belong to another record(in your case another PersonID and another Salary)

Well, hope this would help you.



Herru

nono

You need to investigate the wonderful world of subqueries to solve this one.

Assumptions
You have two tables called tblSalary and tblCommission and both tables contain a field called personID.

The query would look similar to this:

SELECT tblSalary.personID, tblSalary.salary,
(SELECT SUM(tblCommission.commision)
FROM tblCommission
WHERE tblCommission.personID=tblSalary.personID) AS Commision
FROM tblSalary

This gives you the basic salary and the total commission in seperate fields in you query - you can then add them together on your form/report to show the gross pay.

P.S. Herru, I'm afraid your suggestion is a bad idea, breaking at least one of the rules of data normalisation. The main disadvantage being that a successful salesman could always earn one more lot of commission than you've provided fields in your table. nono's table structure is the correct way to go in situations like this.



Brian Skelton
Braxis Computer Services Ltd.

STOP THERE!

Here is where we get into relations of known cardinality. You can not create many fields as krswhy
 suggests. This goes against rules of normalization.

A sales person is not limited to 2 commissions per period. Hopefully they will be getting more than this.

nono, you need two tables for this, Do you have two tables?
Please reply.



Sal

yes sal,

the commission will increased as time goes.
and i have 2 tables and now the problem is solved by just grouping up the primary keys together to get the sum of commission.


thanks all.;)