Help

[BarbWest]

How would I write that If Cost is >0<$10.00 multiply 2 times cost, If Cost is >$11.00<$30.00 Multiply by 1.5, If Cost is >$31.00 divide by .7
I have a column name "Var" that has the 2, 1.5 or .7 based on the "Cost". I just can't get it... HELP please. :banghead:

[HiTechCoach]

How would I write that If Cost is >0<$10.00 multiply 2 times cost, If Cost is >$11.00<$30.00 Multiply by 1.5, If Cost is >$31.00 divide by .7
I have a column name "Var" that has the 2, 1.5 or .7 based on the "Cost". I just can't get it... HELP please. :banghead:


First, I would keep it simple by only using multiplication. Convert the division by .7 to multiply by 1.4286 (1/.7).

Second, I would use a lookup table to get the VAR.

Base on what you wrote:
>0<$10.00 is the range of 0.01 to 9.99

>$11.00<$30.00 is the range of 11.01 to 29.99


If Cost is >$31.00 is the range of 31.01 to 999999999.99

Example: table: tblVAR
StartingPrice - data type currency
EndingPrice - data type currency
VAR - data type Numeric Double


The data in the table would look like:
0, 9.99, 2
11.01,29.99, 1.5
31.01,999999999.99, 1.4286

To get the[VAR] value you find the record where the Cost is between the Starting and Ending price

Example using the DLookup() function:


DLookup("VAR","tblVAR", [Cost] & " Between [StartingPrice] and [EndingPrice]")


In a query where you have the fields [Cost] and [Var] you can created a calculated field like this:


... [Cost] * DLookup("VAR","tblVAR", [Cost] & " Between [StartingPrice] and [EndingPrice]") as NewPrice ...

[BarbWest]

First, I would keep it simple by only using multiplication. Convert the division by .7 to multiply by 1.4286 (1/.7).

Second, I would use a lookup table to get the VAR.

Base on what you wrote:
>0<$10.00 is the range of 0.01 to 9.99

>$11.00<$30.00 is the range of 11.01 to 29.99


If Cost is >$31.00 is the range of 31.01 to 999999999.99

Example: table: tblVAR
StartingPrice - data type currency
EndingPrice - data type currency
VAR - data type Numeric Double


The data in the table would look like:
0, 9.99, 2
11.01,29.99, 1.5
31.01,999999999.99, 1.4286

To get the[VAR] value you find the record where the Cost is between the Starting and Ending price

Example using the DLookup() function:


DLookup("VAR","tblVAR", [Cost] & " Between [StartingPrice] and [EndingPrice]")


In a query where you have the fields [Cost] and [Var] you can created a calculated field like this:


... [Cost] * DLookup("VAR","tblVAR", [Cost] & " Between [StartingPrice] and [EndingPrice]") as NewPrice ...

[BarbWest]

Boy am I in over my head.... So do I add the DLookup in the "Var" field on the form or in the Inventory Table? Do I add a new field "NewPrice" to the Inventory Table or the Var Table? Does anything go before and after the ... in the query? Would this be just a calculation query or can I put it in the PartsPricing_qry? I'm so sorry, I've tasked to build the huge database... I'm good at the basic stuff but I have no formal training in VBA. I'm trying to get them to send me to a 3 day class but, well you know how that can be.

[HiTechCoach]

Sounds like you are building some type of Accounting system with inventory control. Is that correct?


The key to success in a good accounting system is building it on the foundation of a well normalized design of tables.

For a good primer on the subject see: Inventory Control: Quantity On Hand (http://hitechcoach.com/microsoft-office/access/access-links/46-access-programming/396-inventory-control-quantity-on-hand-1)

The concept of lookup tables that I am recommending is a very useful tool in Accounting Applications. I have used multiple Lookup tables in the many Accounting and Payroll Applications I have built.


I will answer the following based on the assumption you are building an account system.


So do I add the DLookup in the "Var" field on the form or in the Inventory Table?

No. I would not expect this to have anything to do with the inventory table or inventory form.

This looks like the standard pricing of a sale by taking taking the inventory cost and lookup up use a multiplier based on the number of units sold to get the selling price. If that is correct then it is part of the sales process.


Do I add a new field "NewPrice" to the Inventory Table or the Var Table?

Neither. As above, it would be used in the sales process when yu know the quanity to be sold.


Does anything go before and after the ... in the query?

Yes. the rest of the query. My example was for only one calculated field from a query.


Would this be just a calculation query or can I put it in the PartsPricing_qry?

I don't see how it can be part of a Parts Parts Pricing query when parts only know cost not quantity sold. Parts have costs. The Price is determined when you sell it.


I'm so sorry, I've tasked to build the huge database... I'm good at the basic stuff but I have no formal training in VBA. I'm trying to get them to send me to a 3 day class but, well you know how that can be.

Curious, do you have a strong accounting background?


have you looked at the Northwind example database that comes with Access?