[HELP] Getting average of transactions

[janthony]

I have the following tables:

SALES TRANSACTION
Sales Transaction ID*
Date
Employee ID (Foreign Key)
Customer ID (Foreign Key)
Discount ID (Foreign Key)

SUBTRANSACTIONS
Sales Transaction ID (Foreign Key)
Product ID (Foreign Key)
Quantity

PRODUCT
Product ID*
Product Name
Product Price

EMPLOYEE
Employee ID*
Employee Name

CUSTOMER
Customer ID*
Customer Name

DISCOUNT
Discount ID*
Discount Name
Discount Amount




I can figure out how to get total of each transaction. For example, to get the total transaction for each customer (total purchases overall) I use the following query:

SELECT CUSTOMER.NAME, Sum(SUBTRANSACTIONS.QUANTITY*PRODUCT.PRODUCT PRICE*DISCOUNT.DISCOUNT AMOUNT) AS TransactionTotal
FROM (DISCOUNT INNER JOIN (CUSTOMER INNER JOIN [SALES TRANSACTIONS] ON CUSTOMER.CUSTOMER ID = [SALES TRANSACTIONS].CUSTOMER ID) ON DISCOUNT.DISCOUNT ID = [SALES TRANSACTIONS].DISCOUNT ID) INNER JOIN (PRODUCT INNER JOIN SUBTRANSACTIONS ON PRODUCT.PRODUCT ID = SUBTRANSACTIONS.PRODUCT ID) ON [SALES TRANSACTIONS].SALES TRANSACTION ID = SUBTRANSACTIONS.SALES TRANSACTION ID
GROUP BY CUSTOMER.CUSTOMER NAME

This displays something like:
Customer AAA $500
Customer BBB $650
Customer CCC $900
etc.

MY PROBLEM
However, I’m having problems getting the average of each transaction. I’ve tried a lot of different things but at this point, it appears I may not even understand how the AVG function works. What do I need to do to get, for example, the Average Transaction per each individual Customer (as opposed to the total above)?

[June7]

Not seeing Avg used. I see Sum.

What do you mean by 'average of each transaction'? You want the average value of all transactions for each customer = total amount divided by number of transactions?

If those Sum calcs are correct, then use Avg in place of Sum.

Grouping and filtering on names is usually bad - what if there are several John Smith or Mary Jones? Unique customer ID better.

[Dal Jeanis]

First, I'm not sure what your [Discount Amount] field is supposed to be. You're multiplying it into the product price, so it would have to be 0.93 when it represents a 7% discount. Is that what the field really is? Amount usually is a dollar value, and discount is usually the amount by which something is reduced, not the amount which remains.

Second, in order to get the average of the transactions, you would have to calculate the individual transactions. You will need two levels of GROUP BY. Assuming I have put the missing square braces in all the right places, which is a really generous assumption, and assuming your original SQL was correct, then here is a shot at the SQL for the average transaction by customer.

SELECT 
CUSTOMER.NAME, 
Avg(TransactionTotal) As AvgTransaction
FROM 
  (SELECT 
      CUSTOMER.NAME, 
      Sum(SUBTRANSACTIONS.QUANTITY*PRODUCT.[PRODUCT PRICE]*DISCOUNT.[DISCOUNT AMOUNT]) AS    TransactionTotal
   FROM 
    ( (   DISCOUNT 
          INNER JOIN 
          (CUSTOMER 
              INNER JOIN 
              [SALES TRANSACTIONS] 
              ON CUSTOMER.[CUSTOMER ID] = [SALES TRANSACTIONS].[CUSTOMER ID]) 
          ON DISCOUNT.[DISCOUNT ID] = [SALES TRANSACTIONS].[DISCOUNT ID]
      ) 
      INNER JOIN 
      (   PRODUCT 
          INNER JOIN 
          SUBTRANSACTIONS 
          ON PRODUCT.[PRODUCT ID] = SUBTRANSACTIONS.[PRODUCT ID]
      ) 
      ON [SALES TRANSACTIONS].[SALES TRANSACTION ID] = SUBTRANSACTIONS.[SALES TRANSACTION ID]
      GROUP BY CUSTOMER.[CUSTOMER NAME], [SALES TRANSACTIONS].[SALES TRANSACTION ID]
    ) 
   GROUP BY CUSTOMER.[CUSTOMER NAME]
   );

Normally I'd alias all those long table names, but I'm leaving the query as close to the original as possible, since I think the original misunderstands the nature of the required calculation, and I'm just demonstrating the technique.

[janthony]

OMG, you are a genius. Thanks so much. That is exactly what I needed.

What I was trying to do was AVG(TransactionTotal) but leaving out the AS _____. I forgot that aggregate functions always need the AS. I was thinking that since it was already an AS in the inner query, I didn't need it again. I forgot this wasn't VB where you just declare something one time and you can keep using it... arg... so much headache over this one issue preventing me from finishing alot of reports on my database.

Yes, you guessed right; the Discount Amount is a % like 95% for 5% discount, 90% for 10% discount, etc.

I have one other somewhat related question, if someone doesn't mind to help. I can start a new thread if I need too but I'm not sure if it's necessary.

=========

I created a form to view SALES TRANSACTIONS and SUBTRANSACTIONS. If I wanted to delete a SALES TRANSACTIONS record, I get an error that the record is related to other records. I understand this part – the SUBTRANSACTIONS are related to SALES TRANSACTIONS. From my research, it seems that I have to remove the checkbox for “Referential Integrity” in the table relationships.

My concern is that the SUBTRANSACTIONS records would still exist. If I were to run a query, would they be included? My thought is that as long as the queries are created in such a fashion that a SALES TRANSACTION record is part of the query, I wouldn’t have to worry about the existing SUBTRANSACTIONS records. However, if I was to run a query to sum just the quantity of products sold in the SUBTRANSACTIONS table, for example, I would have a problem. But if I were to do it per Customer or Employee, it wouldn’t count those records because they are related to the SALES TRANSACTION table. Is this a correct understanding?

Or am I missing the point and is it just better to force database users to delete each related field first?

[ItsMe]

My two cents.

Just like error trapping, I write the referential integrity into the VBA. I don't bother with the relationships window or the "Maintain Referential Integrity" checkbox. I use code to disable or enable the user's ability to delete and or edit data.

Having said that. I prefer not to allow for deletions. Again, programing manages this. Instead, I archive a record. I use Booleans, timestamps, userID's, and event logs to document changes. If a user wants to make an edit, they go to a special edit form. The form's VBA module does the referential integrity check and archives records to take them out of the general view.

[janthony]

Yeah I wanted to do something like that for the host advantages it offers like tracking user edits, never actually losing data and ability to recall it in the future, etc. For now though, I'm trying to get it done in a simpler fashion. I just wanted to make sure "deleted data" doesn't find it's way into queries and yield inaccurate results.

[Dal Jeanis]

I forgot that aggregate functions always need the AS...

No, but if you don't use the alias, then the result field will be called EXPR001 or some such random designation. You need to give it a specific name if you're going to use it elsewhere.

The problem wasn't AVG, it was what you had to have in order to average anything. Your prior code was summing all transactions together for each customer. I had to do an intermediate step that summed up EACH transaction for each customer (Bold Green code). Since I didn't need the transaction ID for anything, I left out the bold purple code, but the field was there implicitly because of the GROUP BY clause.

SELECT 
CUSTOMER.NAME, 
Avg(TransactionTotal) As AvgTransaction
FROM 
 (SELECT 
      CUSTOMER.NAME, 
      [SALES TRANSACTIONS].[SALES TRANSACTION ID],
     Sum(SUBTRANSACTIONS.QUANTITY*PRODUCT.[PRODUCT PRICE]*DISCOUNT.[DISCOUNT AMOUNT]) AS    TransactionTotal
   FROM 
    ( (   DISCOUNT 
          INNER JOIN 
          (CUSTOMER 
              INNER JOIN 
              [SALES TRANSACTIONS] 
              ON CUSTOMER.[CUSTOMER ID] = [SALES TRANSACTIONS].[CUSTOMER ID]) 
          ON DISCOUNT.[DISCOUNT ID] = [SALES TRANSACTIONS].[DISCOUNT ID]
      ) 
      INNER JOIN 
      (   PRODUCT 
          INNER JOIN 
          SUBTRANSACTIONS 
          ON PRODUCT.[PRODUCT ID] = SUBTRANSACTIONS.[PRODUCT ID]
      ) 
      ON [SALES TRANSACTIONS].[SALES TRANSACTION ID] = SUBTRANSACTIONS.[SALES TRANSACTION ID]
      GROUP BY CUSTOMER.[CUSTOMER NAME], [SALES TRANSACTIONS].[SALES TRANSACTION ID]
    ) 
  GROUP BY CUSTOMER.[CUSTOMER NAME]
   );

As a general rule, if it's all inner joins, I prefer to nest them in a way that it looks simpler -

   FROM 
( ( ( ( (
        PRODUCT 
        INNER JOIN SUBTRANSACTIONS 
        ON PRODUCT.[PRODUCT ID] = SUBTRANSACTIONS.[PRODUCT ID]
        ) 
      INNER JOIN [SALES TRANSACTIONS] 
      ON [SALES TRANSACTIONS].[SALES TRANSACTION ID] = SUBTRANSACTIONS.[SALES TRANSACTION ID])
      )
    INNER JOIN CUSTOMER 
    ON CUSTOMER.[CUSTOMER ID] = [SALES TRANSACTIONS].[CUSTOMER ID]
    ) 
  INNER JOIN DISCOUNT 
  ON DISCOUNT.[DISCOUNT ID] = [SALES TRANSACTIONS].[DISCOUNT ID]
  ) 
  GROUP BY CUSTOMER.[CUSTOMER NAME], [SALES TRANSACTIONS].[SALES TRANSACTION ID]
)

And I Prefer to Alias the tables for readability and conciseness

   FROM 
( ( ( ( (
        PRODUCT AS TP 
        INNER JOIN SUBTRANSACTIONS AS TSub
        ON TP.[PRODUCT ID] = TSub.[PRODUCT ID]
        ) 
      INNER JOIN [SALES TRANSACTIONS] AS TS
      ON TS.[SALES TRANSACTION ID] = TSub.[SALES TRANSACTION ID])
      )
    INNER JOIN CUSTOMER AS TC
    ON TC.[CUSTOMER ID] = TS.[CUSTOMER ID]
    ) 
  INNER JOIN DISCOUNT AS TD
  ON TD.[DISCOUNT ID] = TS.[DISCOUNT ID]
  ) 
  GROUP BY TC.[CUSTOMER NAME], TS.[SALES TRANSACTION ID]
)

That's my preferences, but your mileage may vary.

From my research, it seems that I have to remove the checkbox for “Referential Integrity” in the table relationships.

No, you have to make sure that all the subtransactions are deleted before you delete the transaction record. If you delete the transaction without deleting them, then those orphaned subtransactions just sit there forever, and... YES...cause all the problems that you are envisioning.

The key to this is (as ItsMe said) to enforce that constraint programmatically - use a command button to delete, and in the Onclick event VBA, delete the subtransactions, then delete the transaction.

[janthony]

Ah, Ok. I see.

Thanks for answering those questions. I guess I will just enforce deleting the subrecords of related fields for now.

How can I implement a date parameter query into the code you gave me? I used the code:

SELECT 
CUSTOMER.NAME, 
Avg(TransactionTotal) As AvgTransaction
FROM 
  (SELECT 
      CUSTOMER.NAME, 
      Sum(SUBTRANSACTIONS.QUANTITY*PRODUCT.[PRODUCT PRICE]*DISCOUNT.[DISCOUNT AMOUNT]) AS    TransactionTotal
   FROM 
    ( (   DISCOUNT 
          INNER JOIN 
          (CUSTOMER 
              INNER JOIN 
              [SALES TRANSACTIONS] 
              ON CUSTOMER.[CUSTOMER ID] = [SALES TRANSACTIONS].[CUSTOMER ID]) 
          ON DISCOUNT.[DISCOUNT ID] = [SALES TRANSACTIONS].[DISCOUNT ID]
      ) 
      INNER JOIN 
      (   PRODUCT 
          INNER JOIN 
          SUBTRANSACTIONS 
          ON PRODUCT.[PRODUCT ID] = SUBTRANSACTIONS.[PRODUCT ID]
      ) 
      ON [SALES TRANSACTIONS].[SALES TRANSACTION ID] = SUBTRANSACTIONS.[SALES TRANSACTION ID]
      GROUP BY CUSTOMER.[CUSTOMER NAME], [SALES TRANSACTIONS].[SALES TRANSACTION ID]
WHERE SALES TRANSACTIONS.DATE >= [FORMS]![MAIN FORM]![START DATE] AND SALES TRANSACTIONS.DATE <= [FORMS]![MAIN FORM]![END DATE]  
HAVING CUSTOMER.CUSTOMER NAME Like “*” & [FORMS]![MAIN FORM][CUSTOMER NAME] & "*"
) 
   GROUP BY CUSTOMER.[CUSTOMER NAME]
   );

I added the blue text which I hoped to accomplish what I wanted.
It does filter out customers based on what's on the MAIN FORM form, but it doesn't filter out by date. What am I missing here?

[Dal Jeanis]

Quick lesson:

The reason that I NEVER allow spaces in my table names or field names, and always alias long table names to something short, is because those spaces in a name force me to put those darn brackets [] around the name everywhere it's used. Add brackets around [SALES TRANSACTION] in your blue code and it will likely start working for you.

The reason Customer did work is because ... there are no spaces in the name "Customer".

[janthony]

I don't like to use them too. Actually, I always do just a short version like ST for Sales Transactions or CUS for Customer etc.
I've actually had to retranslate them back-and-forth each time I ask for help because I would think having the long version (i.e. Sales Transactions instead of ST) is easier for others to read who aren't familiar with the database naming convention. Good to know I'm not the only one who does it! Other people I know think it's weird that I do that.

By aliasing tables, do you mean using something like

FROM Customer As C

So then I can just do C.Name instead of Customer.Name? I haven't yet taken advantage of this mostly because I'm a noob and think it'll probably just confuse me more -_-

I will try the brackets!

[Dal Jeanis]

Oh, dear. That explains why I had to add braces to your original SQL code.

No, please just post the SQL you actually use. Long names and names with spaces are MUCH harder to read. You can explain that Cus is Customer, etc when you give the table layouts like in the first post.