Lookup tables

[tomtheappraiser]

I am in the midst of deisgn a new database that will be able to track my
appraisal orders, allow for billing information and set up payroll.

I'm wondering if I set up a table with each appraiser in my firms name, along
with thier percentage split, can I setup the appraiser fee field so that when
I select say appraiser "Bob" the appraiser fee field looks up bobs fee split
in his table, applies that percentage (lets say 60%) to the full fee (lets
say $100) I had already entered in the record and calculates what his fee
will be ($60)?

Also I would like to do the same thing with each of my lenders. So when I
choose the lender in the lookup field, and the type of form, it knows what I
charge for that particular lender for that particular form.

 

[Golfinray]

Yes. You will need to set those tables up then use those tables (or data
queried out of them) as the basis of forms that actually do the work. If you
have a more specific question, we will try to give you a more specific answer.

 

[Armen Stein]

I am in the midst of deisgn a new database that will be able to track my
appraisal orders, allow for billing information and set up payroll.

I'm wondering if I set up a table with each appraiser in my firms name, along
with thier percentage split, can I setup the appraiser fee field so that when
I select say appraiser "Bob" the appraiser fee field looks up bobs fee split
in his table, applies that percentage (lets say 60%) to the full fee (lets
say $100) I had already entered in the record and calculates what his fee
will be ($60)?

Also I would like to do the same thing with each of my lenders. So when I
choose the lender in the lookup field, and the type of form, it knows what I
charge for that particular lender for that particular form.

Yes, and don't forget to handle when Bob's fee percentage changes. You
won't want all the historical records to be recalculated. So, you'll
need to store the percentage and/or calculated dollar amount for each
transaction to preserve the value *at the time*.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tomtheappraiser]

Well, I know how to do the sperate tables and general lookups, but I don't
know ho to do the formulas or expressions that would make the database decide
what to when certain things occur.

For instance when I pick Bob as the appraiser how do I make the next field
(The percentage split field) know to look at his percentage split and than
apply to the appraisal fee? From my hazy memories of learning Basic back in
the 80's I would assume it would be some kind of "If..Then.." statement. But
I don't know how to apply in the field design.

 

[Larry Daugherty]

Hi Tom,

Access isn't a flawed version of Excel. it's a different application
with different behaviors.

In the table for appraisers you'd have the appraiser's name or ID
along with a field like "split" or some such that good old Bill gets
on his work. The combobox that you use on your form to select Bill
and others will be based on a query. If you use the query Wizard to
help you create the combobox it will also design the query for you.
You'd want to look up a value for later use. As the wizard steps you
through the process, make sure it returns the field "split" as well
as the other field(s) you want. What you want to do next depends
entirely on what you already have on your form and where you are in
your calculations. I'll assume you just want to return the value in
"split" to a control on your form named "txtShare" or something like
that. In the AfterUUpdate event of cboGetSplit put this line of code:

me!txtShare = me!cboGetSplit.column(1) if your query just returns
the name and the split. A point of confusion: The property form
refers to the combobox columns as "one based", in the code the column
collection values are "zero based".

Once you have your value on the form you can calculate the result of
the Job value multiplied by the split value. Once you get the hang of
it you can change things around to suit yourself.

Change the names of things and the exact same process works for loan
rates. associated with lenders.

Post back with any problems.

HTH