Solved Sum of 3 columns sort by 3 diff. dates (1 Viewer)

hrdpgajjar

Registered User.
Local time
Today, 18:21
Joined
Sep 24, 2019
Messages
142
Hi all,
I have a table name FinalPaymentT having following columns,

- FarmerShareRcvdDate
- FarmerShareAmount
- PFMSRcvdDate
- PFMSAmount
- StateTopUpRcvdDate
- StateTopUpAmount

I need Total of Three Amounts (FarmerShareAmount + PFMSAmount + StateTopUpAmount) sort by three dates where,

Dates must be between current financial year i.e. between April-2025 To Today

For sorting between financial years, i have modules named as under,

- fnfinancialyearstart (code below)

Public Function fnFinancialYearStart(Optional ByVal start_date As Date = 1) As Date
Dim x_date As Date
If start_date = 1 Then
start_date = Date
End If
x_date = DateSerial(Year(start_date), 4, 1)
If start_date < x_date Then
x_date = DateAdd("yyyy", -1, x_date)
End If
fnFinancialYearStart = x_date
End Function



- fnfinancialyearend (Code below)

Public Function fnFinancialYearEnd(Optional ByVal start_date As Date = 1) As Date
start_date = fnFinancialYearStart(start_date)
fnFinancialYearEnd = DateAdd("yyyy", 1, start_date) - 1
End Function



Now my query code is as under,

SELECT FinalPaymentT.PFMSRcvdDate, FinalPaymentT.FarmerShareRcvdDate, FinalPaymentT.StateTopUpRcvdDate, FinalPaymentT.RegNo, FinalPaymentT.PFMSAmount, FinalPaymentT.StateTopUpAmount, FinalPaymentT.FarmerShareAmount, [PFMSAmount]+[StateTopUpAmount]+[FarmerShareAmount] AS TotalPayment
FROM FinalPaymentT
WHERE (((FinalPaymentT.PFMSRcvdDate) Between fnFinancialYearStart() And fnFinancialYearEnd()) AND ((FinalPaymentT.RegNo) Not Like "*CC*"));



It returns wrong value,

What i want is
- Sum of PFMSAMOUNT (beween financial years)
- Sum of StateTopUpAmount (between financial years)
- Sum of FarmerShareAmount (betwen financial years)

And Total of above three sums

whenever i tried to sort a date it give wrong sum of other date


Please help


Thanks

- Hardip
 
you can only Aggregate each amount fields from Starting fiscal year up to Ending fiscal year.
 
you can only Aggregate each amount fields from Starting fiscal year up to Ending fiscal year.
means i need to create three different queries and then create a new query to make total of three queries? is there any alternative ?
 
you can try this SQL (somebody might have a better sql)
Replace "YourTableName" with the name of your table.
Code:
SELECT TOP 1 fnFinancialYearStart(1) & " - " & Date() AS Period, 
(SELECT SUM(FarmerShareAmount) FROM YourTableName WHERE FarmerShareRcvdDate BETWEEN fnFinancialYearStart(1) AND DATE()) AS TotalFarmerShareAmount, 
(SELECT SUM(PFMSAmount) FROM YourTableName WHERE PFMSRcvdDate BETWEEN fnFinancialYearStart(1) AND DATE()) AS TotalPFMSAmount, 
(SELECT SUM(StateTopUpAmount) FROM YourTableName WHERE StateTopUpRcvdDate BETWEEN fnFinancialYearStart(1) AND DATE()) AS TotalStateTopUpAmount 
FROM YourTableName;
 
you can try this SQL (somebody might have a better sql)
Replace "YourTableName" with the name of your table.
Code:
SELECT TOP 1 fnFinancialYearStart(1) & " - " & Date() AS Period,
(SELECT SUM(FarmerShareAmount) FROM YourTableName WHERE FarmerShareRcvdDate BETWEEN fnFinancialYearStart(1) AND DATE()) AS TotalFarmerShareAmount,
(SELECT SUM(PFMSAmount) FROM YourTableName WHERE PFMSRcvdDate BETWEEN fnFinancialYearStart(1) AND DATE()) AS TotalPFMSAmount,
(SELECT SUM(StateTopUpAmount) FROM YourTableName WHERE StateTopUpRcvdDate BETWEEN fnFinancialYearStart(1) AND DATE()) AS TotalStateTopUpAmount
FROM YourTableName;
This works as i want. Thanks for your support as always.
 
Any time I see a question asking about calculating across fields, I assume the table isn’t normalized. In this case, I would have created a payment table with fields: TransactionID, [ParentID], TransactionType, TransactionDate, and TransactionAmount. There would be three records rather than one in your current system. Then, you sum the amounts in a basic totals query. When you need to add a new transaction type you could do so without altering a table.
 
Any time I see a question asking about calculating across fields, I assume the table isn’t normalized. In this case, I would have created a payment table with fields: TransactionID, [ParentID], TransactionType, TransactionDate, and TransactionAmount. There would be three records rather than one in your current system. Then, you sum the amounts in a basic totals query. When you need to add a new transaction type you could do so without altering a table.
based on little experience i have in access queries, this is the best and only hassle free work around. Create a table and get whatever you want. will definitely consider this as a solution too. Thanks for the reply.
 

Users who are viewing this thread

Back
Top Bottom