Web Development

Ask something
Discussion Locked

ASP/Access dynamic product prices

0 helpful
3 replies

by mr. origami

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How can I calculate prices? Here's what I have: a table named tblProducts with fields including COST, DISCOUNT, and DISCOUNT TYPE. The problem is, some products are discounted by a dollar amount and others by a percent, like this:
COST DISCOUNT DISCOUNT_TYPE
300 50 %
250 30 $
300 50 $

I need to display the calculated PRICES on a web page. But the math equation changes if the DISCOUNT TYPE is $ or %. How do I construct the SQL to createa VIEW where I have "IFs". Like, IF DISCOUNT_TYPE=$ THEN PRICE = COST-DISCOUNT ELSE IF DISCOUNT_TYPE=% THEN PRICE = (COST-(COST*DISCOUNT/100))

I need to "link" the prices to the products (in tblProducts) and I need the web page to be able to SORTby PRICE. I have done this by storing the PRICES in the table itself, but I know that's poor design. Do I create a View? Stored Procedure?

Other problem is when I "globally" update the DISCOUNT (all products starting with "a" set the DISCOUNT to 35%). The change needs to be reflected in the PRICES and when the prices are stored in the table, they don't change all by themselves.

3 replies

by georgevlob

Your SQL statement will look like this:

SELECT yourTable.Cost, IIf([Discount_Type]="$",[Cost]-[Discount],[Cost]-([cost]*[discount]/100)) AS Price
FROM test;

If you want your web page to update automatically, create a second table that contains the Cost and Price, link your web page to this table. Update the new table with the following Update query:

UPDATE Prices INNER JOIN test ON Prices.Product = test.Product SET Prices.Cost = [test].[cost], Prices.Price = IIf([test].[discount_type]="$",[test].[cost]-[test].[discount],[test].[cost]-([test].[cost]*[test].[discount]/100));

where you have to have a product or product id in both tables so that you can join the two tables with it. In the example above, Price is the new table, test is the original table.

George

by mr. origami

Yup - I found out about the IIF statement the other day, but your extra explanation of the UPDATE query was helpful. Thanks!

This discussion is closed to new replies.