and i have this querey to calculate income need to produce the following queries:Code:

SELECT Equipment.Category, ([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])+(([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])*0.2) AS Income
FROM Equipment INNER JOIN Rent ON Equipment.Equip_ID = Rent.Equip_ID
WHERE (((Rent.act_return_date) Between [Enter the Starting Date] And [Enter the Ending Date]));

the only thing i want to check the user category and give him a discount depending on the type of user which can be chosen from a drop down under Customers table, the categories and discountare:Personal no discountSilver 10%Gold 25%Diamond 40%

quote:the only thing i want to check the user category and give him a discount depending on the type of user which can be chosen from a drop down under Customers table, the categories and discountare:Personal no discountSilver 10%Gold 25%Diamond 40%

use CASE statement as follows:

CASE Equipment.Category WHEN 'Personal' THEN 0% WHEN 'Silver' THEN 10% WHEN 'Gold' THEN 25% WHEN 'Diamond' THEN 40%end as Discount