I am creating a database for a sports club which has membership details and finance details.
The tables are:-
tblMembers (FullName, Address, Email, etc)
tblFinance (ID, TransactionCategory, TransactionDate, TransactionAmount, TransactionMember).
The tblFinance "TransactionMember" is linked to tblMembers "FullName".
I have 2 forms:-
When I receive membership fees from a member, I open frmTransactions and input the details (e.g., Income, 22/07/2012, £20.00,
In frmMembers, I have created a tab for "Member Finance" which I want to show a monthly breakdown of the fees paid.
Therefore, I have created 12 unbound text boxes - one for each month.
In the VBA for the form, I have an update button linked to a function to carry out the DSUM function (I have only shown
January's for now):-
JanuaryFeesPaid = DSum("[TransactionAmount]", "tblFinance", "[TransactionMember] = _
'" & Forms!frmMembers!FullName & "' and [TransactionDate] between_
#01/01/2012# and #31/01/2012# and [TransactionCategory]='Income'")
Now, if I put in the following data in frmTransactions:-
Income | 22/01/2012 | £20.00 | John Smith
Income | 20/02/2012 | £15.00 | John Smith
The frmMembers finance tab will show £25.00 in the January fees box but then shows £35.00 in the February fees box. It is
ignoring the date range and is keeping a running total.
Is there any way to have a total for each month?