Hello,
I am hoping someone can help me with a database I am working on for my Church. The object is to combine our various revenue streams into one database for easier reporting and querying. I haven't used a lot of Access in my professional life (I'm an accountant, not a programmer), so I'm more or less teaching myself as I go. So far, I've been able to create modules for most of our revenue streams without much issue. However, I'm stuck on the one that I thought would be the easiest going in to the project.
The current module would be based on our Stewardship information. I am trying to track the annual pledges of our Congregation, along with their weekly payments. I have a table for members (tbl_Members) which includes their personal information. I have a table for the pledge amounts (tbl_Pledges) which links to the member's name in tbl_Members and includes the annual amount of the pledge, the year, a yes/no box indicating whether they would like to receive envelopes and the envelope number (filled in after the fact, once assigned). Finally I have a table for payments (tbl_Payments) which includes the date of payment, the type of payment (chk/cash), the amount of the payment and the check number.
My problem is how to relate the payment table back to the pledge table. The envelope numbers are re-used year after year, but are not necessarily assigned to the same member, so I can't just link the envelope number alone to the payment table. I need to use some combination of the envelope number and the year.
Ultimately, I would like the form for the payments to filter based on year, so the Parish secretary would enter the pledge year and all the envelope numbers for that year populate a list table. She could then just tab down to the correct envelope number to enter the payment information each week. That payment information is saved tbl_Payments which (somehow) links back to the original pledge in tbl_Pledges. From there I can create queries and reports to calculate balances due, etc.
If I write a query that combines the envelope number and year, is it still possible to filter the payment form based on year alone? Even then, I’m still not sure how I would link all the payments back to the correct pledge.
Either I’m missing something obvious and making this far too difficult, or this is really a much more complex module than I anticipated. Either way, I’m stuck. I appreciate any ideas, suggestions, criticisms or direction. I’m happy to do the research; I’m just at a loss as to where to go from here.
Thank you,
Susan
I am hoping someone can help me with a database I am working on for my Church. The object is to combine our various revenue streams into one database for easier reporting and querying. I haven't used a lot of Access in my professional life (I'm an accountant, not a programmer), so I'm more or less teaching myself as I go. So far, I've been able to create modules for most of our revenue streams without much issue. However, I'm stuck on the one that I thought would be the easiest going in to the project.
The current module would be based on our Stewardship information. I am trying to track the annual pledges of our Congregation, along with their weekly payments. I have a table for members (tbl_Members) which includes their personal information. I have a table for the pledge amounts (tbl_Pledges) which links to the member's name in tbl_Members and includes the annual amount of the pledge, the year, a yes/no box indicating whether they would like to receive envelopes and the envelope number (filled in after the fact, once assigned). Finally I have a table for payments (tbl_Payments) which includes the date of payment, the type of payment (chk/cash), the amount of the payment and the check number.
My problem is how to relate the payment table back to the pledge table. The envelope numbers are re-used year after year, but are not necessarily assigned to the same member, so I can't just link the envelope number alone to the payment table. I need to use some combination of the envelope number and the year.
Ultimately, I would like the form for the payments to filter based on year, so the Parish secretary would enter the pledge year and all the envelope numbers for that year populate a list table. She could then just tab down to the correct envelope number to enter the payment information each week. That payment information is saved tbl_Payments which (somehow) links back to the original pledge in tbl_Pledges. From there I can create queries and reports to calculate balances due, etc.
If I write a query that combines the envelope number and year, is it still possible to filter the payment form based on year alone? Even then, I’m still not sure how I would link all the payments back to the correct pledge.
Either I’m missing something obvious and making this far too difficult, or this is really a much more complex module than I anticipated. Either way, I’m stuck. I appreciate any ideas, suggestions, criticisms or direction. I’m happy to do the research; I’m just at a loss as to where to go from here.
Thank you,
Susan
