 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / March 2008If Then. Maybe?
I've got a table that lists parts and prices. Prices are down by Years. Currently Years are 2007, 2008 & 2009. What I need to do is determine IF year = 2008 & Price is Null, THEN i need to take the Price that is listed in year = 2007 and escalate that price by a set percentage and then use that as the 2008 price. Any suggestions? RHM Please provide more information - Are the years 2007, 2008 and 2009 separate fields in the same table (BAD design!!), or are they in a separate table? John >I've got a table that lists parts and prices. Prices are down by Years. >Currently Years are 2007, 2008 & 2009. [quoted text clipped - 5 lines] >Any suggestions? >RHM Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3, Qty1, Qty2, Qty3 Where Price1 is to Qty1, etc. So they can adjust prices due to quantity discounts, ie, the more you buy the cheaper they are each. I've inherited this database (always fun.....NOT), so trying to assist this group produce their product and make recommendations for improvements. Im not happy with how its currently put together, but..having to work with what I have for the time being (unfortunately) Heres whats happening though, For some parts we have vendor quotes for 2007, 2008 & 2009. for others we just have a quote for 2007, thats when the escalation would come into play. ex. 23456, 2007, 1.16, 1.13, 1.10, 1, 5, 10 23456, 2008. 1.20, 1.17, 1.15, 1, 5, 10 23456, 2009, 1.22, 1.20, 1.17, 1, 5, 10 1234, 2007, 34.20, 33.50, 32.00, 1, 25, 75 1234, 2008 1234, 2009 4321, 2007, 128.12 4321, 2008 4321, 2009 so for parts 1234 & 4321 I would need to escalate the 2007 price to 2008. Basically its for the creation of a Catalogue of sorts Any suggestions would be great RHM >Please provide more information - Are the years 2007, 2008 and 2009 separate >fields in the same table (BAD design!!), or are they in a separate table? [quoted text clipped - 6 lines] >>Any suggestions? >>RHM >Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3, >Qty1, Qty2, Qty3 >Where Price1 is to Qty1, etc. So they can adjust prices due to quantity >discounts, ie, the more you buy the cheaper they are each. Yep. BADLY denormalized! "Committing spreadsheet upon a database". >I've inherited this database (always fun.....NOT), so trying to assist this >group produce their product and make recommendations for improvements. Im [quoted text clipped - 21 lines] > >Any suggestions would be great Stop struggling with this badly flawed design, and just normalize it. You should have a parts table, related one to many to a price table with fields Partno, EffectiveDate, EndDate, MinQuantity, Price; this would have multiple rows for each part, rather than multiple fields. You can run Append queries to migrate the data. I suggest the two date fields rather than just a year field because someday you might want to change prices on some date other than January 1. John W. Vinson [MVP] Exactly, it was a spreadsheet........ a 50MG spreadsheet. Thats not a typo thats 50MG. You should of seen the Macros they had and the convoluted formulas to create a report for them. They at least decided, yes was need this in a database and then imported, directly into Access. AS IS. Now they want their reports running yesterday and dont want to spend the time or money to do it right. I should just go on and normalize it and be done with it, you are right.......Im screwed. LOL. RHM >>Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3, >>Qty1, Qty2, Qty3 [quoted text clipped - 18 lines] > > John W. Vinson [MVP] OK I highjacked their DB and normalized it, much the way you suggested it and have it running with reports. Should of just did it to start with, but was already juggling projects. Anyway its done. So, What I need to do now is...There is a form that allows the user to put in the quantity of how many parts are wanted (QTYParts) I need to search through the table and match the QTYParts with MinQty so I can use the correct price. Remembering that sometimes there is only 1 price and other times there can be up to 6 discounts. I think I need a loop function to search through, but not sure how to go about it. Im still a novice when it comes to programming. Thanx! RHM >>Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3, >>Qty1, Qty2, Qty3 [quoted text clipped - 18 lines] > > John W. Vinson [MVP] >OK I highjacked their DB and normalized it, much the way you suggested it >and have it running with reports. Should of just did it to start with, but [quoted text clipped - 7 lines] >sure how to go about it. Im still a novice when it comes to programming. >Thanx! What's the current structure of your tables and some sample data? I don't think any sort of loop would be needed or appropriate, but a subquery should work.  SignatureJohn W. Vinson [MVP] Table structure: PartNumber, Year, Qty, Price So for data looks like: XYZ, 2007, 1, $2.40 XYZ, 2007, 10, $2.35 XYZ, 2007, 25, $2.20 XYZ, 2007, 35, $2.15 Where, if you were looking at 2007 prices, for 15 parts, you would want your search to go thru all the prices and select the $2.35 price. So I'm trying to figure out how to search through the different quantity breaks to find the correct price to use for calculations. RHM >>OK I highjacked their DB and normalized it, much the way you suggested it >>and have it running with reports. Should of just did it to start with, but [quoted text clipped - 5 lines] >think any sort of loop would be needed or appropriate, but a subquery should >work. >Table structure: >PartNumber, Year, Qty, Price [quoted text clipped - 10 lines] >So I'm trying to figure out how to search through the different quantity >breaks to find the correct price to use for calculations. You need to get out of the usual programming mindset of loops and sequential processing. Queries don't work that way! They happen "all at once" - the WHERE clause of a query is just a Boolean logical statement which (eventually) evaluates to True (retrieve this record) or False (don't). You will need a Self Join query joining this table to itself by Partnumber and Year in order to get the price range within the same record: SELECT A.PartNumber, A.[Year], A.[Qty] AS Low, B.[Qty] AS High, A.Price FROM tablename AS A INNER JOIN tablename AS B ON A.PartNumber = B.Partnumber AND A.[Year] = B.[Year] WHERE B.Qty = (SELECT Min(C.[Qty]) FROM tablename AS C WHERE C.Partnumber = A.Partnumber AND C.[Year] = A.[Year] AND C.Qty > A.Qty); As written this will need another record with an "infinite" quantity for the top price bracket, I'm sure there's some clever way to turn the logic around. SignatureJohn W. Vinson [MVP] I get the A and B but whats the C? >>Table structure: >>PartNumber, Year, Qty, Price [quoted text clipped - 19 lines] >As written this will need another record with an "infinite" quantity for the >top price bracket, I'm sure there's some clever way to turn the logic around. >Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3, >Qty1, Qty2, Qty3 [quoted text clipped - 23 lines] > >so for parts 1234 & 4321 I would need to escalate the 2007 price to 2008. Ok... this can be done, even with this mess. But what do you mean by "escalate"? That term may have meaning for you but doesn't for me. John W. Vinson [MVP] Escalate is double talk for Adjust for Inflation. For Example, you take the 2007 Price and Escalate(Inflate) it by 3%. >>Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3, >>Qty1, Qty2, Qty3 [quoted text clipped - 6 lines] > > John W. Vinson [MVP] >Escalate is double talk for Adjust for Inflation. For Example, you take the >2007 Price and Escalate(Inflate) it by 3%. Ok: an Update query should work. Put a unique Index on the combination of fields PartNumber and Year (open the table in design view and use the Indexes tool; put some distinctive name in the left column, PartNumber in the right, and Year on the next row down in the right column, and check the Unique checkbox; or else ctrl-click PartNumber and Year and then the Key icon to make these two fields a joint Primary Key). Then run the following Update query: UPDATE yourtable AS ThisYear INNER JOIN yourtable AS LastYear ON ThisYear.PartNumber = LastYear.PartNumber AND ThisYear.Year = LastYear.Year + 1 SET ThisYear.Qty1 = LastYear.Qty1, ThisYear.Qty2 = LastYear.Qty2, ThisYear.Qty3 = LastYear.Qty3, ThisYear.Price1 = Round(LastYear.Price1 * 1.03, 2), ThisYear.Price2 = Round(LastYear.Price2 * 1.03, 2), ThisYear.Price3 = Round(LastYear.Price3 * 1.03, 2) WHERE ThisYear.Price1 IS NULL AND ThisYear.Price2 IS NULL AND ThisYear.Price3 IS NULL AND LastYear.Price1 IS NOT NULL AND LastYear.Price2 IS NOT NULL And LastYear.Price3 IS NOT NULL; Back up your database first, and check the results carefully of course! John W. Vinson [MVP] One method of handling this NZ([2008 Price],[2007 Price] * (1 + IncreaseFactor])) OR IIF([2008 Price] Is Not Null,[2008 Price],[2007 Price] * (1 + IncreaseFactor])) SignatureJohn Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . > I've got a table that lists parts and prices. Prices are down by Years. > Currently Years are 2007, 2008 & 2009. [quoted text clipped - 7 lines] > Any suggestions? > RHM |
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.