Hello, I’m working on a database that I’m having some problems with. This database is for a data mining class and I ran into a little snag. I would appreciate any help.
Basically, I’m working on a POS database and would like for it to have discount functionality. Here is how I have the tables set up.
Customer Table
cid – customer id*
cname – customer name
Product Table
pid – product id*
pname – product name
pprice – product price
Transaction Table
tid – transaction id*
cid – customer id (Foreign Key from Customer table)
tdate – transaction date
did – discount id (Foreign Key from Discount table)
Subtransaction Table (contains Transaction table’s actual transactions)
tid – transaction id (Foreign Key from Transaction table)
pid – product id (Foreign Key from Product table)
quantity – quantity
Discount Table
did – discount id*
dminimum – discount minimum [i.e. require $100 to get $10 off]
dtype – discount type [Flat or Percent]
damount – discount amount [i.e. $10 or 10%]
Why do I have Discount Table set up this way?
I wanted to be able to add a Form where someone can add their own discounts, and have a choice of the minimum required for the transaction, and type – Flat or Percent. For example, you can have $10 off $100 minimum, Or 15% of $100 minimum, etc.
The problem
What I want to do is create a query that gives me the total for each transaction (sum of product price x quantity). I can do this by doing this query:
But now I want to take it one step further. I want to create a query that shows the total transaction amount after the discount is applied. I found out the tricky part is because I’m using different types of discounts – Flat or Percent. What I have now is:
Basically, I want it to read:
If Discount Type = Percent, Then (Produce Price * Quantity) * Discount Amount
If Discount Type = Flat, Then (Product Price * Quantity) – Discount Amount
Else, (Product Price * Quantity)[/CODE]
The problem is that it is subtracting the flat discount from each subtransaction, resulting in the same discount being applied multiple times if a transaction has multiple subtransactions. For example, if Transaction 1 has “$10 off discount” and 3 items, a $30 discount is subtracted from the total. I am sure it is doing this to the Percent discount type, but that is fine because of how percentages work.
So I tried to change the IF STATEMENT to:
By removing a set of ( ) in the second nested IF STATEMENT.
And now I’m getting an error that says:
You tried to execute a query that does not include the specified expression ‘IIf(discount.dtype='Percent', Sum((product.pprice*subtransaction.quantity)*discount.damount),
IIf(discount.dtype='Flat', Sum(product.pprice*subtransaction.quantity)-discount.damount, Sum(product.price*subtransaction.quantity)))’ as part of an aggregate function.
Please help! How do I get it so that it only subtract the discount amount once per transaction if it is a Flat discount?
Also, for bonus points
can someone tell me if it's possible to create a Form where I can enter the transactions, subtransactions, and discounts, with the discounts giving an error if the minimum amount is NOT MET. For example, if the discount is $10 off $100, it will shoot out an error if the transaction total is only $90. Of course, I would need all of this to be done with variables because the minimum amount would have to come from the discount table. I know this more of a Form question, but with my limited experience in Access, I’m thinking I will need a query for this to start.
I would've started working on this already, but I've been stumped on this issue and working on it for a while.
Your help is appreciated!
Basically, I’m working on a POS database and would like for it to have discount functionality. Here is how I have the tables set up.
Customer Table
cid – customer id*
cname – customer name
Product Table
pid – product id*
pname – product name
pprice – product price
Transaction Table
tid – transaction id*
cid – customer id (Foreign Key from Customer table)
tdate – transaction date
did – discount id (Foreign Key from Discount table)
Subtransaction Table (contains Transaction table’s actual transactions)
tid – transaction id (Foreign Key from Transaction table)
pid – product id (Foreign Key from Product table)
quantity – quantity
Discount Table
did – discount id*
dminimum – discount minimum [i.e. require $100 to get $10 off]
dtype – discount type [Flat or Percent]
damount – discount amount [i.e. $10 or 10%]
Why do I have Discount Table set up this way?
I wanted to be able to add a Form where someone can add their own discounts, and have a choice of the minimum required for the transaction, and type – Flat or Percent. For example, you can have $10 off $100 minimum, Or 15% of $100 minimum, etc.
The problem
What I want to do is create a query that gives me the total for each transaction (sum of product price x quantity). I can do this by doing this query:
Code:
[B]SELECT[/B] transaction.tid, transaction.tdate, customer.cid, Sum(product.pprice*subtransaction.quantity) AS TransactionTotal
[B]FROM[/B] (customer INNER JOIN (discount INNER JOIN transaction ON discount.did = transaction.did) ON customer.cid = transaction.cid) INNER JOIN (product INNER JOIN subtransaction ON product.pid = subtransaction.pid) ON transaction.tid = subtransaction.tid
[B]GROUP BY[/B] transaction.tid, transaction.tdate, customer.cid
Code:
[B]SELECT[/B] transaction.tid, transaction.tdate, customer.cid,
IIf(discount.dtype='Percent', Sum((product.pprice*subtransaction.quantity)*discount.damount),
IIf(discount.dtype='Flat', Sum((product.pprice*subtransaction.quantity)-discount.damount), Sum(product.price*subtransaction.quantity))) AS TransactionTotal
[B]FROM[/B] (customer INNER JOIN (discount INNER JOIN transaction ON discount.did = transaction.did) ON customer.cid = transaction.cid) INNER JOIN (product INNER JOIN subtransaction ON product.pid = subtransaction.pid) ON transaction.tid = subtransaction.tid
[B]GROUP BY[/B] transaction.tid, transaction.tdate, customer.cid, discount.dtypeIf Discount Type = Percent, Then (Produce Price * Quantity) * Discount Amount
If Discount Type = Flat, Then (Product Price * Quantity) – Discount Amount
Else, (Product Price * Quantity)[/CODE]
The problem is that it is subtracting the flat discount from each subtransaction, resulting in the same discount being applied multiple times if a transaction has multiple subtransactions. For example, if Transaction 1 has “$10 off discount” and 3 items, a $30 discount is subtracted from the total. I am sure it is doing this to the Percent discount type, but that is fine because of how percentages work.
So I tried to change the IF STATEMENT to:
Code:
IIf(discount.dtype='Percent', Sum((product.pprice*subtransaction.quantity)*discount.damount),
IIf(discount.dtype='Flat', Sum(product.pprice*subtransaction.quantity)-discount.damount, Sum(product.price*subtransaction.quantity))) AS TransactionTotalBy removing a set of ( ) in the second nested IF STATEMENT.
And now I’m getting an error that says:
You tried to execute a query that does not include the specified expression ‘IIf(discount.dtype='Percent', Sum((product.pprice*subtransaction.quantity)*discount.damount),
IIf(discount.dtype='Flat', Sum(product.pprice*subtransaction.quantity)-discount.damount, Sum(product.price*subtransaction.quantity)))’ as part of an aggregate function.
Please help! How do I get it so that it only subtract the discount amount once per transaction if it is a Flat discount?
Also, for bonus points
can someone tell me if it's possible to create a Form where I can enter the transactions, subtransactions, and discounts, with the discounts giving an error if the minimum amount is NOT MET. For example, if the discount is $10 off $100, it will shoot out an error if the transaction total is only $90. Of course, I would need all of this to be done with variables because the minimum amount would have to come from the discount table. I know this more of a Form question, but with my limited experience in Access, I’m thinking I will need a query for this to start.I would've started working on this already, but I've been stumped on this issue and working on it for a while.
Your help is appreciated!
Last edited:
