Query puchase price from table to include percentage of markup fi.

[panterablanco]

Building a database for a company. I want to print a catalog with reports
from the database.
I have the product and purchase price in a table. In another table I have a
number (45) which represents how much each product should be markedup. I want
to be able to query the product, product description and the retail price
(derived from the purchase price times % of markup). Can't seem to find the
proper way to set up that information so that I can add an expression to
calculate the retail price. In a form, it wouldn't be a problem. But in order
to print all coffee in one section all cheese in another (with their retail
price) it seems perplexing. Can someone offer advice?

 

[Armen Stein]

Building a database for a company. I want to print a catalog with reports
from the database.
I have the product and purchase price in a table. In another table I have a
number (45) which represents how much each product should be markedup. I want
to be able to query the product, product description and the retail price
(derived from the purchase price times % of markup). Can't seem to find the
proper way to set up that information so that I can add an expression to
calculate the retail price. In a form, it wouldn't be a problem. But in order
to print all coffee in one section all cheese in another (with their retail
price) it seems perplexing. Can someone offer advice?

Hi,

You can create a calculated expression in a query, by placing something
like this in the Field:

ListPrice: [PurchasePrice] * (1+([Markup]/100))

The query needs to join both tables together on whatever field makes
sense - product category, or something like that. If there is only one
record in the Markup table (in other words, it's a universal value - you
mark *everything* up by the same amount) then you can just use a DLookup
to get the Markup instead of a joined table:

ListPrice: [PurchasePrice]*(1+(DLookUp("Markup","tblMarkup")/100))

This also assumes Markup is an integer - you don't need to divide by 100
if it's actually .45.

Hope this helps,

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com