If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup tables
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. |
#2
|
|||
|
|||
Lookup tables
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. "tomtheappraiser" wrote: 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. |
#3
|
|||
|
|||
Lookup tables
On Tue, 11 Mar 2008 19:25:52 GMT, "tomtheappraiser" u42026@uwe
wrote: 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 |
#4
|
|||
|
|||
Lookup tables
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. "Golfinray" wrote: 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. "tomtheappraiser" wrote: 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. |
#5
|
|||
|
|||
Lookup tables
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 -- -Larry- -- "Tomtheappraiser" wrote in message ... 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. "Golfinray" wrote: 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. "tomtheappraiser" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|