CODEWITHSUNDEEP
sqlms-accessmsbuildms-access-2007
Smiley
Smiley

Reputation: 11

Calculated field with Sum IFF in MS Access 2010: Query and Expression Builder

In MS Access 2010, I successfully wrote a query that gives me the following fields from two seperate tables: [Customer ID], [Product], [Price], [Total Price] and [Payment Method] A customer could have order different product or use different payment method. Now, I am trying to have a calculated field that will give the total/sum of only the products that were paid online by each customer.

The [Payment Method] code for online is a "D". I used the code builder expression with the following expression:

1) Sum(IIf([Customer ID] = [Customer ID] AND [Payment Method] = "D", [Price], NULL))

However, it keeps on giving me this error message: You tried to execute a query that does not inclide the specified expression "Customer ID" as part of aggregate function.

If I want to do it in SQL (or expression builder) how would I do it? Everything I've tried so far leads me to the same error message.

Edit
My full query is:

SELECT CUSTOMER_INFO.ID AS [Customer ID], 
CUSTOMER_INFO.PROD_KEY AS [Product], 
CUSTOMER_INFO.PROD_PRICE AS [Price], 
CUSTOMER_INFO.SUM_PRICE AS [Total Price], 
PAYMENT_TRANZAK.PAY_METHD, 
Sum(IIf([Customer ID]=[Customer ID] And [PAY_MTHD]="D",[Price],[IsNull])) AS [Online Total]

FROM CUSTOMER_INFO INNER JOIN PAYMENT_TRANZAK ON (CUSTOMER_INFO.PROD_KEY= PAYMENT_TRANZAK.SSBSECT_CRN) AND (CUSTOMER_INFO.TERM_CODE_KEY = PAYMENT_TRANZAK.DATE_CODE)

WHERE (
((CUSTOMER_INFO.SUM_PRICE)>0) AND ((PAYMENT_TRANZAK.PAY_METHD) Is Not Null) AND     ((CUSTOMER_INFO.CUST_CODE)="RE" Or (CUSTOMER_INFO.CUST_CODE)="RW") AND   ((CUSTOMER_INFO.DATE_CODE)=[Please enter a transaction date: ]) AND   ((CUSTOMER_INFO.ESTS_CODE)="EL") AND ((CUSTOMER_INFO.STST_CODE)="AS")
)
ORDER BY CUSTOMER_INFO.ID;

Upvotes: 1

Views: 8685

Answers (2)

Brad
Brad

Reputation: 12255

You're trying to perform aggregation on non-aggregated data. In order to do a sum the function needs something over which to sum; a "group" of data. Hence you will need a Group By clause in there. Adding the clause GROUP BY CUSTOMER_INFO.ID will create a sum of the totals for each customer ID. You can add your payment type clause to the where statement, too, to get the proper payment type logic.

SELECT CUSTOMER_INFO.ID AS [Customer ID]

  , Sum([Price]) AS [Online Total] 
FROM CUSTOMER_INFO 
INNER JOIN PAYMENT_TRANZAK 
ON (CUSTOMER_INFO.PROD_KEY= PAYMENT_TRANZAK.SSBSECT_CRN) 
    AND (CUSTOMER_INFO.TERM_CODE_KEY = PAYMENT_TRANZAK.DATE_CODE)
WHERE (((CUSTOMER_INFO.SUM_PRICE)>0) 
    AND ((PAYMENT_TRANZAK.PAY_METHD) Is Not Null) 
    AND ((CUSTOMER_INFO.CUST_CODE)="RE" Or (CUSTOMER_INFO.CUST_CODE)="RW") 
    AND ((CUSTOMER_INFO.DATE_CODE)=[Please enter a transaction date: ]) 
    AND ((CUSTOMER_INFO.ESTS_CODE)="EL") 
    AND ((CUSTOMER_INFO.STST_CODE)="AS"))
    AND PAYMENT_TRANZAK.PAY_METHD="D"
GROUP BY CUSTOMER_INFO.ID
ORDER BY CUSTOMER_INFO.ID;

Because you are not aggregating all the fields of not all the fields are being grouped by it is not possible to express them in this kind of query. These

  • CUSTOMER_INFO.PROD_KEY AS [Product]
  • CUSTOMER_INFO.PROD_PRICE AS [Price]
  • CUSTOMER_INFO.SUM_PRICE AS [Total Price]
  • PAYMENT_TRANZAK.PAY_METHD

thus aren't a good match.

But you know your data better than me, maybe there's a way to fit them in logically. that's up to you.

Edit: You could try a query like this where you don't do any filtering but you jsut do your grouping. This will present everything then you do the filtering on your report or form.

SELECT CUSTOMER_INFO.ID AS [Customer ID]
  , CUSTOMER_INFO.CUST_CODE
  , Sum(CUSTOMER_INFO.PROD_PRICE) AS [Online Total] 
  , Sum(CUSTOMER_INFO.SUM_PRICE) as [SumOfSumPrice]
  , CUSTOMER_INFO.CUST_CODE
  , PAYMENT_TRANZAK.PAY_METHD
  , CUSTOMER_INFO.DATE_CODE
  , CUSTOMER_INFO.ESTS_CODE
  , CUSTOMER_INFO.STST_CODE
FROM CUSTOMER_INFO 
INNER JOIN PAYMENT_TRANZAK 
ON (CUSTOMER_INFO.PROD_KEY= PAYMENT_TRANZAK.SSBSECT_CRN) 
    AND (CUSTOMER_INFO.TERM_CODE_KEY = PAYMENT_TRANZAK.DATE_CODE)
GROUP BY CUSTOMER_INFO.ID
  , CUSTOMER_INFO.CUST_CODE
  , PAYMENT_TRANZAK.PAY_METHD
  , CUSTOMER_INFO.DATE_CODE
  , CUSTOMER_INFO.ESTS_CODE
  , CUSTOMER_INFO.STST_CODE
ORDER BY CUSTOMER_INFO.ID;

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

In MS SQL SERVER, ORACLE, MS ACCESS you need to add all other fields in select clause into aggregate clause.

It may help to start with what Access Help has to say on the subject :

Jet SQL Help:All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.This quote from the Help system implies that all references to fields, even within compound references, must either be aggregated (IE. included in one of the aggregate functions listed above) or included in the GROUP BY clause. Any expression which is of either type is considered aggregated. The aggregate functions can only take field reference expressions which resolve to non-aggregated fields (IE. It is equally invalid to aggregate data more than once).

reference

Since aggregate function is anyway aggregating your query, you may try it without group by clause and see. Or you could include each of those fields. Issue might be mainly in your case, you have two different fields within the IIF yet having nulls not handled. For an aggregate I would use a zero or make sure to have Isnull to sum IIF.

PS: I sent answer from mobile and it seems the full answer has not been published the first time.

Upvotes: 0

Related Questions