CODEWITHSUNDEEP
sqldatabasevbams-accessdatabase-design
Bob P
Bob P

Reputation: 237

Applying calculations based on a number of criteria stored in separate tables?

What I need to create is a table containing "Rules" such as overriding prices and applying percentage increases to the price of stock.

For Example:

Sales Price is select from the table containing information about products, then the system needs to check another table to see if that Customer/Product/Product Category has any price rules set against it, such as percentage discount or set price to be overridden to.

How do I get access to first of all check if the customer in question exists in the table, then if the product exists and then if the category exists; and then apply the price change that is stored?

So far we have a PriceRules table that contains the headers:

RuleID | CustomerID | Product Code | Category | Price | Percentage | DateApplied | AppliedBy

The plan is to store the different variables in each column and then search based on the columns.

I'm sure this sounds really confusing so I will be around to answer queries as quickly as possible.

Thanks in advance,

Bob P

Upvotes: 0

Views: 267

Answers (1)

Germann Arlington
Germann Arlington

Reputation: 3353

You can get these results using SQL JOINs: 

SELECT ...
Product.ProductPrice as Price,
CustomerRules.ProductPriceRules as Rules
FROM Product
LEFT JOIN Customer
ON ...
LEFT JOIN CustomerRules
ON Product.ProductID = CustomerRules.ProductID
AND Customer.CustomerID = CustomerRules.CustomerID

LEFT JOIN will return ONLY matching results if any exist, if record does not exist all CustomerRules fields will contain NULL values

Upvotes: 1

Related Questions