A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with an expression to calculate a profit from one of three fi



 
 
Thread Tools Display Modes
  #1  
Old May 29th, 2010, 11:42 PM posted to microsoft.public.access.forms
Jen
external usenet poster
 
Posts: 544
Default Help with an expression to calculate a profit from one of three fi

Using Access 2003 SP3 - in Forms

I know I'm probably asking a very complex question, so a very big THANK YOU
to whomever can help.

I am doing a detailed inventory and need help with an expression in my form
that will calculate my profit based on one of three fields. Only one field
would have the end data.

To clarify what I am working with: Most stock items come by the "unit" and
contain multiple "subunits" which, in turn, contain smaller "pieces."
However, some "units" only come with "subunits" and some only come as "units."

Here is what I have done so far, along with the (format) and followed by the
expression.

Unit Cost (currency) ~ CS:Unit Cost
Units on hand (number) ~ CS:Units on hand
Cost of all Units on hand (currency) ~ =Nz([UnitCost],0)*Nz([UnitsOnHand],0)
Number of Subunits per Unit (number) ~ CS:Number of Subunits per Unit
Subunit Cost (currency) ~
=IIf([NumbSubUnitsPerUnit]=0,Null,Nz([UnitCost])/Nz([NumbSubUnitsPerUnit]))
Subunits on hand (number) ~ CS:Subunits on hand
Cost of all Subunits on hand (currency)
~ =Nz([SubUnitCost])*Nz([SubUnitsOnHand])
Number of Pieces per Subunit (number) ~ CS:Number of Pieces per Subunit
Piece Cost (currency) ~
=IIf([NumbPiecesPerSubUnit]=0,Null,Nz([SubUnitCost])/Nz([NumbPiecesPerSubUnit]))
Pieces on hand (number) ~ CS:Pieces on hand
Cost of all Pieces on hand(currency) ~ =Nz([PieceCost])*Nz([PiecesOnHand])
Total Cost of All on Hand (currency) ~
=Nz([CostUnitsOnHand])+Nz([CostSubUnitsOnHand])+Nz([CostPiecesOnHand])
Selling At (currency) ~ CS:Sell At

Profit (currency) ~ =[SellingAt]-[??cost on hand??]**

**Here is where I need the help. I need an expression that will calculate
the profit based on whichever of the following fields contain the final "cost
on hand" data:

Cost of all Units on hand , Cost of all Subunits on hand , or Cost of all
Pieces on hand

Typically the final cost on hand data will be in "Cost of all Pieces on
hand", but occasionally it will be in one of the other two. Is there an
expression that I can use that will find the last of the three calculated
data fields and use that data?

If there isn't, how would I go about creating a combo box list that would
include only the calculated data in those fields for only the current record?
And if this is possible, could I then use the value displayed in the combo
box to figure the profit?

Again, Thanks ahead of time.
J
  #2  
Old May 30th, 2010, 10:27 PM posted to microsoft.public.access.forms
Lord Kelvan
external usenet poster
 
Posts: 637
Default Help with an expression to calculate a profit from one of threefi

mmm well my guess would be

[cost on hand]=iif(Nz([PieceCost])*Nz([PiecesOnHand])0,
Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([PieceCost])*Nz([PiecesOnHand])0,
Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand],
0)))

This checks if the cost of pieces is 0 if not then it says the cost on
hand is the cost of pieces if it is 0 then it checks the subunits if
the sub units cost is not 0 it will use the subunits cost as cost on
hand and if that is 0 it will use the units as cost on hand.

Though this code won’t work if you have pieces lets say and they have
no cost to the company then I would use this code

[cost on hand]=iif(Nz([Pieces on hand])0,
Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([Subunits on hand])0,
Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand],
0)))

This will do the same but will check to see if the pieces on hand
value is 0 and so on rather than the cost value

Hope this helps

Regards
Kelvan
  #3  
Old June 2nd, 2010, 08:40 PM posted to microsoft.public.access.forms
Jen
external usenet poster
 
Posts: 544
Default Help with an expression to calculate a profit from one of thre



"Lord Kelvan" wrote:

mmm well my guess would be

[cost on hand]=iif(Nz([PieceCost])*Nz([PiecesOnHand])0,
Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([PieceCost])*Nz([PiecesOnHand])0,
Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand],
0)))

This checks if the cost of pieces is 0 if not then it says the cost on
hand is the cost of pieces if it is 0 then it checks the subunits if
the sub units cost is not 0 it will use the subunits cost as cost on
hand and if that is 0 it will use the units as cost on hand.

Though this code won’t work if you have pieces lets say and they have
no cost to the company then I would use this code

[cost on hand]=iif(Nz([Pieces on hand])0,
Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([Subunits on hand])0,
Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand],
0)))

This will do the same but will check to see if the pieces on hand
value is 0 and so on rather than the cost value

Hope this helps

Regards
Kelvan
.


I have to apologize, I managed to screw up which fields I needed to use to
perform the profit calculation.

I originally stated that I needed the calculation to use whichever of these
three fields was the last field to have a data entry:
Cost of all Units on hand , Cost of all Subunits on hand , or Cost of all
Pieces on hand

I also neglected to say that I was only trying to find the profit for a
single item, in which case I may notice my big mistake about the fields. In
basic math, the profit would be figured as follows:

PROFIT = [SellingAt]- the cost of a single item

The "cost of a single item" would be whichever of these three fields was the
LAST field to have a data entry:

[UnitCost], [SubUnitCost], or [PieceCost]

each of which are calculated based on the previous set of fields. Subunits
are based on units and pieces are based on subunits.

Typically the "cost of a single item" data will be in [PieceCost], but
occasionally it will be in one of the other two. Additionally, if the final
"cost of a single item" data is in the [PieceCost] field, there will also be
data in [UnitCost] and [SubUnitCost] fields that helped calculate the
[PieceCost]. If the final "cost of a single item" data is in the
[SubUnitCost] field, then there will also be data in the [UnitCost] field and
the [PieceCost] will be Null. If the final "cost of a single item" data is in
the [UnitCost] field, then the [SubUnitCost] and [PieceCost] fields will both
be Null.

Is there a calculated expression that I can use that will find the last of
the three calculated data fields and use that data to figure the profit?

If there isn't a way to do what I'm asking using calculated expressions, is
there a way I can set the [Profit] field to ask me which of the three field's
data I'd like to use? Or would it be easier to create a "cost of a single
item" field that would allow me to pull in the data from one of those three
fields so that the expression to calculate the profit in the [Profit] field
would be as simple as the basic math example above?

Again, I apologize for my initial confusion and thanks again in advance.
J
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.