Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Excel Sheet
Dear experts,
I have some challenge that i am having difficulty designing.
I have an organization that wtith membership (of course).
Each member is required to pay on an annual basis $120.00
So, the base annual duess $120.00
Beginning from 2014, each year should have two columns, one shows how much each member has paid his/her $120.00 dues.
The second column (again for each year) should show how much is left.
For instance, 2014, member pays $60.00 on one column and the other column for 2014 shows the remaining $60.00 to be paid.
Even though the year begins in 2014, it span up to 2050.
There are also miscileanous events such as fund raising. This could occur at any year. That even should show how much a member pledged to pay, how much is paid and how much is left to pay.
Eventually, the total paid and the total remaing should show horizontally and then vertically.
Example:
John Doe pays $60.00 out of annual dues of $120. Then John Doe pledges to pay $100.00 for fund raising and pays $0.00.
At far right will be a total of $60.00 and total of $160.00 by John Doe.
Then at bottom shows total paid for each year and total owed
Can someone please suggest the best way to design the excel spreadship to accommodate this requirement?
Attached spreadsheet shows sample of what I am trying to accomplish.
Thanks a lot for your assistance.
C--Users-sokeh-Desktop-APO.xlsx
I have some challenge that i am having difficulty designing.
I have an organization that wtith membership (of course).
Each member is required to pay on an annual basis $120.00
So, the base annual duess $120.00
Beginning from 2014, each year should have two columns, one shows how much each member has paid his/her $120.00 dues.
The second column (again for each year) should show how much is left.
For instance, 2014, member pays $60.00 on one column and the other column for 2014 shows the remaining $60.00 to be paid.
Even though the year begins in 2014, it span up to 2050.
There are also miscileanous events such as fund raising. This could occur at any year. That even should show how much a member pledged to pay, how much is paid and how much is left to pay.
Eventually, the total paid and the total remaing should show horizontally and then vertically.
Example:
John Doe pays $60.00 out of annual dues of $120. Then John Doe pledges to pay $100.00 for fund raising and pays $0.00.
At far right will be a total of $60.00 and total of $160.00 by John Doe.
Then at bottom shows total paid for each year and total owed
Can someone please suggest the best way to design the excel spreadship to accommodate this requirement?
Attached spreadsheet shows sample of what I am trying to accomplish.
Thanks a lot for your assistance.
C--Users-sokeh-Desktop-APO.xlsx
Asked:
5-
4
1 Solution
LVL 16
This sounds like you're using the wrong tool and would be better suited for an Access Database.
So, you can accomplish something like this with 3 tables and 3 forms.
TABLES:
Members (Id, FirstName, LastName)
Credits (Id, Amount, PostDate, MemberID)
Debits (Id, Amount, PostDate, MemberID)
Forms:
Multi - Credits
Multi - Debits
Standard - Members w/ subforms for debits and credits.
Now, you can get more complex with totals and such, as well as a different main form that displays on open allowing users to search and work with users but here's a quick put-together to give you an idea (and this was done in about 10 minutes, so if you refine it you can make something functional). Access is specifically meant to handle this sort of application vs Excel-- and you can create detailed reports for invoicing, etc. off it.
You can expand Credits and Debits to add categories using Lookup fields. You can add balances to the Members form. You can lock it down so users can't modify the forms, etc.
Again, this is just a sample-- if you plan on using Access you might consider doing a 'Front End' Access app and then a 'Back End' for the tables and then using Linked Tables for your users (so you can create/push updates to the UI without having to take down the app). If I'm being honest, there is a bit of a learning curve from Excel to Access but with a starting point and some time experimenting/googling/ask
If you need help customizing, there are plenty of Access experts who can help if you go that route and you can open questions in that topic.
membercontributions.accdb
TABLES:
Members (Id, FirstName, LastName)
Credits (Id, Amount, PostDate, MemberID)
Debits (Id, Amount, PostDate, MemberID)
Forms:
Multi - Credits
Multi - Debits
Standard - Members w/ subforms for debits and credits.
Now, you can get more complex with totals and such, as well as a different main form that displays on open allowing users to search and work with users but here's a quick put-together to give you an idea (and this was done in about 10 minutes, so if you refine it you can make something functional). Access is specifically meant to handle this sort of application vs Excel-- and you can create detailed reports for invoicing, etc. off it.
You can expand Credits and Debits to add categories using Lookup fields. You can add balances to the Members form. You can lock it down so users can't modify the forms, etc.
Again, this is just a sample-- if you plan on using Access you might consider doing a 'Front End' Access app and then a 'Back End' for the tables and then using Linked Tables for your users (so you can create/push updates to the UI without having to take down the app). If I'm being honest, there is a bit of a learning curve from Excel to Access but with a starting point and some time experimenting/googling/ask
If you need help customizing, there are plenty of Access experts who can help if you go that route and you can open questions in that topic.
membercontributions.accdb
Side note- sorry, so used to just running through questions I don't always check the asker's profile; but considering your certs on EE why not just put together a quick .NET WinForm/SQL app for this vs Access?
You can probably do that way quicker (the only benefit of Access would be if you're handing it off to someone else to maintain who isn't a dev).
You can probably do that way quicker (the only benefit of Access would be if you're handing it off to someone else to maintain who isn't a dev).
Thanks alot Dustin.
I actually thought about doing it in .NET but somehow I thought it could be done in Excel.
I just have to ensure the DB is designed correctly first and .NET would be easy.
I actually thought about doing it in .NET but somehow I thought it could be done in Excel.
I just have to ensure the DB is designed correctly first and .NET would be easy.
Should be pretty straight forward, 4 tables if you want a drop down of transaction types (member fee, donation, etc.).
MemberID links to Debits and Credits, and Debits and Credits TyeId goes to TransactionTypes table.
You could continue to add on depending on usage (Address table linked to MemberId, PhoneNumbers table linked to MemberId, etc.) If you add on addresses or phone numbers do those in separate tables vs. columns in Members for 2 reasons:
>Members can have more than 1 address. If you did Address1 and Address2 columns, you can only store 2 addresses and there may be instances where you need more.
>You can keep historical records without having to delete them or edit them by adding new items and marking the old ones as inactive. Gives you more history and detail for things like audits or following up on payments.
MemberID links to Debits and Credits, and Debits and Credits TyeId goes to TransactionTypes table.
You could continue to add on depending on usage (Address table linked to MemberId, PhoneNumbers table linked to MemberId, etc.) If you add on addresses or phone numbers do those in separate tables vs. columns in Members for 2 reasons:
>Members can have more than 1 address. If you did Address1 and Address2 columns, you can only store 2 addresses and there may be instances where you need more.
>You can keep historical records without having to delete them or edit them by adding new items and marking the old ones as inactive. Gives you more history and detail for things like audits or following up on payments.
You could simply even more and just create a Transactions table-- and from there have a value that indicates if it was a debit or credit. This was just the easiest way to set it up for Access imo but really if you're doing your own forms and controls you might find that more beneficial.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now