I'm totally new to access so please bare with me, what I want to do is create a query that takes the price from one column and takes 25% off it if there is 2 units in stock, 30% off if there is 3 units, 40% if there is 4 units in stock and 50% if ther is more than 5 units in stock, the results will then be created in a column called DiscountPrice and the percentage off will will be created in a column called SaleInfo. Is this possible?
in short yes
lets assume you have already got your base query with all the relevant columns (fields) in it
open the query using the query editor
the way of implementing it depends on you
you could use an iif() expression in the query to set a discount rate based on stock level
Stylistically I don't think returning a value is wise, Id suggest you return the discount rate (you can always get the discount from the price * discount rate in any form or report using the values)
If you pla on using this rule in several places then consider deploying the means of calculating the discount rate as either a table, a function or as a standalone query and bring the discount rate through as linked item in the final query.