Query Help

[Kathy]

I am defeated , I have searched and searched to find an answer to my Access issue but can’t seem to find it. With all of the information available in this forum alone – I’m sure there is some fundamentals I’m not understanding correctly – HELP!!!!



I have two tables, a tblBalance and tbltransaction.

The tblBalance table is used to balance an account statement it contains the following fields:
BalAccountID (relates to the Account Table)
BalBegDate (the beginning statement date for the month)
BalBegBalance (the beginning statement balance)
BalEndDate (the ending statement date for the month)
BalEndBalance (the ending statement balance for the month)

The tblTransaction table contains transactions applied to the account. The data elements are:
tranAccountID (relates to the Account Table)
tranDate (the date of the transaction)
tranCrAmount (populated if the transaction is a credit to the account)
tranDbAmount (populated if the transaction is a debit to the account)

I am trying to create a query that looks like this:

AccountID Stmt Month Beg Balance Credits Debits End Balance
123456 January 500.00 1000.00 200.00 1,300.00
123456 February 1,300.00 2000.00 100.00 3,200.00

Remarks:
1). AccountID can be from either the tblBalance or tblTransaction table as it is the join component
2). The Stmt Month field I have created in the query by simply formatting the balBegDate using ‘mmmm yyyy’
3). The Credits need to sum if the [tblTransaction].[trandate] is >=[tblBalance].[balBegDate] <= [tblBalance].[balEndDate]
4). The same as above applies to Debits
5). The End Balance is a calculated field by the query (Beg Balance + Credits – Debits) and does not relate to the BalEndBalance in the Balance table (the BalEndBalance is update manually when the account statement is balanced, reviewed and approved.

I have tried to create this query with incorrect results. I have then tried to create a query that contains only the Tran Date, beg balance and Ending Balance Dates to identify the statement month for each transaction, thinking then I could another create a query that sums based on the stmt month.

As I said earlier – I’m Defeated, I have searched the net researching this for days and can’t seem to find what I’m looking to do. I consider myself to be extremely strong in Excel formulas, etc. but very new to Access.

Any assistance is MOST appreciated.

[Robeen]

Could you post the SQL of your query here?

[Kathy]

Thank you so very much for taking the time to review the problem I'm having, I really appreciate it!

This is the SQL that contains all the fields where I would like to total all Credits Amounts and Debit Amounts by statement month within Account ID:

SELECT tblBalance.AccountID AS tblBalance_AccountID, tblBalance.BalBegDate, tblBalance.BalEndDate, tblBalance.BalBegBalance, tblTransaction.AccountID AS tblTransaction_AccountID, tblTransaction.tranDate, tblTransaction.TranCrAmount, tblTransaction.tranDbAmount, Format$([tblBalance].[BalBegDate],'mmmm yyyy') AS StmtMonth
FROM tblBalance INNER JOIN tblTransaction ON tblBalance.[AccountID] = tblTransaction.[AccountID];

Then I tried a query that contained only the dates, tblBalance.balBegDate, tblBalance.balEndDate and tblTransaction.tranDate. Here I was thinking maybe I could add a column to associate each transaction record with the appropriate statement period. Somehow comparing tranDate >=balBegDate and <=balEndDate. I don’t have this logic in my SQL as I don’t know how to do it as it is not a ‘true’ SELECT as I want all records to be evaluated/updated in the new column.

SELECT qryDate1.BalAcctID, qryDate1.TranAcctID, qryDate1.tranDate, qryDate1.BalBegDate, qryDate1.BalEndDate, qryDate1.BalMonth
FROM qryDate1;

[Robeen]

I arrived at the results you wanted by creating three queries.
I'll post the SQL for each of these here and you can get back with me if you have questions.

1. QryGroup_Accts_Month
This one groups the data from tblBalance.

 
SELECT tblBalance.BalAccountID, Month([BalBegDate]) AS StatementMonth, tblBalance.BalBegBalance
FROM tblBalance
GROUP BY tblBalance.BalAccountID, Month([BalBegDate]), tblBalance.BalBegBalance
ORDER BY tblBalance.BalAccountID, Month([BalBegDate]);

2. QryGroupDebits_Credits
This one groups the data from tblTransaction.

SELECT tblTransaction.tranAccountID AS AccountID, Month([tranDate]) AS TranMonth, Sum(tblTransaction.tranCrAmount) AS TotalCredits, Sum(tblTransaction.tranDbAmount) AS TotalDebits
FROM tblTransaction
GROUP BY tblTransaction.tranAccountID, Month([tranDate]);

3. QryGroupAccts_Debit_Credit
This one combines the grouped data from both tables using the two queries above.

SELECT QryGroup_AcctsMonth.BalAccountID, QryGroup_AcctsMonth.StatementMonth, QryGroup_AcctsMonth.BalBegBalance, QryGroupDebits_Credits.TotalCredits, QryGroupDebits_Credits.TotalDebits, [BalBegBalance]+[TotalCredits]-[TotalDebits] AS EndBalance
FROM QryGroup_AcctsMonth INNER JOIN QryGroupDebits_Credits ON (QryGroup_AcctsMonth.StatementMonth = QryGroupDebits_Credits.TranMonth) AND (QryGroup_AcctsMonth.BalAccountID = QryGroupDebits_Credits.AccountID);

For your Months - you should stick with your
Format$([tblBalance].[BalBegDate],'mmmm yyyy') AS StmtMonth
so that if you have data for multiple years they will stay separate in the grouping.
Otherwise all the Novembers for every year that you have in your tables will be grouped together [I know you already thought of that!! ].
I just used 'Month()' to make it quicker for me.

Let me know how it goes!

[Kathy]

Hello Robeen - Sorry for the delayed response, had an unexpected trip out of town. Your response is perfect - THANK YOU and Happy New Year!!!!

[Robeen]

I'm glad I could help, Kathy!!

Mark this as solved if it worked for you! Thanks!