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
|
|||
|
|||
Conceptual Excercise - Pricing
Hi folks!
I'm putting together some code that will price jobs based on a certain price structure, but I am unsure if I have the concept totally nailed down. I just thought I'd ask the big brains at this community for their opinions. Feel free to let me know what you think... WHAT I HAVE: Currently, we have hundreds of customers, many of whom have their own pricing structure (negotiated by management). Within that pricing structure, jobs are priced by the process that the parts undergo and the size of the part. Information about the parts is input into our database manually. The size of the part is input into a single field using a format similar to this: "2x4"; "36x24x36". Some of you may have read my posts asking for help with this. Anyhow, I've got it to work for a single process for only one customer. I can post the code, but let's leave that for later. Right now, I'm just asking for conceptual guidance. Lets just assume that this will work for all circumstances. WHAT I NEED: In a nutshell I need the code I currently have (with modifications, of course) to work for all processes and for all customers. MY CONCEPT: Well, I've never worked on code so largescale before, so if anyone has any tips to make it more managable, please let me know. This is sort of how I envisioned it: If [customer] = customer1 then If [process code] = process code1 then Select Case [Length] Case Size1 [Price]=price1 Case Size2 [Price]=price2 Case Size3 [Price]=price3 End Select If [Process Code] = Process Code2 Then... etc. If [customer] = customer2 Then If [process code] = process code1 then ...Different pricing structure... ....etc. You can imagine that with hundreds of customers and with dozens of process codes and about fifteen size ranges for each process code, this become quite an undertaking (for me at least). The code becomes huge. I'm definately not a wiz when it comes to coding (as I'm sure Stefan can attest) so this is ambitous to say the least. WHAT I'M ASKING Does this coding structure make sense in theory? (Details to be worked out later) How do you recommend that the errors be handled? Are there techniques I can use to keep the code from becoming too unmanagable? Is there a(n effective or useful) way I can break the pricing down by customer using an event procedure to simplify and isolate my code? Heck, this is academic at this point.. Just give me your thoughts, words of warning, pearls of wisdom, critiques, musings, curses, etc. I'd appreciate the input. Thanks in advance! -- Why are you asking me? I dont know what Im doing! Jaybird |
#2
|
|||
|
|||
Conceptual Excercise - Pricing
OMG, if you don't get rid of that first if, you are going to be looking at
some ugly spagetti code me thinks. How 'bout just retrieving a process code for a customer and you can eliminate that IF statement altogether, assuming of course every customer in your database has a process code related to it... HTH, I also hope I understood you question correctly. S "Jaybird" wrote in message ... Hi folks! I'm putting together some code that will price jobs based on a certain price structure, but I am unsure if I have the concept totally nailed down. I just thought I'd ask the big brains at this community for their opinions. Feel free to let me know what you think... WHAT I HAVE: Currently, we have hundreds of customers, many of whom have their own pricing structure (negotiated by management). Within that pricing structure, jobs are priced by the process that the parts undergo and the size of the part. Information about the parts is input into our database manually. The size of the part is input into a single field using a format similar to this: "2x4"; "36x24x36". Some of you may have read my posts asking for help with this. Anyhow, I've got it to work for a single process for only one customer. I can post the code, but let's leave that for later. Right now, I'm just asking for conceptual guidance. Lets just assume that this will work for all circumstances. WHAT I NEED: In a nutshell I need the code I currently have (with modifications, of course) to work for all processes and for all customers. MY CONCEPT: Well, I've never worked on code so largescale before, so if anyone has any tips to make it more managable, please let me know. This is sort of how I envisioned it: If [customer] = customer1 then If [process code] = process code1 then Select Case [Length] Case Size1 [Price]=price1 Case Size2 [Price]=price2 Case Size3 [Price]=price3 End Select If [Process Code] = Process Code2 Then... etc. If [customer] = customer2 Then If [process code] = process code1 then ...Different pricing structure... ...etc. You can imagine that with hundreds of customers and with dozens of process codes and about fifteen size ranges for each process code, this become quite an undertaking (for me at least). The code becomes huge. I'm definately not a wiz when it comes to coding (as I'm sure Stefan can attest) so this is ambitous to say the least. WHAT I'M ASKING Does this coding structure make sense in theory? (Details to be worked out later) How do you recommend that the errors be handled? Are there techniques I can use to keep the code from becoming too unmanagable? Is there a(n effective or useful) way I can break the pricing down by customer using an event procedure to simplify and isolate my code? Heck, this is academic at this point.. Just give me your thoughts, words of warning, pearls of wisdom, critiques, musings, curses, etc. I'd appreciate the input. Thanks in advance! -- Why are you asking me? I dont know what Im doing! Jaybird |
#3
|
|||
|
|||
Conceptual Excercise - Pricing
I definitely understand that I'm looking at a mess... and I appreciate your
suggestions on how to avoid it. I think I know how I can retrieve the process code and determine which pricing structure to use from there BUT I'm not sure that I can avoid the process of sorting them by customer first. The thing is, certain customers get certain deals and special arrangements from management, so I have to treat them differently. However, I'm pretty sure that MOST of our customers follow a standard pricing structure, so I guess its not really as big a mess as I originally indicated. Still, I THINK you know what I'm saying... Now... I hope I've understood what YOU were saying:-) -- Why are you asking me? I dont know what Im doing! Jaybird "SAL" wrote: OMG, if you don't get rid of that first if, you are going to be looking at some ugly spagetti code me thinks. How 'bout just retrieving a process code for a customer and you can eliminate that IF statement altogether, assuming of course every customer in your database has a process code related to it... HTH, I also hope I understood you question correctly. S "Jaybird" wrote in message ... Hi folks! I'm putting together some code that will price jobs based on a certain price structure, but I am unsure if I have the concept totally nailed down. I just thought I'd ask the big brains at this community for their opinions. Feel free to let me know what you think... WHAT I HAVE: Currently, we have hundreds of customers, many of whom have their own pricing structure (negotiated by management). Within that pricing structure, jobs are priced by the process that the parts undergo and the size of the part. Information about the parts is input into our database manually. The size of the part is input into a single field using a format similar to this: "2x4"; "36x24x36". Some of you may have read my posts asking for help with this. Anyhow, I've got it to work for a single process for only one customer. I can post the code, but let's leave that for later. Right now, I'm just asking for conceptual guidance. Lets just assume that this will work for all circumstances. WHAT I NEED: In a nutshell I need the code I currently have (with modifications, of course) to work for all processes and for all customers. MY CONCEPT: Well, I've never worked on code so largescale before, so if anyone has any tips to make it more managable, please let me know. This is sort of how I envisioned it: If [customer] = customer1 then If [process code] = process code1 then Select Case [Length] Case Size1 [Price]=price1 Case Size2 [Price]=price2 Case Size3 [Price]=price3 End Select If [Process Code] = Process Code2 Then... etc. If [customer] = customer2 Then If [process code] = process code1 then ...Different pricing structure... ...etc. You can imagine that with hundreds of customers and with dozens of process codes and about fifteen size ranges for each process code, this become quite an undertaking (for me at least). The code becomes huge. I'm definately not a wiz when it comes to coding (as I'm sure Stefan can attest) so this is ambitous to say the least. WHAT I'M ASKING Does this coding structure make sense in theory? (Details to be worked out later) How do you recommend that the errors be handled? Are there techniques I can use to keep the code from becoming too unmanagable? Is there a(n effective or useful) way I can break the pricing down by customer using an event procedure to simplify and isolate my code? Heck, this is academic at this point.. Just give me your thoughts, words of warning, pearls of wisdom, critiques, musings, curses, etc. I'd appreciate the input. Thanks in advance! -- Why are you asking me? I dont know what Im doing! Jaybird |
#4
|
|||
|
|||
Conceptual Excercise - Pricing
Try to organize the business rules so that you can create a table with
prices in it. Each product must have a unique ID. That id may be comprised of part number plus size. There will be a standard price associated with this. For customers who have special prices, the key to their pricing table would be part number, size, and customeriD. You will have to make use of left joins in your query. You would left join to the standards table and left join to the customer special prices table. You should always find a standard price and if the customer has a special price, you will find it otherwise the special price will be null. Given that, your report/form would use an IIf() to determine which price to use. IIf(isnull(specialprice), stdprice, specialprice) as unitprice Trying to code this will be much more complicated than using queries. Tables are very easy to change. Customers may have special pricing for some parts but not others. The table will be much more flexible. "Jaybird" wrote in message ... Hi folks! I'm putting together some code that will price jobs based on a certain price structure, but I am unsure if I have the concept totally nailed down. I just thought I'd ask the big brains at this community for their opinions. Feel free to let me know what you think... WHAT I HAVE: Currently, we have hundreds of customers, many of whom have their own pricing structure (negotiated by management). Within that pricing structure, jobs are priced by the process that the parts undergo and the size of the part. Information about the parts is input into our database manually. The size of the part is input into a single field using a format similar to this: "2x4"; "36x24x36". Some of you may have read my posts asking for help with this. Anyhow, I've got it to work for a single process for only one customer. I can post the code, but let's leave that for later. Right now, I'm just asking for conceptual guidance. Lets just assume that this will work for all circumstances. WHAT I NEED: In a nutshell I need the code I currently have (with modifications, of course) to work for all processes and for all customers. MY CONCEPT: Well, I've never worked on code so largescale before, so if anyone has any tips to make it more managable, please let me know. This is sort of how I envisioned it: If [customer] = customer1 then If [process code] = process code1 then Select Case [Length] Case Size1 [Price]=price1 Case Size2 [Price]=price2 Case Size3 [Price]=price3 End Select If [Process Code] = Process Code2 Then... etc. If [customer] = customer2 Then If [process code] = process code1 then ...Different pricing structure... ...etc. You can imagine that with hundreds of customers and with dozens of process codes and about fifteen size ranges for each process code, this become quite an undertaking (for me at least). The code becomes huge. I'm definately not a wiz when it comes to coding (as I'm sure Stefan can attest) so this is ambitous to say the least. WHAT I'M ASKING Does this coding structure make sense in theory? (Details to be worked out later) How do you recommend that the errors be handled? Are there techniques I can use to keep the code from becoming too unmanagable? Is there a(n effective or useful) way I can break the pricing down by customer using an event procedure to simplify and isolate my code? Heck, this is academic at this point.. Just give me your thoughts, words of warning, pearls of wisdom, critiques, musings, curses, etc. I'd appreciate the input. Thanks in advance! -- Why are you asking me? I dont know what Im doing! Jaybird |
#5
|
|||
|
|||
Conceptual Excercise - Pricing
Hmmm... ...forest for the trees syndrome? Maybe. This is an excellent
thought, Pat. Using a query to organize my pricing by customer makes a lot of sense. However, there are many thousands of part numbers that we deal with, and new ones are brought to us all the time. (WE're a job shop dealing with customer parts...) My thinking is that a query for this purpose would be too complex to process. Are you saying that for each customer and for each process, there should be a new query? If not, I'm having difficulty imagining what such a query would look like. For clarification, we have hundreds of customers and tens of thousands of part numbers that we deal with. A part number can be treated with six or seven different processes. These processes determine the pricing model that determines the piece price, which, in turn, is dependant on the size of the part. There are about a dozen sizes that can determine price. Anything outside of that those parameters gets a "special" price negotiated by management. In reality, there are about a dozen prices (based on size) for each process and there are about seven main processes that we perform. So there are about 84 prices that I can come up with for customers that use a "standard" pricing format. Which is a headache, but perhaps not too unmanageable. I can imagine a way to use a query for each process to obtain my pricing, but I can't figure out how to do it in a single query. I'm going to limit myself to the "standard" pricing model for right now. Different pricing models for special customers will just have to wait. You may have figuered it out by now, but SQL is something of a mystery to me and mixing VBA and SQL language together is a bit unsettling. Thanks for your response! -- Why are you asking me? I dont know what Im doing! Jaybird "Pat Hartman (MVP)" wrote: Try to organize the business rules so that you can create a table with prices in it. Each product must have a unique ID. That id may be comprised of part number plus size. There will be a standard price associated with this. For customers who have special prices, the key to their pricing table would be part number, size, and customeriD. You will have to make use of left joins in your query. You would left join to the standards table and left join to the customer special prices table. You should always find a standard price and if the customer has a special price, you will find it otherwise the special price will be null. Given that, your report/form would use an IIf() to determine which price to use. IIf(isnull(specialprice), stdprice, specialprice) as unitprice Trying to code this will be much more complicated than using queries. Tables are very easy to change. Customers may have special pricing for some parts but not others. The table will be much more flexible. "Jaybird" wrote in message ... Hi folks! I'm putting together some code that will price jobs based on a certain price structure, but I am unsure if I have the concept totally nailed down. I just thought I'd ask the big brains at this community for their opinions. Feel free to let me know what you think... WHAT I HAVE: Currently, we have hundreds of customers, many of whom have their own pricing structure (negotiated by management). Within that pricing structure, jobs are priced by the process that the parts undergo and the size of the part. Information about the parts is input into our database manually. The size of the part is input into a single field using a format similar to this: "2x4"; "36x24x36". Some of you may have read my posts asking for help with this. Anyhow, I've got it to work for a single process for only one customer. I can post the code, but let's leave that for later. Right now, I'm just asking for conceptual guidance. Lets just assume that this will work for all circumstances. WHAT I NEED: In a nutshell I need the code I currently have (with modifications, of course) to work for all processes and for all customers. MY CONCEPT: Well, I've never worked on code so largescale before, so if anyone has any tips to make it more managable, please let me know. This is sort of how I envisioned it: If [customer] = customer1 then If [process code] = process code1 then Select Case [Length] Case Size1 [Price]=price1 Case Size2 [Price]=price2 Case Size3 [Price]=price3 End Select If [Process Code] = Process Code2 Then... etc. If [customer] = customer2 Then If [process code] = process code1 then ...Different pricing structure... ...etc. You can imagine that with hundreds of customers and with dozens of process codes and about fifteen size ranges for each process code, this become quite an undertaking (for me at least). The code becomes huge. I'm definately not a wiz when it comes to coding (as I'm sure Stefan can attest) so this is ambitous to say the least. WHAT I'M ASKING Does this coding structure make sense in theory? (Details to be worked out later) How do you recommend that the errors be handled? Are there techniques I can use to keep the code from becoming too unmanagable? Is there a(n effective or useful) way I can break the pricing down by customer using an event procedure to simplify and isolate my code? Heck, this is academic at this point.. Just give me your thoughts, words of warning, pearls of wisdom, critiques, musings, curses, etc. I'd appreciate the input. Thanks in advance! -- Why are you asking me? I dont know what Im doing! Jaybird |
#6
|
|||
|
|||
Conceptual Excercise - Pricing
Jaybird wrote:
Currently, we have hundreds of customers, many of whom have their own pricing structure (negotiated by management). Within that pricing structure, jobs are priced by the process that the parts undergo and the size of the part. Information about the parts is input into our database manually. First lets get the business rules for standard pricing going smoothly. You stated in another posting " A part number can be treated with six or seven different processes. These processes determine the pricing model that determines the piece price, which, in turn, is dependant on the size of the part. There are about a dozen sizes that can determine price. " We need a processes table and we need a size (or size range) table. A part can have any or all of these six or seven processes as well as a size. So now we need a parts processes table listing which processes happen to that part. A part can also have a size. Now we need a pricing process size table which has a price for the process and size. There will be 84 records in this table. Oh but different customers get special prices? Are these prices arranged by group of customer? That is these customers get 10% off and these higher volume customers get 15% off? Nah, that would be easy. So the pricing process table needs to also have customerID added in there as well. Or possible mgmt can assign customers to groups. Five, ten or fifteen different groups and each group get a different price. Much easier to manage. Now what happens when mgmt decides to increase prices because a particular process is more expensive? Yeah, it's a PITA but that's life. Mgmt really needs to stop making so many different special deals. Now how do you apply this pricing? In the after update event of the quantity control on the subform you should enter the various parameters and do an open recordset against the pricing process size table. Now you do want to store this price on your invoice table as this is the price at that moment in time. As you might be changing that particular price later that day. The following is some very complex sample code which is overkill for you situation but you should get the idea. The price book concept allows us different pricing systems. Also this logic uses a combination of piping schedule and size as it's basic matrix for pricing. (Although this is only a few lines of the 5,000 or so lines of code dealing with pricing in this system.) Given that there are a range of sizes available and other details the strPriceCalcDetails variable is used to store the logic I used to calculate the price. For example say someone choose a 5" pipe to weld but there is no 5" pipe in the pricing table. The = in the query will then select the next size up which should be 6". And I then put 6" in the strPriceCalcDetails, among other fields. Watch for line wraps as there are a lot of them. Dim strSQL As String Dim sngAuditlopbdSize As Single, strAuditmsSchedule As String, sngAuditmsSize As Single Dim RS As Recordset strSQL = "SELECT * FROM [Invoicing - Price Book Calcs - Matrix] " & _ "WHERE lopbhPriceBooksID =" & CStr(lngPriceBookID) & " AND " & _ "lotType=" & CStr(intLabourOpType) & " AND " If blnSchedRequired = True Then strSQL = strSQL & "lopbdMaterialScheduleID=" & CStr(lngMaterialScheduleID) Else strSQL = strSQL & "lopbdMaterialScheduleID=Null" End If strSQL = strSQL & " AND lopbdSize = " & CStr(sngPipeSize) & ";" Set RS = dbsPermanent.OpenRecordset(strSQL, dbOpenSnapshot) If RS.EOF Then strPriceCalcDetails = strLabourOpType & " price not found. Calculated from " & _ "Size = " & CStr(sngPipeSize) & " and " & _ "Schedule = " & CStr(strMaterialSchedule) & ". " & vbCrLf & _ " (sub - CalculatePriceFromMatrix - Schedule)" Else RS.MoveFirst lngDiscountAdderID = Nz(RS!lopbDiscountAdderID) If RS!lopblInactiveQ = True Then strPriceCalcDetails = strPriceCalcDetails & vbCrLf & " Price book section '" & _ RS!lopbhText & "' is inactive. " CalculatePriceFromMatrix = 0 Else CalculatePriceFromMatrix = RS!lopbdAmount End If sngAuditlopbdSize = RS!lopbdSize strAuditmsSchedule = Nz(RS!msSchedule) sngAuditmsSize = Nz(RS!msSize) strPriceBookPageNbr = Nz(RS!lopblPageNumber) strSectionNbr = Nz(RS!lopbImportGridSectionNumber) strPriceCalcDetails = strPriceCalcDetails & vbCrLf & _ strLabourOpType & " Price is " & Format(RS!lopbdAmount, "Currency") & _ " calculated from Size = " & CStr(sngAuditlopbdSize) & " " & _ Pricing_ReturnSchedRatingSize(Nz(RS!msScheduleType ), strAuditmsSchedule, strPriceCalcDetails) & ". " End If Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm |
#7
|
|||
|
|||
Conceptual Excercise - Pricing
"Tony Toews [MVP]" wrote:
Now we need a pricing process size table which has a price for the process and size. There will be 84 records in this table. Let me change that last sentence. There will be 84 records in this table if there was only a basic pricing structure. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm |
#8
|
|||
|
|||
Conceptual Excercise - Pricing
Thanks, Mr. Toews! I had a feeling that even a monster like this was more
managable than I had conceived. I'll go over your response and try to figure out what it all means... -- Why are you asking me? I dont know what Im doing! Jaybird "Tony Toews [MVP]" wrote: "Tony Toews [MVP]" wrote: Now we need a pricing process size table which has a price for the process and size. There will be 84 records in this table. Let me change that last sentence. There will be 84 records in this table if there was only a basic pricing structure. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm |
#9
|
|||
|
|||
Conceptual Excercise - Pricing
Tony,
I might better be able to tell what's going on in the code if I understood better the structure of the query you are suggesting... You seem to have related at least three different tables: size, process, and customer together in one table. In other words, I have a part of a certain size that needs a certain process done to it, depending on what pricing model this particular customer has I get a price. The key to this thing is, I believe, the 'pricing process size table' which assigns a price for each possible combination of price and size. This defines the 'standard' pricing which will (hopefully) be used for pricing most of the jobs we get. From there I can define the exceptions with customized queries, right? I guess maybe I'm just being stubborn. (Or dense) Maybe I do understand how this needs to work and I'm just balking at the amount of work it will take. I don't know why. Surely this is a lot less work than it would take to code it from scratch... and a lot easier to read... and a lot easier to understand... and a lot easier to fix in case something went wrong. If I'm still missing something, please let me know... -- Why are you asking me? I dont know what Im doing! Jaybird "Tony Toews [MVP]" wrote: "Tony Toews [MVP]" wrote: Now we need a pricing process size table which has a price for the process and size. There will be 84 records in this table. Let me change that last sentence. There will be 84 records in this table if there was only a basic pricing structure. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm |
#10
|
|||
|
|||
Conceptual Excercise - Pricing
Jaybird wrote:
I might better be able to tell what's going on in the code if I understood better the structure of the query you are suggesting... In your case you only really need the one table 'pricing process size table' that you are "drilling" into with the various fields such as ProcessID and size. Because I was using a price book which allows for customizing the complete pricing system by customer or group of customers along with a number of other additional factors my query had four or six tables in it. You seem to have related at least three different tables: size, process, and customer together in one table. In other words, I have a part of a certain size that needs a certain process done to it, depending on what pricing model this particular customer has I get a price. The key to this thing is, I believe, the 'pricing process size table' which assigns a price for each possible combination of price and size. This defines the 'standard' pricing which will (hopefully) be used for pricing most of the jobs we get. Correct. From there I can define the exceptions with customized queries, right? Well no. Then you need to figure out what the business rules are behind the exceptions. And you might, almost certainly, need to add a customer ID field to the 'pricing process size table' and give that customer a special price for that particular process/size entry. Thus expanding the number of records much past 84 records. Or group of customers. Maybe you have a ABC list of customers. A gets the best price, B not quite as good a price and C a bit better than list. And tell mgmt that it's much easier to give customers x% of your standard pricing rather than customized pricing where each item process/size could have a different price. This will reduce clerical work and the chance of errors. "Whaddya mean Joe Blow Inc has been paying $4.40 for that process/size for the past two years when it should've been $44.00." Especially when it comes to price changes in the future. I guess maybe I'm just being stubborn. (Or dense) Maybe I do understand how this needs to work and I'm just balking at the amount of work it will take. I don't know why. Surely this is a lot less work than it would take to code it from scratch... and a lot easier to read... and a lot easier to understand... and a lot easier to fix in case something went wrong. If I'm still missing something, please let me know... Yes, this approach requires a different way of coding your the price lookup. But once it's done and working then you just need to maintain the prices using forms. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm |
|
Thread Tools | |
Display Modes | |
|
|