Q: How to create query expression that calculates based on table values

[John]

I need help understanding how to get a query to lookup an employee's
sales (tblSales) and calculate and expression (Commission) based on a
CommissionRate (tblCommissionSchedule) that corresponds to that
employee's sales.

table (example record):
tblSales
---------------
1.lngEmployeeID (1)
curSales ($20,000)

tblCommissionSchedule
---------------------
1. curSalaryStart ($0)
curSalaryEnd ($50,000)
decCommmissionRate (0.10)

2. curSalaryStart ($51,000)
curSalaryEnd ($75,000)
decCommissionRate (0.15)

Query should return:
lngEmployeeID (1)
curSales ($20,000)
Commission ($2,000)

Any help is greatly appreciated!

[tom]

here's a start...

SELECT
s.lngEmployeeID, s.CurSales, s.curSales * c.decCommissionRate As Commission
FROM
tblSales as s,
tblCommissionSchedule as c
WHERE
s.CurSales Between c.curSalaryStart And c.curSalaryEnd

-td

so*********@hotmail.com (John) wrote in message news:<90**************************@posting.google. com>...

I need help understanding how to get a query to lookup an employee's
sales (tblSales) and calculate and expression (Commission) based on a
CommissionRate (tblCommissionSchedule) that corresponds to that
employee's sales.

table (example record):
tblSales
---------------
1.lngEmployeeID (1)
curSales ($20,000)

tblCommissionSchedule
---------------------
1. curSalaryStart ($0)
curSalaryEnd ($50,000)
decCommmissionRate (0.10)

2. curSalaryStart ($51,000)
curSalaryEnd ($75,000)
decCommissionRate (0.15)

Query should return:
lngEmployeeID (1)
curSales ($20,000)
Commission ($2,000)

Any help is greatly appreciated!

[John]

Thanks Tom....that hit the spot!

-john

to*@nuws.com (tom) wrote in message news:<c1**************************@posting.google. com>...

here's a start...

SELECT
s.lngEmployeeID, s.CurSales, s.curSales * c.decCommissionRate As Commission
FROM
tblSales as s,
tblCommissionSchedule as c
WHERE
s.CurSales Between c.curSalaryStart And c.curSalaryEnd

-td

so*********@hotmail.com (John) wrote in message news:<90**************************@posting.google. com>...

I need help understanding how to get a query to lookup an employee's
sales (tblSales) and calculate and expression (Commission) based on a
CommissionRate (tblCommissionSchedule) that corresponds to that
employee's sales.

table (example record):
tblSales
---------------
1.lngEmployeeID (1)
curSales ($20,000)

tblCommissionSchedule
---------------------
1. curSalaryStart ($0)
curSalaryEnd ($50,000)
decCommmissionRate (0.10)

2. curSalaryStart ($51,000)
curSalaryEnd ($75,000)
decCommissionRate (0.15)

Query should return:
lngEmployeeID (1)
curSales ($20,000)
Commission ($2,000)

Any help is greatly appreciated!

[John]

A follow-up question:

How would I add a second expression that caculates a "paycheck total",
which would be equal to the sum of Commission (an expression) and
tblSales.curSales?

When I created the expression:
Paycheck: sum([curSales]+[Commission])

I get this error:
You tried to execute a query that does not include the specified
expression <name> as part of an aggregate function. (Error 3122)

I feel like this is yet another simple question where the answer is
just not coming to me...
to*@nuws.com (tom) wrote in message news:<c1**************************@posting.google. com>...

here's a start...

SELECT
s.lngEmployeeID, s.CurSales, s.curSales * c.decCommissionRate As Commission
FROM
tblSales as s,
tblCommissionSchedule as c
WHERE
s.CurSales Between c.curSalaryStart And c.curSalaryEnd

-td

so*********@hotmail.com (John) wrote in message news:<90**************************@posting.google. com>...

I need help understanding how to get a query to lookup an employee's
sales (tblSales) and calculate and expression (Commission) based on a
CommissionRate (tblCommissionSchedule) that corresponds to that
employee's sales.

table (example record):
tblSales
---------------
1.lngEmployeeID (1)
curSales ($20,000)

tblCommissionSchedule
---------------------
1. curSalaryStart ($0)
curSalaryEnd ($50,000)
decCommmissionRate (0.10)

2. curSalaryStart ($51,000)
curSalaryEnd ($75,000)
decCommissionRate (0.15)

Query should return:
lngEmployeeID (1)
curSales ($20,000)
Commission ($2,000)

Any help is greatly appreciated!

[John]

BAM! There's the answer...
Paycheck: [Commission] + [curSales]

Although, Tom, I'm curious why you renamed the tables in your SQL to
"s" and "c." Can you or anyone else enlighten me?

-john

to*@nuws.com (tom) wrote in message news:<c1**************************@posting.google. com>...

here's a start...

SELECT
s.lngEmployeeID, s.CurSales, s.curSales * c.decCommissionRate As Commission
FROM
tblSales as s,
tblCommissionSchedule as c
WHERE
s.CurSales Between c.curSalaryStart And c.curSalaryEnd

-td

so*********@hotmail.com (John) wrote in message news:<90**************************@posting.google. com>...

I need help understanding how to get a query to lookup an employee's
sales (tblSales) and calculate and expression (Commission) based on a
CommissionRate (tblCommissionSchedule) that corresponds to that
employee's sales.

table (example record):
tblSales
---------------
1.lngEmployeeID (1)
curSales ($20,000)

tblCommissionSchedule
---------------------
1. curSalaryStart ($0)
curSalaryEnd ($50,000)
decCommmissionRate (0.10)

2. curSalaryStart ($51,000)
curSalaryEnd ($75,000)
decCommissionRate (0.15)

Query should return:
lngEmployeeID (1)
curSales ($20,000)
Commission ($2,000)

Any help is greatly appreciated!

[Pieter Linden]

so*********@hotmail.com (John) wrote in message news:<90**************************@posting.google. com>...

BAM! There's the answer...
Paycheck: [Commission] + [curSales]

Although, Tom, I'm curious why you renamed the tables in your SQL to
"s" and "c." Can you or anyone else enlighten me?

-john

just makes the SQL easier to write and maybe understand. Much easier
to alias a long name than to retype it several times...