Help with Transactions Total w/ Discount Applied

[janthony]

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:

SELECT transaction.tid, transaction.tdate, customer.cid, Sum(product.pprice*subtransaction.quantity) AS TransactionTotal
FROM (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
GROUP BY transaction.tid, transaction.tdate, customer.cid

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:

SELECT 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
FROM (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
GROUP BY transaction.tid, transaction.tdate, customer.cid, discount.dtype

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:

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

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)*disco unt.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!

[Dal Jeanis]

In coding the following, I assumed that your damount is the percentage discount, rather than the percentage remaining after discount. You'll notice that I have created a separate column for pct discount and for flat discounts, because they need to use different aggregate functions to get the right answers. This should allow you to see the results and test easily, and then you can ignore the unneeded columns. TranPricenet is just TranPriceGross - TranDiscPct - TranDiscFlat, so any changes you make to the discount calculations need to get made to the last column as well..

SELECT
  TC.cid,
  First(TC.cname) AS cname,
  TS.tid,
  FIRST(TS.tdate) as tdate,
  SUM(TP.pprice*TS.quantity)  AS TranPriceGross
  SUM(IFF(TD.dtype='Percent',
          TP.pprice*TS.quantity*TD.damount,
          0))  AS TranDiscPct
  MAX(IFF(TD.dtype='Flat',
          TD.damount,
          0))  AS TranDiscFlat,
  (SUM(TP.pprice*TS.quantity) 
 - SUM(IFF(TD.dtype='Percent',
           TP.pprice*TS.quantity*TD.damount,
           0)) 
 - MAX(IFF(TD.dtype='Flat',
           TD.damount,
           0))) AS TranPriceNet,
FROM
   Customer AS TC
   INNER JOIN
      Discount AS TD
      INNER JOIN
         Transaction AS TT
         INNER JOIN
            SubTransaction AS TS 
            INNER JOIN 
            Product AS TP 
            ON TS.pid = TP.pid
         ON TS.tid = TT.tid
      ON TD.did = TT.did
   ON TC.cid = TT.cid
GROUP BY TC.cid, TS.tid;

[Dal Jeanis]

What this is all about is the way that aggregate functions work.

In order for a field or calculation to be allowed in the SELECT of a query with a GROUP BY, the field has to either be one of the fields in the GROUP BY list, or it has to be calculated using an aggregate function that is calculated from the entire group of records that have matching fields in the GROUP BY.

Aggregate functions include COUNT, SUM, MIN, MAX, FIRST, LAST, AVG, and so on.

If a field will always be the same -- for instance, cname when cid is in the GROUP BY list -- then for efficiency you should use FIRST, rather than adding it as a field to the GROUP BY. If the records are always going to be the same, then FIRST, MAX, MIN, and LAST are equivalent. AVG also, if the field is numeric.

So, this will get the same result as the first one, with only a single discount field. I'd tend to use the first one, since it's more explicit, if I planned on supporting this application for any length of time.

SELECT
  TC.cid,
  First(TC.cname) AS cname,
  TS.tid,
  FIRST(TS.tdate) as tdate,
  SUM(TP.pprice*TS.quantity)  AS TranPriceGross
  IFF(First(TD.dtype)='Percent',
      SUM(TP.pprice*TS.quantity*TD.damount),
      IFF(First(TD.dtype)='Flat',
          First(TD.damount),
          0))  AS TranDisc,      
  (SUM(TP.pprice*TS.quantity) 
   - IFF(First(TD.dtype)='Percent',
       SUM(TP.pprice*TS.quantity*TD.damount),
       IFF(First(TD.dtype)='Flat',
           First(TD.damount),
           0))  AS TranPriceNet,
FROM
   Customer AS TC
   INNER JOIN
      Discount AS TD
      INNER JOIN
         Transaction AS TT
         INNER JOIN
            SubTransaction AS TS 
            INNER JOIN 
            Product AS TP 
            ON TS.pid = TP.pid
         ON TS.tid = TT.tid
      ON TD.did = TT.did
   ON TC.cid = TT.cid
GROUP BY TC.cid, TS.tid;