 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2007Calculations Forms and Reports
I have a form that I am calculating the price of an item based on its packaging. For example if the Packaging = Each then I need the individual price, if the packaging is pack I need the pack price etc. My delmia is how do I take that calculated price and mutiply it but the quanity to get the value of the the item. And how do I get both the item price and the total value to show up in my report. I tried to do this in a query, but the query would not take the long if else statement required to produce the Item Price based on the packaging. Any suggestions.  SignatureLynette Someone might be able to help if we had information about your table structures and data. Without some specifics, no one can be of much assistance. SignatureDuane Hookom Microsoft Access MVP > I have a form that I am calculating the price of an item based on its > packaging. For example if the Packaging = Each then I need the individual [quoted text clipped - 5 lines] > query, but the query would not take the long if else statement required to > produce the Item Price based on the packaging. Any suggestions. The table consits of the following fields Tent # Location Box Number Item Description Item Quanity Item Packaging Each Price Pack Price Box Price I created a calculation field called getitemprice here is my code Private Function getitemprice() As Currency If PACKINGTYPE = "EACH" Then getitemprice = WIEACHPRICE ElseIf PACKINGTYPE = "PACK" Then getitemprice = WIPACKPRICE ElseIf PACKINGTYPE = "BOX" Then getitemprice = WIBOXPRICE Else End If End Function The price shows up in the calculated field on the form. My question is how do I get this calculation in my table. Yes, I do need it in my table. The price will not change once it has been calculated. I then need to use this calculated answer to figure my total price for the item (boss wants both the individual item price and the total price based on the quanity). Exp Sparkler #8 161 Each = .50 each X 161 = $80.50. SignatureLynette > Someone might be able to help if we had information about your table > structures and data. Without some specifics, no one can be of much assistance. [quoted text clipped - 8 lines] > > query, but the query would not take the long if else statement required to > > produce the Item Price based on the packaging. Any suggestions. Do you really only have one significant table regarding this question? What table is storing the value "Each" as well as the results of your calculations? SignatureDuane Hookom Microsoft Access MVP > The table consits of the following fields > [quoted text clipped - 40 lines] > > > query, but the query would not take the long if else statement required to > > > produce the Item Price based on the packaging. Any suggestions. No the table is based on a query that brings together data from a three tables . One tables has the prices, on table has the box detail (item, quantity, packing) and other table has the tent info. I want this table to have the calulation values - once i get it to work right, i will have to make the query a criteria query based on tent number so i can get a specific value of each tent's items. SignatureLynette > Do you really only have one significant table regarding this question? > What table is storing the value "Each" as well as the results of your [quoted text clipped - 44 lines] > > > > query, but the query would not take the long if else statement required to > > > > produce the Item Price based on the packaging. Any suggestions. I don't see where you want to store the actual price. I expect you want to store a value like "Each", "Pack", or "Box" in the Item Packaging field. I think your table structures aren't correct. SignatureDuane Hookom Microsoft Access MVP > No the table is based on a query that brings together data from a three > tables . One tables has the prices, on table has the box detail (item, [quoted text clipped - 52 lines] > > > > > query, but the query would not take the long if else statement required to > > > > > produce the Item Price based on the packaging. Any suggestions. Because these items are repacks (left overs fireworks). Each item is packed and listed on a sticker that has a bar code. Each bar code is then entered by which tent returned the items. The tent is then credit for the return inventory. Each product may be returned in one of four packings each, pack, box, brick. Each packing has a different price. The prices will not change because our price lists are set each season. So the value are not going to change if they return 50 firecrackers at .75 the value of their credit is 37.50. Now the boxes will be reissued at the same prices to following season but not necessarily to the same tent. But the value stays the same because the price stickers are already on them and we honor that price. So the boss wants me to be able to produce a report (credit memo) for the repacks being returned and the same type of report but as an invoice for the repacks being reissued. Confused yet, I have been working on this project since last August. Wright now, I need to be able to produce a report for data I lost and had to retype that allows me to show each box # the item the quanity return the price for the packing being returned and the total of the quanity times the packing price. Unfortuantley time is a factor today. SignatureLynette > I don't see where you want to store the actual price. I expect you want to > store a value like "Each", "Pack", or "Box" in the Item Packaging field. [quoted text clipped - 57 lines] > > > > > > query, but the query would not take the long if else statement required to > > > > > > produce the Item Price based on the packaging. Any suggestions. After all of your explanation, I still don't know where you want to store your values of price etc. I would expect you to have a table like: tblInventoryReturns ================= InvRetID autonumber primary key TentNum ItemID ItemQty ItemPackaging ItemQuantity ItemUnits (values like "Each", "Pack","Box",...) UnitPrice SignatureDuane Hookom Microsoft Access MVP > Because these items are repacks (left overs fireworks). Each item is packed > and listed on a sticker that has a bar code. Each bar code is then entered [quoted text clipped - 81 lines] > > > > > > > query, but the query would not take the long if else statement required to > > > > > > > produce the Item Price based on the packaging. Any suggestions. I have a similar table called Repack Box Values It has Box # ItemDescription Quanity Packing This where I want to put Individal Value and Quanity Value SignatureLynette > After all of your explanation, I still don't know where you want to store > your values of price etc. [quoted text clipped - 96 lines] > > > > > > > > query, but the query would not take the long if else statement required to > > > > > > > > produce the Item Price based on the packaging. Any suggestions. >I have a similar table called Repack Box Values > [quoted text clipped - 5 lines] > >This where I want to put Individal Value and Quanity Value If you want to store the values, then you need fields into which to store the values. Are Values the same as Price in your model? John W. Vinson [MVP] Is [Repack Box Values] where you want to store the data that you have been asking how to store since the beginning of this thread? If so, is there a reason why I don't see a TentNumber field? SignatureDuane Hookom Microsoft Access MVP > I have a similar table called Repack Box Values > [quoted text clipped - 106 lines] > > > > > > > > > query, but the query would not take the long if else statement required to > > > > > > > > > produce the Item Price based on the packaging. Any suggestions. I believe that is where I want it, it seems the best place. As for the tent number - more than one tent may be assigned to each box depending on if it is coming back to the warehouse at the end of the season, being staged for distrubtion (on a skid), or being sent out as part on an intial order at the begining of a season. So the tent number may fluctuate - but the value will not. I hope this makes sense. SignatureLynette > Is [Repack Box Values] where you want to store the data that you have been > asking how to store since the beginning of this thread? If so, is there a [quoted text clipped - 110 lines] > > > > > > > > > > query, but the query would not take the long if else statement required to > > > > > > > > > > produce the Item Price based on the packaging. Any suggestions. |
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.