Calculations Forms and Reports

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[John W. Vinson]

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

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]

 

[Guest]

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?

 

[Guest]

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.
--
Lynette

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?

--
Duane Hookom
Microsoft Access MVP

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