Conditional Sums with multiple criteria in the same query (1 Viewer)

[Sanjay_S]

I have a table “trxns_expt”, and a query “Max RDate” in the attached access database. Trxns_Expt contains a lot of fields of raw data pertaining to investments in various funds, of which only the following are relevant for this query:
1. Folio_no: the account number of the investor for that particular fund
2. Prodcode: the code assigned to the product in which the investor has transacted
3. Traddate: the date of the transaction
4. Mod Ttype, which is the transaction type- ‘P’ for purchase, ‘SI’ for a switch-in, ‘DP’ for dividend paid, ‘DR’ for dividend reinvested, ‘R’ for a redemption and ‘SO’ for a switch-out
5. Mod Units: which is the number of units transacted. In case of a withdrawal/ redemption (i.e. where the Mod Ttype is ‘R’ or ‘SO’)

The query tries to build the following datasheet:
1. Folio_no
2. Prodcode
3. Max RDate- the last (most recent) date where an “R” or “SO” transaction has happened for a particular folio_no/ prodcode combination
4. Cum RUnits: The sum of all [Mod Units] where the [Mod Ttype] is “R” or “SO” for a folio_no/prodcode combination
5. Orig Units: The sum of all [Mod Units] where the [Mod Ttype] is “P” or “SI”, and where the [traddate] is less than or equal to the [Max RDate]

1 to 4 above worked out, thanks to some tips in response to previous posts on this forum that I built on. But 5 is not working out. I tried the following:
1.

Orig Units: (SELECT Sum(T2.[Mod Units]) FROM [Trxn_Expt] AS T2 WHERE T2.[Folio_no] = [Trxn_Expt].[Folio_no] And T2.[Prodcode] = [Trxn_Expt].[Prodcode] And T2.[Traddate]<=(SELECT Max(T2.[Traddate]) FROM [Trxn_Expt] AS T2 WHERE T2.[Folio_no] = [Trxn_Expt].[Folio_no] And T2.[Prodcode] = [Trxn_expt].[Prodcode] And (T2.[Mod Ttype]="R" or T2.[Mod Ttype]="SO")) And (T2.[Mod Ttype]="R" or T2.[Mod Ttype]="SO"))

2. Orig Units:

(SELECT Sum(T2.[Mod Units]) FROM [Trxn_Expt] AS T2 WHERE T2.[Folio_no] = [Trxn_Expt].[Folio_no] And T2.[Prodcode] = [Trxn_Expt].[Prodcode] And T2.[Traddate]<= [Max RDate] And (T2.[Mod Ttype]="R" or T2.[Mod Ttype]="SO"))

The first option runs, but produces the same answer as [Cum RUnits]. The second one immediately results in a pop up asking for the parameter value of [Max RDate].

Ideally, this is the datasheet that should come up on running the query:
Folio_No Prodcode Max Rdate Cum Runits Orig Units
2779701013 AL84D 11-12-2015 -6189.681 5986.047
8000151016 AL84D 19-10-2015 -1524.546 1498.645
8000151016 AX513DD 09-11-2015 -31335.433 30859.034

What I get instead is this (on using option 1 for Orig Units):
Max Rdate
folio_no prodcode Max Rdate Cum RUnits Orig Units
2779701013 AL84D 11-12-2015 -6189.68 -6189.68
8000151016 AL84D 19-10-2015 -1524.55 -1524.55
8000151016 AX513DD 09-11-2015 -31335.43 -31335.43

Can someone help me with a solution on the Orig Units issue?

 

[arnelgp]

the criteria for [Orig Units] calculated field should be less than (<) not less than or equal.

 

[Sanjay_S]

the criteria for [Orig Units] calculated field should be less than (<) not less than or equal.

Fixed it, thanks. A stupid error on my part in cutting and pasting part of the sub query. I had to change the [Mod Ttype] in the [Orig Units] criteria at the end to "P" or "SI". It works now.

Thanks for the help yet again, Arnelgp.