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
|
|||
|
|||
Choosing a Currency Rate for 1 table based on field in another tab
Currency fields do not allow lookup values.
Each piece of equipment will have a rate attached to it. The equipment used is chosen from "Equipment" Table as a lookup column. The equipment used is stored in "Applications" Table. The rate is stored in a "Pricing" Table. Can I have the rate field in the Pricing Table choose a value based on the input in the Appliations table? Eg. I have a query setup that combines the information from both Tables (Applications & Pricing). A truck is used in my "Applications" table. The rate for a Truck is $100 (this information will be stored in another table for a lookup column). When I am inputting data for Pricing, is there a way that I can have it automatically select the rate for a truck based on the value chosen in the Applications Table? |
#2
|
|||
|
|||
Choosing a Currency Rate for 1 table based on field in another tab
I have a query setup that combines the information from both Tables
Applications & Pricing). In the query use a left join from Applications to Pricing on equipment. Why do you not have price in the Applications table or does the price change or different for some customers? -- KARL DEWEY Build a little - Test a little "Abouttime" wrote: Currency fields do not allow lookup values. Each piece of equipment will have a rate attached to it. The equipment used is chosen from "Equipment" Table as a lookup column. The equipment used is stored in "Applications" Table. The rate is stored in a "Pricing" Table. Can I have the rate field in the Pricing Table choose a value based on the input in the Appliations table? Eg. I have a query setup that combines the information from both Tables (Applications & Pricing). A truck is used in my "Applications" table. The rate for a Truck is $100 (this information will be stored in another table for a lookup column). When I am inputting data for Pricing, is there a way that I can have it automatically select the rate for a truck based on the value chosen in the Applications Table? |
#3
|
|||
|
|||
Choosing a Currency Rate for 1 table based on field in another tab
First of all, you should not be using Lookup fields (aka combo boxes) in
tables. See this link for a discussion as to why; http://www.mvps.org/access/lookupfields.htm Second, why is your pricing in a separate table? Is it because each piece of equipment can have more than one price associated with it? If that is the case, how do you determine which of the possible prices for a piece of equipment should be used for a given record? On the other hand, if each piece of equipment only has one "price" then maybe it should just be an attribute of the equipment and not in a separate table. Third, you should be using a form for data entry. There are several ways you could get the pricing info from the table like DLookup or the Column property of a combo box, but it would be helpful to know a little more about the fields in you tables and the relationships between tables. -- _________ Sean Bailey "Abouttime" wrote: Currency fields do not allow lookup values. Each piece of equipment will have a rate attached to it. The equipment used is chosen from "Equipment" Table as a lookup column. The equipment used is stored in "Applications" Table. The rate is stored in a "Pricing" Table. Can I have the rate field in the Pricing Table choose a value based on the input in the Appliations table? Eg. I have a query setup that combines the information from both Tables (Applications & Pricing). A truck is used in my "Applications" table. The rate for a Truck is $100 (this information will be stored in another table for a lookup column). When I am inputting data for Pricing, is there a way that I can have it automatically select the rate for a truck based on the value chosen in the Applications Table? |
#4
|
|||
|
|||
Choosing a Currency Rate for 1 table based on field in another
The pricing is in a separate table because it is information that only
certain people will have permission to view. I will be using a form for the data entry; however, all the data will be stored in these separate tables. Are you recommending to have the form have lookup fields but not the Table? What I'm hoping is to have the price come up based on the equipment chosen, not to actually look it up. Please let me know if there is still something I should clarify. "Beetle" wrote: First of all, you should not be using Lookup fields (aka combo boxes) in tables. See this link for a discussion as to why; http://www.mvps.org/access/lookupfields.htm Second, why is your pricing in a separate table? Is it because each piece of equipment can have more than one price associated with it? If that is the case, how do you determine which of the possible prices for a piece of equipment should be used for a given record? On the other hand, if each piece of equipment only has one "price" then maybe it should just be an attribute of the equipment and not in a separate table. Third, you should be using a form for data entry. There are several ways you could get the pricing info from the table like DLookup or the Column property of a combo box, but it would be helpful to know a little more about the fields in you tables and the relationships between tables. -- _________ Sean Bailey "Abouttime" wrote: Currency fields do not allow lookup values. Each piece of equipment will have a rate attached to it. The equipment used is chosen from "Equipment" Table as a lookup column. The equipment used is stored in "Applications" Table. The rate is stored in a "Pricing" Table. Can I have the rate field in the Pricing Table choose a value based on the input in the Appliations table? Eg. I have a query setup that combines the information from both Tables (Applications & Pricing). A truck is used in my "Applications" table. The rate for a Truck is $100 (this information will be stored in another table for a lookup column). When I am inputting data for Pricing, is there a way that I can have it automatically select the rate for a truck based on the value chosen in the Applications Table? |
#5
|
|||
|
|||
Choosing a Currency Rate for 1 table based on field in another
Are you recommending to have the form have lookup fields but not the Table?
Yes. Combo boxes are perfectly acceptable, and commonly used, in forms but they will cause you nothing but headaches if you use them in tables. There are a couple of ways you could accomplish what you want. Both of these methods assume the following; 1) You have an existing relationship between the Equipment table and the Price table based on, for example, EquipID. 2) The text box that will be used to display the price will be *unbound*. Text box name (for this example) = txtDisplayPrice. This text box should be locked so the users can't overwrite it. 3) You are using a combo box in your form to select the Equipment. Combo box name (for this example) = cboEquipment Option 1 ****** Modify the Row Source of your combo box to include the price from the Prices table like; Select tblEquip.EquipID, tblEquip.EquipName, tblPrices.Price From tblEquip Inner Join tblPrices On tblEquip.EquipID = tblPrices.EquipID Order By tblEquip.EquipName Then set the following properties in your combo box Bound Column = 1 Column Count = 3 Column Widths = 0",2",0" This way the first and third columns will be hidden so the users will only see the Equipment Name in the combo box. Next, set the Control Source of the unbound text box (txtDisplayPrice) to; =[cboEquipment].Column(2) the text box will then display the value (price) that is stored in the third column of your combo box. It is a zero based index, so the first column is Column(0), second is Column(1), etc. Option 2 ****** Use a combo box Row Source like; Select EquipID,EquipName From tblEquip Order By tblEquip.EquipName combo box properties like; Bound Column = 1 Column Count = 2 Column Widths = 0",2" Then set the control source of txtDisplayPrice to; =DLookup("Price", "tblPrices", "EquipID=" & [cboEquipment]) This will "Lookup" the appropriate price based on what is selected in the combo box. -- _________ Sean Bailey "Abouttime" wrote: The pricing is in a separate table because it is information that only certain people will have permission to view. I will be using a form for the data entry; however, all the data will be stored in these separate tables. Are you recommending to have the form have lookup fields but not the Table? What I'm hoping is to have the price come up based on the equipment chosen, not to actually look it up. Please let me know if there is still something I should clarify. "Beetle" wrote: First of all, you should not be using Lookup fields (aka combo boxes) in tables. See this link for a discussion as to why; http://www.mvps.org/access/lookupfields.htm Second, why is your pricing in a separate table? Is it because each piece of equipment can have more than one price associated with it? If that is the case, how do you determine which of the possible prices for a piece of equipment should be used for a given record? On the other hand, if each piece of equipment only has one "price" then maybe it should just be an attribute of the equipment and not in a separate table. Third, you should be using a form for data entry. There are several ways you could get the pricing info from the table like DLookup or the Column property of a combo box, but it would be helpful to know a little more about the fields in you tables and the relationships between tables. -- _________ Sean Bailey "Abouttime" wrote: Currency fields do not allow lookup values. Each piece of equipment will have a rate attached to it. The equipment used is chosen from "Equipment" Table as a lookup column. The equipment used is stored in "Applications" Table. The rate is stored in a "Pricing" Table. Can I have the rate field in the Pricing Table choose a value based on the input in the Appliations table? Eg. I have a query setup that combines the information from both Tables (Applications & Pricing). A truck is used in my "Applications" table. The rate for a Truck is $100 (this information will be stored in another table for a lookup column). When I am inputting data for Pricing, is there a way that I can have it automatically select the rate for a truck based on the value chosen in the Applications Table? |
Thread Tools | |
Display Modes | |
|
|