Re: pricing formulas

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Edward G (junkmail4ever_at_att.netREMOVE)
Date: 11/15/04 

Date: Mon, 15 Nov 2004 06:41:03 GMT

Jimmy,

Allen Browne is an Access expert and very comfortable with VBA. I am just a
shade more experienced than an out
and out newbie and would tackle this problem in an entirely different way
since I am NOT comfortable with VBA. I would
construct a query from the Partmaster table that would include the
Partnumber, Description, and Cost and build some expressions in query design
view. Here's how:
1. Open the Database window, select the Query tab and click the "New"
button.
2. Select "Simple Query Wizard and click OK.
3. In the wizard select Partmaster (or equivalent) from the list of tables.
Then choose the fields I mentioned above. Click next, rename the query if
you like, and Finish.
4. In an empty column of the grid in Design view add this expression in the
"Field" row: Price1: IIf([Cost]<=2,[Cost]*4)
(Please substitute the name of your cost field if it is different and rename
Price1 if you so choose). BTW, the colon after Price1 is important. It lets
Access know that this is the name of your new field and that what follows is
an expression. I suggest you cut and paste these expressions and then adjust
the bracketed field names.
5. In the next empty field enter this: Price2: IIf([Cost] Between 2 And
5,[Cost]*3.5)
6. Keep going like this until all your prices are covered. If you would like
all these calculated fields to display in a single column enter
this: AllPrice: CCur([Price1] & [Price2] & [Price3]) in the next open
column. (The Ccur forces the field to display as currency and may be removed
if you don't want it).
7. You can also compare your current retail price to the "formula price"
with: Expr: [RetailPrice]/[AllPrice] but you would first have to add the
RetailPrice field (or whatever your database calls it) to the grid. You
could then sort by this ratio to determine which of your current prices is
most in need of correction.

I hope you find this helpful.

Edward G

"MichiganJimmy" <MichiganJimmy@discussions.microsoft.com> wrote in message
news:6A38AA95-7FDE-4693-8620-B6DE4B345456@microsoft.com...
> How do I set access to figure differant pricing depending on cost. For
> example I have a price list and if the part cost me $0.01-$2.00 I'd like
to
> times by 4 if it cost $2.01-$5.00 I'd like to times by 3.5 and so on,
please
> help, I am new at this and on a dead line.--
> jimmy
>
> --
> jimmy

Relevant Pages

  • Re: Return ID of Lowest Cost
    ... that I could perform the query in the following steps: ... Of the matching price quotes, ... Use the TOP predicate to return only the TOP 1 records (i.e. PriceID). ... where a Price is a part cost at a given quantity and year. ...
    (microsoft.public.access.queries)
  • Re: Return ID of Lowest Cost
    ... Matthew, I haven't gone through this in detail, but could you solve this just omitting the GROUP BY clause, and instead *sorting* the query on the resultant price? ... That should give you the lowest price first, and the PriceID of the row that generated that price. ... where a Price is a part cost at a given quantity and year. ...
    (microsoft.public.access.queries)
  • RE: queries to scrub for contract prices and then compare to exist
    ... Cost], tblContractVendorMicroChip.[Contract Price] ... I don't know the right query to give me all three ... Y/N for a contract price and list the p/n's that are contract parts and their ...
    (microsoft.public.access.queries)
  • Re: Question on scripting
    ... Item Method Price Cost Percentage ... I can do this obviously using multiple When Then Else statements. ... would be to use CASE expressions for the variable parts ...
    (comp.databases.ms-sqlserver)
  • Re: pricing
    ... >example I have a price list and if the part cost me $0.01-$2.00 I'd like to ... return the second member of the first pair for which the first member ... Join this table to your Query with no JOIN line but with a criterion ...
    (microsoft.public.access.gettingstarted)