I want to sum some fields with criteria (1 Viewer)

[atrium]

I have three tables Transactions, Transaction Types,
The transactions file records relate to Many different financial transaction types. The most common thread is that they all relate to different MatterId's (one matterId to many transactions).
I need to sum 6 different payment transaction types for each matterId. They have to be summed but also take into account that trans code 104 needs to be changed to a negative figure for the sum

I have an expression in the query - TotPayments: IIf([Transactions.TransCode]="104",Sum(([Transactions.AmountTotal])*-1),Sum([Transactions.AmountTotal]))

The fields in my query are MatterId (Transactions) and Group By
TotPayments: (with the above expression
TransCpode (Transactions) Where "101" Or "104" Or "109" Or "199" Or "914" Or "915"

THE PROBLEM Error Your query does not include the specified expression IIf([Transactions.TransCode]="104",Sum(([Transactions.AmountTotal])*-1),Sum([Transactions.AmountTotal])) as part of an aggregate function


This is the code that is presented if I view it in SQL

SELECT Transactions.MatterId, IIf([Transactions.TransCode]="104",Sum(([Transactions.AmountTotal])*-1),Sum([Transactions.AmountTotal])) AS TotPayments
FROM [Transaction Types] RIGHT JOIN Transactions ON [Transaction Types].TransTypeId = Transactions.TransCode
WHERE (((Transactions.TransCode)="101" Or (Transactions.TransCode)="104" Or (Transactions.TransCode)="109" Or (Transactions.TransCode)="199" Or (Transactions.TransCode)="914" Or (Transactions.TransCode)="915"))
GROUP BY Transactions.MatterId;

I really do need some help on this one and it will be much appreciated

 

[cheekybuddha]

Try:

SELECT
  t.MatterId,
  SUM(t.AmountTotal * IIf(t.TransCode = "104", -1, 1)) AS TotPayments
FROM [Transaction Types] ty
RIGHT JOIN Transactions t
        ON ty.TransTypeId = t.TransCode
WHERE t.TransCode IN ("101", "104", "109", "199", "914", "915")
GROUP BY t.MatterId;

Are your TransCodes really a text datatype?

If they are actually a numeric datatype then use:

SELECT
  t.MatterId,
  SUM(t.AmountTotal * IIf(t.TransCode = 104, -1, 1)) AS TotPayments
FROM [Transaction Types] ty
RIGHT JOIN Transactions t
        ON ty.TransTypeId = t.TransCode
WHERE t.TransCode IN (101, 104, 109, 199, 914, 915)
GROUP BY t.MatterId;

Do you really need to include table [Transaction Types] in this query?

 

[arnelgp]

you don't need to involve TransCode table when you know which TransCode to sum?

SELECT
  t.MatterId,
  SUM(t.AmountTotal * IIf(t.TransCode = "104", -1, 1)) AS TotPayments
FROM Transactions t
WHERE t.Transtype IN ("101", "104", "109", "199", "914", "915")
GROUP BY t.MatterId;

 

[cheekybuddha]

you don't need to involve TransCode table when you know which TransCode to sum?

Yup, that's what I was thinking too.

 

[plog]

Counterpoint--[Transactions Types] should be included in the query. In doing so the SELECT and the WHERE should be simpler.

[Transaction Types] should have a field that groups these 6 fields together such that you don't have to explicitly list them in the WHERE. Let's say this query is for all "Blah" transactions and those are defined by those 6 fields. You would have a [Category] field in [Transaction Types] and in each of those 6 records you would have "Blah" in the [Category] field. Then in your query, instead of listing each code, you simply use :

...WHERE [Transaction Types].[Category]="Blah"...

If another code needs to be used in the future (or one removed) you simply update [Transaction Types] and never have to dig into the SQL of this query.

Additionally, [Transaction Types] should have a [TransactionValue] field. This will determine if you add or subtract the transaction (1, -1). Doing this simplifies the SELECT to :

SELECT t.MAtterID, SUM(t.AmountTotal*[Transaction Types].[TransactionVAlue]) AS TotPayments...

And just like before, doesn't require you to modify SQL if any codes change.

 

[Pat Hartman]

Hard coding the transition types is a nightmare of a solution. A better option would be to add a way of grouping the transactions so you can use a group code to select the transactions you want to sum. Also, it is best to keep the transaction amounts and quantities with their natural sign. BUT, if you insist on keeping them all as positive numbers, then the transaction definition table should have a Multiplier value. It will be either 1 or -1 and every amount/quantity is multiplied by it so no IIf() is required.

Tablize everything you can to simplify your code.

 

[atrium]

Thank you everyone, what a great help

 

[atrium]

Thank you e

veryone, what a great help

Thank you everyone, what a great help

I haven't got it working

I have now used

SELECT t.MatterId, t.TransCode, Sum(t.AmountTotal*[Transaction Types].[ValueType]) AS TotPayments
FROM [Transaction Types] RIGHT JOIN Transactions AS t ON [Transaction Types].TransCode = t.TransCode
WHERE (((t.TransCode) In (101,104,109,199,914,915)))
GROUP BY t.MatterId, t.TransCode;

I have changed the Transaction Types TransCode to a number type (the first image shows that)

But for some reason the result is using TransCode 101 as the TransTypeId ( Primary key of Transaction Types) and picks up transaction TransCode 812

Ha Ha is it because I have a lookup back to the Transaction Types on the transactions.TransCode

 

[plog]

TransCode 101 as the TransTypeId ( Primary key of Transaction Types) and picks up transaction TransCode 812

Huh? No where in your SQL do you reference TransTypeId. Further, why isn't TransCode the primary key of the [Transaction Types] table? Is that field not unique? Can you have multiple records for TransCode=104? If so, what differentiates them?