Forum Discussion
Month Year as an ID
Hi Experts, I have been fiddling around with this query for quite some time. I am getting nowhere with it. What I have resorted to is trying to use MonthYearSort in a Where clause in the follow...
You can return the balance for the prior month to each transaction by calling the DateSerial function in a BETWEEN…AND operation in a correlated subquery, e.g. for a table with separate Credit and Debit columns, both of which disallow Nulls (Required property = True in Access) and have a DefaultValue property of 0:
SELECT T1.TransactionDate,T1.Credit,T1.Debit,
NZ((SELECT SUM(Credit-Debit)
FROM TransactionsCD AS T2
WHERE T2.TransactionDate BETWEEN
DATESERIAL(YEAR(T1.TransactionDate),MONTH(T1.TransactionDate)-1,1)
AND DATESERIAL(YEAR(T1.TransactionDate),MONTH(T1.TransactionDate),0)),0)
AS PriorMonthBalance
FROM TransactionsCD AS T1
ORDER BY TransactionDate;
SELECT T1.TransactionDate,T1.Credit,T1.Debit,
NZ((SELECT SUM(Credit-Debit)
FROM TransactionsCD AS T2
WHERE T2.TransactionDate BETWEEN
DATESERIAL(YEAR(T1.TransactionDate),MONTH(T1.TransactionDate)-1,1)
AND DATESERIAL(YEAR(T1.TransactionDate),MONTH(T1.TransactionDate),0)),0)
AS PriorMonthBalance
FROM TransactionsCD AS T1
ORDER BY TransactionDate;
Hi XPS and Ken,
thank you both for your responses.
XPS, i will wait for your response. Many thanks.
Ken, that is amazing. Could that query be grouped by TransactionDate? I have fiddled around with it by adding the grouping (the summation button) but its not grouping like I thought it would. I dont need to show the debit and credit so that might help. I will add this is not exactly what I was looking since I dont have debits and credits...just [Amount] but has given me some ideas and might be able to add efficiency by using this query and making some changes for my application (by summing only the [Amount] not summing debit and credit) but would need to group.
Your response might have been directed at XPS I am not sure. thank you sir...
- To group the transactions by TransactionDate and return the aggregated amount per day would be:
SELECT T1.TransactionDate,SUM(T1.Credit-T1.Debit) AS TotalAmount,
NZ((SELECT SUM(Credit-Debit)
FROM TransactionsCD AS T2
WHERE T2.TransactionDate BETWEEN
DATESERIAL(YEAR(T1.TransactionDate),MONTH(T1.TransactionDate)-1,1)
AND DATESERIAL(YEAR(T1.TransactionDate),MONTH(T1.TransactionDate),0)),0)
AS PriorMonthBalance
FROM TransactionsCD AS T1
GROUP BY TransactionDate;
Having separate Credit and Debit columns or a single Amount column in the base table is immaterial. In the latter case you'd simply SUM(Amount), assuming that the column contains positive values for credits and negative values for debits.
