expression which will return a result based on different conditio.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have one field that lists the amount of payments received from customers.
I have another table which lists four payment options. How do I write an
expression that will return a commission amount on the payment received based
on the commission option for that record. (Payment amount * .10 or .20 or
..30 or .40)
Thanks in advance for your help!!
 
So you've got a table containing a unique record (row) for
each payment option available. It should have a field
(column) in it to store the percent commission to be paid
when that option is used. eg) [OptionID], [CommPct]

You've got another table containing a record (row) for
each unique payment you've recieved with a field (column)
in it to store the payment option utilized by the
customer. eg) [PaymentID], [OptionID], [PaidAmount]

Your query should use an inner join between the two
corresponding 'payment option' fields (connect them with a
line) in the two tables which will allow your query to use
the correct percentage for the commission calculation.
Your expression would look something like this...

CommDue: [tblPayment]![PaidAmount]*[tblOption]![CommPct]

The SQL for the query, using the example field names above
would be this...

SELECT [tblPayment]![PaidAmount]*[tblOption]![CommPct]
AS CommDue
FROM tblOption
INNER JOIN tblPayment
ON tblOption.OptionID = tblPayment.OptionID

I hope this gives you a direction :) good luck.
 
Back
Top