 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2008Price Levels
I have an order entry form that runs off of a query. It started off with just using the unit price and it works fine. But now my boss is wanting it to reflect the correct price for the customer's price level. I started by adding Price level in my customer table to reflect which price level they are on. then I added the price levels in my items table. (There are 10 price levels total). But after that I am drawing a blank on how to pull them in to my form. If at all possible, I would like it to be a combo box that brings up the correct price for that customer's bracket and then be able to view the other 9 levels within the combo box. Any recommendations? Use the Form Current event to set the value of the combo box. Use a DLookup to the customer table to determine the price level, then assign that value to the combo box: Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = " & Me.txtCustID)  SignatureDave Hargis, Microsoft Access MVP > I have an order entry form that runs off of a query. It started off with > just using the unit price and it works fine. But now my boss is wanting it [quoted text clipped - 5 lines] > up the correct price for that customer's bracket and then be able to view the > other 9 levels within the combo box. Any recommendations? I try this and it gives me an error: compile error: Syntax error >Use the Form Current event to set the value of the combo box. Use a DLookup >to the customer table to determine the price level, then assign that value to [quoted text clipped - 7 lines] >> up the correct price for that customer's bracket and then be able to view the >> other 9 levels within the combo box. Any recommendations? Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = " & Me.txtCustID) The syntax, as posted, is correct, but there are some assumptions. The combo box name is cboPriceLevel The field name that stores the price level is named PriceLevel and is a numeric field. The table name is tblCustomer The name of the table's primary key field is CustID There is a text box control on the form named txtCustID boud to the field CustID in the form's recordset. The code is in the form's code module. Please check these things. If you are still having a problem, please copy/paste the code from your module, so I can see if I missed something SignatureDave Hargis, Microsoft Access MVP > I try this and it gives me an error: compile error: Syntax error > [quoted text clipped - 9 lines] > >> up the correct price for that customer's bracket and then be able to view the > >> other 9 levels within the combo box. Any recommendations? You pointed out a couple of problems that I can see. One, My field name that stores that price level name is a text field because I was not sure if it was going to be looking up anything. I will change that to numeric. Also, I had no CustomerID in my form because it is a subform with just the orderdetails based from a query. So I guess you could say I have many things wrong. But I have just been clueless on where to even start. >Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = " & >Me.txtCustID) [quoted text clipped - 18 lines] >> >> up the correct price for that customer's bracket and then be able to view the >> >> other 9 levels within the combo box. Any recommendations? To help, I need to know a bit about your configuration. First, I assume you have the invoice header and client info in the main form and the line item details are in the subform, but what I need to know is how the price is calculated. If you put an unbound combo on the main form as I described, you could use the value of the combo in the extended price calculation. How is the discount carrired? As a percentage off list, or is it a separate price list for each client? SignatureDave Hargis, Microsoft Access MVP > You pointed out a couple of problems that I can see. One, My field name that > stores that price level name is a text field because I was not sure if it was [quoted text clipped - 25 lines] > >> >> up the correct price for that customer's bracket and then be able to view the > >> >> other 9 levels within the combo box. Any recommendations? Originally the price was calculated by looking up the unit price in my Items table. When I found that they wanted the other 9 price levels included, it has just thrown me off. There are 10 different prices for each Item in the table, and different customers are set to different price levels. >To help, I need to know a bit about your configuration. >First, I assume you have the invoice header and client info in the main form [quoted text clipped - 10 lines] >> >> >> up the correct price for that customer's bracket and then be able to view the >> >> >> other 9 levels within the combo box. Any recommendations? Then I think you will need to modify the subform's record source to be a query that can include the price by customer. Since I don't know your table structures, I can't give you a specicif answer, but basically, you should be able to find a price in a query. You need to have your price table set up so it has fields for the client and the item. Then you can use a query for the subform's record source that joins the price list table on the combination of item and client. SignatureDave Hargis, Microsoft Access MVP > Originally the price was calculated by looking up the unit price in my Items > table. When I found that they wanted the other 9 price levels included, it [quoted text clipped - 15 lines] > >> >> >> up the correct price for that customer's bracket and then be able to view the > >> >> >> other 9 levels within the combo box. Any recommendations? I totally get what you are saying, but I can not figure out what field to put in the price table to link the two together. Here is what my ItemList contains. Any suggestions? ItemID ItemDescription Active UnitPrice PriceLevel2 PriceLevel3 PriceLevel4 PriceLevel5 PriceLevel6 PriceLevel7 PriceLevel8 PriceLevel9 PriceLevel10 SalesAcct >Then I think you will need to modify the subform's record source to be a >query that can include the price by customer. [quoted text clipped - 8 lines] >> >> >> >> up the correct price for that customer's bracket and then be able to view the >> >> >> >> other 9 levels within the combo box. Any recommendations? For starters, you would use the ItemID To join to the customer, I don't know, but is SalesAcct the same as the customer? Is so, you would join SalesAcct to the Customer and ItemID to the Item Table's primary key. SignatureDave Hargis, Microsoft Access MVP > I totally get what you are saying, but I can not figure out what field to put > in the price table to link the two together. Here is what my ItemList [quoted text clipped - 27 lines] > >> >> >> >> up the correct price for that customer's bracket and then be able to view the > >> >> >> >> other 9 levels within the combo box. Any recommendations? No, the sales account is a GL account to put the price in to the correct category. >For starters, you would use the ItemID >To join to the customer, I don't know, but is SalesAcct the same as the [quoted text clipped - 6 lines] >> >> >> >> >> up the correct price for that customer's bracket and then be able to view the >> >> >> >> >> other 9 levels within the combo box. Any recommendations? Do you have a field in the Customer table that defines what price level the customer gets? SignatureDave Hargis, Microsoft Access MVP > No, the sales account is a GL account to put the price in to the correct > category. [quoted text clipped - 9 lines] > >> >> >> >> >> up the correct price for that customer's bracket and then be able to view the > >> >> >> >> >> other 9 levels within the combo box. Any recommendations? Yes, I have a field that is called PriceLevel in my customer table. >Do you have a field in the Customer table that defines what price level the >customer gets? [quoted text clipped - 3 lines] >> >> >> >> >> >> up the correct price for that customer's bracket and then be able to view the >> >> >> >> >> >> other 9 levels within the combo box. Any recommendations? Okay, great. I am about to leave for the day, but I will get back to you tomorrow with some ideas. SignatureDave Hargis, Microsoft Access MVP > Yes, I have a field that is called PriceLevel in my customer table. > [quoted text clipped - 5 lines] > >> >> >> >> >> >> up the correct price for that customer's bracket and then be able to view the > >> >> >> >> >> >> other 9 levels within the combo box. Any recommendations? What data type is the field PriceLevel in the customer table? What value does it contain to identify a specific price level? SignatureDave Hargis, Microsoft Access MVP > Okay, great. > I am about to leave for the day, but I will get back to you tomorrow with [quoted text clipped - 9 lines] > > >> >> >> >> >> >> up the correct price for that customer's bracket and then be able to view the > > >> >> >> >> >> >> other 9 levels within the combo box. Any recommendations? I just have it as text because I was not sure what to do with it. >What data type is the field PriceLevel in the customer table? >What value does it contain to identify a specific price level? [quoted text clipped - 3 lines] >> > >> >> >> >> >> >> up the correct price for that customer's bracket and then be able to view the >> > >> >> >> >> >> >> other 9 levels within the combo box. Any recommendations? What text value identifes which price level? It would make it easier if you used a numberic value so like 0 = standard price, 1 = PriceLeve1, etc. In either case, let me know, I think this will take a function you will need to call to return the correct price. SignatureDave Hargis, Microsoft Access MVP > I just have it as text because I was not sure what to do with it. > [quoted text clipped - 5 lines] > >> > >> >> >> >> >> >> up the correct price for that customer's bracket and then be able to view the > >> > >> >> >> >> >> >> other 9 levels within the combo box. Any recommendations? Would it make since to make a table of Price levels with the appropriate levels indicated with the numbers 1-10 as the primary key, and then look them up from my customers price level table? Or am I making it too complicated? >What text value identifes which price level? >It would make it easier if you used a numberic value so like 0 = standard [quoted text clipped - 7 lines] >> >> > >> >> >> >> >> >> up the correct price for that customer's bracket and then be able to view the >> >> > >> >> >> >> >> >> other 9 levels within the combo box. Any recommendations? That would actually be the easy way, but the problem is how your pricelist table is structured. To do that, it would need to be: ItemID PriceLevelID ItemPrice The you could join it in a query using the ItemID from your Item table and the PriceLevelID from the Customer table. SignatureDave Hargis, Microsoft Access MVP > Would it make since to make a table of Price levels with the appropriate > levels indicated with the numbers 1-10 as the primary key, and then look them [quoted text clipped - 11 lines] > >> >> > >> >> >> >> >> >> up the correct price for that customer's bracket and then be able to view the > >> >> > >> >> >> >> >> >> other 9 levels within the combo box. Any recommendations? I can think of several approaches based on your formulation of price level. Do you use a base price and then level 1 is a percent reduced and level 5 a percent increased or all are discrete prices? Using the percent method the customer would have a field for level. Then use a multiplier factor for raise or lower price Level % Reduce Multiplier 0 0 1 1 1.00% 0.99 =1-0.01 2 2.00% 0.98 =1-0.02 3 3.00% 0.97 =1-0.03 4 4.00% 0.96 =1-0.04 5 5.00% 0.95 =1-0.05 10 10.00% 0.9 =1-0.1 15 15.00% 0.85 =1-0.15 -1 101.00% -0.01 =1-1.01 -2 102.00% -0.02 =1-1.02 -3 103.00% -0.03 =1-1.03 -4 104.00% -0.04 =1-1.04 -5 105.00% -0.05 =1-1.05 -10 110.00% -0.1 =1-1.1 -15 115.00% -0.15 =1-1.15 The formula would be one minus the percent stated in decimal form. Discrete prices will require a translation table. SignatureKARL DEWEY Build a little - Test a little > I have an order entry form that runs off of a query. It started off with > just using the unit price and it works fine. But now my boss is wanting it [quoted text clipped - 5 lines] > up the correct price for that customer's bracket and then be able to view the > other 9 levels within the combo box. Any recommendations?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.