If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help finding information on Database design
I'm looking for some information on how to best design a transaction table
for monthly payments in regards to rentals. The specifics things I can think of is each account has a account number and each unit has a number. Payments are posted using both the account number and unit number. Payments can also be made by check, cash, credit card, money order, or combinations of all the above. The payment will have to be divided up into things like rent late fees, merchandise purchase (locks). It is also possible that we receive a payment that is short (Previous Balance Due) or over (Credits). Every one is due on the 1st of the month so PaidFrom and PaidThru dates, late fees etc are all based on this Ledger. It is also possible that some of the charges could be waved, which further complicates it. Oh and the unit/custno combo is subject to change in that a tenant may vacate one unit but still have one and another tenant move into the old unit all within the same day. Hope that is enough information to get me started on this little project. -- Thanks Joe Cilinceon |
#2
|
|||
|
|||
Joe
Just an aside, it seems like you are willing to spend considerable effort re-designing and re-inventing the wheel. Have you already ruled out using one of the commercial accounting/bookkeeping packages? -- Good luck Jeff Boyce Access MVP "Joe Cilinceon" wrote in message ... I'm looking for some information on how to best design a transaction table for monthly payments in regards to rentals. The specifics things I can think of is each account has a account number and each unit has a number. Payments are posted using both the account number and unit number. Payments can also be made by check, cash, credit card, money order, or combinations of all the above. The payment will have to be divided up into things like rent late fees, merchandise purchase (locks). It is also possible that we receive a payment that is short (Previous Balance Due) or over (Credits). Every one is due on the 1st of the month so PaidFrom and PaidThru dates, late fees etc are all based on this Ledger. It is also possible that some of the charges could be waved, which further complicates it. Oh and the unit/custno combo is subject to change in that a tenant may vacate one unit but still have one and another tenant move into the old unit all within the same day. Hope that is enough information to get me started on this little project. -- Thanks Joe Cilinceon |
#3
|
|||
|
|||
Yes, we have looked at several deacated to the storage business and all are
out of our budget till late next year. In the meantime we are using old paper ledgers which is just not getting it. I done quite a bit already to get this onto a modern system however I still have much to do. Joe "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Joe Just an aside, it seems like you are willing to spend considerable effort re-designing and re-inventing the wheel. Have you already ruled out using one of the commercial accounting/bookkeeping packages? -- Good luck Jeff Boyce Access MVP "Joe Cilinceon" wrote in message ... I'm looking for some information on how to best design a transaction table for monthly payments in regards to rentals. The specifics things I can think of is each account has a account number and each unit has a number. Payments are posted using both the account number and unit number. Payments can also be made by check, cash, credit card, money order, or combinations of all the above. The payment will have to be divided up into things like rent late fees, merchandise purchase (locks). It is also possible that we receive a payment that is short (Previous Balance Due) or over (Credits). Every one is due on the 1st of the month so PaidFrom and PaidThru dates, late fees etc are all based on this Ledger. It is also possible that some of the charges could be waved, which further complicates it. Oh and the unit/custno combo is subject to change in that a tenant may vacate one unit but still have one and another tenant move into the old unit all within the same day. Hope that is enough information to get me started on this little project. -- Thanks Joe Cilinceon |
#4
|
|||
|
|||
I asked because Quickbooks, among others, came to mind. I believe Microsoft
"adopted" Great Plains accounting software also, and may have reasonable costs. Have you factored in the value of your time? Jeff Boyce Access MVP "Joe Cilinceon" wrote in message ... Yes, we have looked at several deacated to the storage business and all are out of our budget till late next year. In the meantime we are using old paper ledgers which is just not getting it. I done quite a bit already to get this onto a modern system however I still have much to do. Joe "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Joe Just an aside, it seems like you are willing to spend considerable effort re-designing and re-inventing the wheel. Have you already ruled out using one of the commercial accounting/bookkeeping packages? -- Good luck Jeff Boyce Access MVP "Joe Cilinceon" wrote in message ... I'm looking for some information on how to best design a transaction table for monthly payments in regards to rentals. The specifics things I can think of is each account has a account number and each unit has a number. Payments are posted using both the account number and unit number. Payments can also be made by check, cash, credit card, money order, or combinations of all the above. The payment will have to be divided up into things like rent late fees, merchandise purchase (locks). It is also possible that we receive a payment that is short (Previous Balance Due) or over (Credits). Every one is due on the 1st of the month so PaidFrom and PaidThru dates, late fees etc are all based on this Ledger. It is also possible that some of the charges could be waved, which further complicates it. Oh and the unit/custno combo is subject to change in that a tenant may vacate one unit but still have one and another tenant move into the old unit all within the same day. Hope that is enough information to get me started on this little project. -- Thanks Joe Cilinceon |
#5
|
|||
|
|||
I'm cheap and get paid regardless, joking of course. This is a real small
operation of just my wife and I but will look into it. -- Joe Cilinceon "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... I asked because Quickbooks, among others, came to mind. I believe Microsoft "adopted" Great Plains accounting software also, and may have reasonable costs. Have you factored in the value of your time? Jeff Boyce Access MVP "Joe Cilinceon" wrote in message ... Yes, we have looked at several deacated to the storage business and all are out of our budget till late next year. In the meantime we are using old paper ledgers which is just not getting it. I done quite a bit already to get this onto a modern system however I still have much to do. Joe "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Joe Just an aside, it seems like you are willing to spend considerable effort re-designing and re-inventing the wheel. Have you already ruled out using one of the commercial accounting/bookkeeping packages? -- Good luck Jeff Boyce Access MVP "Joe Cilinceon" wrote in message ... I'm looking for some information on how to best design a transaction table for monthly payments in regards to rentals. The specifics things I can think of is each account has a account number and each unit has a number. Payments are posted using both the account number and unit number. Payments can also be made by check, cash, credit card, money order, or combinations of all the above. The payment will have to be divided up into things like rent late fees, merchandise purchase (locks). It is also possible that we receive a payment that is short (Previous Balance Due) or over (Credits). Every one is due on the 1st of the month so PaidFrom and PaidThru dates, late fees etc are all based on this Ledger. It is also possible that some of the charges could be waved, which further complicates it. Oh and the unit/custno combo is subject to change in that a tenant may vacate one unit but still have one and another tenant move into the old unit all within the same day. Hope that is enough information to get me started on this little project. -- Thanks Joe Cilinceon |
#6
|
|||
|
|||
Another way of looking at that, Joe, is that this project is occupying 50%
of your workforce! :-) The value of your time, of course, is how much you would have made if you had spent it doing, um, whatever you would have been doing if you hadn't been doing this! :-) -- Brendan Reynolds (MVP) http://brenreyn.blogspot.com The spammers and script-kiddies have succeeded in making it impossible for me to use a real e-mail address in public newsgroups. E-mail replies to this post will be deleted without being read. Any e-mail claiming to be from brenreyn at indigo dot ie that is not digitally signed by me with a GlobalSign digital certificate is a forgery and should be deleted without being read. Follow-up questions should in general be posted to the newsgroup, but if you have a good reason to send me e-mail, you'll find a useable e-mail address at the URL above. "Joe Cilinceon" wrote in message ... I'm cheap and get paid regardless, joking of course. This is a real small operation of just my wife and I but will look into it. -- Joe Cilinceon "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... I asked because Quickbooks, among others, came to mind. I believe Microsoft "adopted" Great Plains accounting software also, and may have reasonable costs. Have you factored in the value of your time? Jeff Boyce Access MVP "Joe Cilinceon" wrote in message ... Yes, we have looked at several deacated to the storage business and all are out of our budget till late next year. In the meantime we are using old paper ledgers which is just not getting it. I done quite a bit already to get this onto a modern system however I still have much to do. Joe "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Joe Just an aside, it seems like you are willing to spend considerable effort re-designing and re-inventing the wheel. Have you already ruled out using one of the commercial accounting/bookkeeping packages? -- Good luck Jeff Boyce Access MVP "Joe Cilinceon" wrote in message ... I'm looking for some information on how to best design a transaction table for monthly payments in regards to rentals. The specifics things I can think of is each account has a account number and each unit has a number. Payments are posted using both the account number and unit number. Payments can also be made by check, cash, credit card, money order, or combinations of all the above. The payment will have to be divided up into things like rent late fees, merchandise purchase (locks). It is also possible that we receive a payment that is short (Previous Balance Due) or over (Credits). Every one is due on the 1st of the month so PaidFrom and PaidThru dates, late fees etc are all based on this Ledger. It is also possible that some of the charges could be waved, which further complicates it. Oh and the unit/custno combo is subject to change in that a tenant may vacate one unit but still have one and another tenant move into the old unit all within the same day. Hope that is enough information to get me started on this little project. -- Thanks Joe Cilinceon |
#7
|
|||
|
|||
Yes, I know but the powers that be said no until the end of next year.
Things like concrete work, new doors, new roofs, our salaries etc have all the money spent until the last quarter of next year. Now with that said the only time problem is filling it. This is a small 455 unit storage facility that is pretty stable for 15 years now. With my wife and I it only requires about 2 real days of work out of a normal week, my time isn't that filled. To give you a better idea of what we took over on Oct 1 of this year. No computer, paper ledgers, hand written letters and such. Since Nov. I've managed to get it converted over to a very simple Access database system that now handles most of the things that where done in the past by hand. I do own Office XP Developers. I'm also a retired systems analyst, though it was with a school system and had little to do with computers. I've not programmed since DOS and Windows 3.1 and then nothing major. I also have enjoyed doing what I've done so far and would love to take it all the way if for no other reason than just self satisfaction of learning new things even at my age. What I've done so far is get the tenant records finished and working very smooth. These include all information including contact info. The unit tables are done and tied in with the tenants files so we know who is in what unit as well as well as what each person pays for their units. The payment side of this is where I am at now even though the method used at the moment is getting the results. With what I have written so far, we generate about 20 reports required by the investors that where done by hand, as well as late notices, invoices, payment receipts, auction letters, deposit slips. I have even added a competition survey section as well as payroll, bank deposits, credit card tracking. I even have a reservation system, waiting list, and the ability to scan tenant id into the system. I send the reports now by email in PDF format to the investors. All of this has taken me since Nov 1 to accomplish. Now, I want to bring the payment side up to more than a simple spreadsheet that requires us to enter every detail of a payment. It works just like the paper system did but is very cumbersome though more accurate than the paper system. This ledger has a Transaction number (autonumber) custno, unitno, date paid, how much, payment method, tracking number (check number, credit card approval code), dates if from and thru. It gets very had to do when you get partial payments, split payments (cash and check etc), over payments that don't cover a full month, so we wind up with a credit. We also have to deal with bounced checks, units in lien, auctioned units (sold to general public at auction). etc. Well I didn't mean to go on this long but I've really been frustrated in finding examples for this aspect of database applications. It seems all examples are for the same things and nothing on accounting at all. If I need to track inventory, ship goods or had to list suppliers I would be covered. g -- Joe Cilinceon "Brendan Reynolds" brenreyn at indigo dot ie wrote in message ... Another way of looking at that, Joe, is that this project is occupying 50% of your workforce! :-) The value of your time, of course, is how much you would have made if you had spent it doing, um, whatever you would have been doing if you hadn't been doing this! :-) -- Brendan Reynolds (MVP) SNIPPED for size |
#8
|
|||
|
|||
That sounds very ambitious, Joe. Don't you think that the end of next year
will have come and gone long before all that is finished? Mind you, if you have the time, and you're enjoying it, good luck to you. There is a potential 'middle way' in between buying an off-the-shelf package and doing it all yourself, which is to buy a package with source code included that you can customize to your needs. Here's one of the best known .... http://www.databasecreations.com/ And Tony Toews has a list of such at http://www.granite.ab.ca/accsacct.htm -- Brendan Reynolds (MVP) http://brenreyn.blogspot.com The spammers and script-kiddies have succeeded in making it impossible for me to use a real e-mail address in public newsgroups. E-mail replies to this post will be deleted without being read. Any e-mail claiming to be from brenreyn at indigo dot ie that is not digitally signed by me with a GlobalSign digital certificate is a forgery and should be deleted without being read. Follow-up questions should in general be posted to the newsgroup, but if you have a good reason to send me e-mail, you'll find a useable e-mail address at the URL above. "Joe Cilinceon" wrote in message ... Yes, I know but the powers that be said no until the end of next year. Things like concrete work, new doors, new roofs, our salaries etc have all the money spent until the last quarter of next year. Now with that said the only time problem is filling it. This is a small 455 unit storage facility that is pretty stable for 15 years now. With my wife and I it only requires about 2 real days of work out of a normal week, my time isn't that filled. To give you a better idea of what we took over on Oct 1 of this year. No computer, paper ledgers, hand written letters and such. Since Nov. I've managed to get it converted over to a very simple Access database system that now handles most of the things that where done in the past by hand. I do own Office XP Developers. I'm also a retired systems analyst, though it was with a school system and had little to do with computers. I've not programmed since DOS and Windows 3.1 and then nothing major. I also have enjoyed doing what I've done so far and would love to take it all the way if for no other reason than just self satisfaction of learning new things even at my age. What I've done so far is get the tenant records finished and working very smooth. These include all information including contact info. The unit tables are done and tied in with the tenants files so we know who is in what unit as well as well as what each person pays for their units. The payment side of this is where I am at now even though the method used at the moment is getting the results. With what I have written so far, we generate about 20 reports required by the investors that where done by hand, as well as late notices, invoices, payment receipts, auction letters, deposit slips. I have even added a competition survey section as well as payroll, bank deposits, credit card tracking. I even have a reservation system, waiting list, and the ability to scan tenant id into the system. I send the reports now by email in PDF format to the investors. All of this has taken me since Nov 1 to accomplish. Now, I want to bring the payment side up to more than a simple spreadsheet that requires us to enter every detail of a payment. It works just like the paper system did but is very cumbersome though more accurate than the paper system. This ledger has a Transaction number (autonumber) custno, unitno, date paid, how much, payment method, tracking number (check number, credit card approval code), dates if from and thru. It gets very had to do when you get partial payments, split payments (cash and check etc), over payments that don't cover a full month, so we wind up with a credit. We also have to deal with bounced checks, units in lien, auctioned units (sold to general public at auction). etc. Well I didn't mean to go on this long but I've really been frustrated in finding examples for this aspect of database applications. It seems all examples are for the same things and nothing on accounting at all. If I need to track inventory, ship goods or had to list suppliers I would be covered. g -- Joe Cilinceon "Brendan Reynolds" brenreyn at indigo dot ie wrote in message ... Another way of looking at that, Joe, is that this project is occupying 50% of your workforce! :-) The value of your time, of course, is how much you would have made if you had spent it doing, um, whatever you would have been doing if you hadn't been doing this! :-) -- Brendan Reynolds (MVP) SNIPPED for size |
#9
|
|||
|
|||
WOW and I thought the storage software was expensive. If I could get the
accounting packages we could get the complete storage package. Thanks again. -- Joe Cilinceon "Brendan Reynolds" brenreyn at indigo dot ie wrote in message ... That sounds very ambitious, Joe. Don't you think that the end of next year will have come and gone long before all that is finished? Mind you, if you have the time, and you're enjoying it, good luck to you. There is a potential 'middle way' in between buying an off-the-shelf package and doing it all yourself, which is to buy a package with source code included that you can customize to your needs. Here's one of the best known ... http://www.databasecreations.com/ And Tony Toews has a list of such at http://www.granite.ab.ca/accsacct.htm -- Brendan Reynolds (MVP) http://brenreyn.blogspot.com The spammers and script-kiddies have succeeded in making it impossible for me to use a real e-mail address in public newsgroups. E-mail replies to this post will be deleted without being read. Any e-mail claiming to be from brenreyn at indigo dot ie that is not digitally signed by me with a GlobalSign digital certificate is a forgery and should be deleted without being read. Follow-up questions should in general be posted to the newsgroup, but if you have a good reason to send me e-mail, you'll find a useable e-mail address at the URL above. "Joe Cilinceon" wrote in message ... Yes, I know but the powers that be said no until the end of next year. Things like concrete work, new doors, new roofs, our salaries etc have all the money spent until the last quarter of next year. Now with that said the only time problem is filling it. This is a small 455 unit storage facility that is pretty stable for 15 years now. With my wife and I it only requires about 2 real days of work out of a normal week, my time isn't that filled. To give you a better idea of what we took over on Oct 1 of this year. No computer, paper ledgers, hand written letters and such. Since Nov. I've managed to get it converted over to a very simple Access database system that now handles most of the things that where done in the past by hand. I do own Office XP Developers. I'm also a retired systems analyst, though it was with a school system and had little to do with computers. I've not programmed since DOS and Windows 3.1 and then nothing major. I also have enjoyed doing what I've done so far and would love to take it all the way if for no other reason than just self satisfaction of learning new things even at my age. What I've done so far is get the tenant records finished and working very smooth. These include all information including contact info. The unit tables are done and tied in with the tenants files so we know who is in what unit as well as well as what each person pays for their units. The payment side of this is where I am at now even though the method used at the moment is getting the results. With what I have written so far, we generate about 20 reports required by the investors that where done by hand, as well as late notices, invoices, payment receipts, auction letters, deposit slips. I have even added a competition survey section as well as payroll, bank deposits, credit card tracking. I even have a reservation system, waiting list, and the ability to scan tenant id into the system. I send the reports now by email in PDF format to the investors. All of this has taken me since Nov 1 to accomplish. Now, I want to bring the payment side up to more than a simple spreadsheet that requires us to enter every detail of a payment. It works just like the paper system did but is very cumbersome though more accurate than the paper system. This ledger has a Transaction number (autonumber) custno, unitno, date paid, how much, payment method, tracking number (check number, credit card approval code), dates if from and thru. It gets very had to do when you get partial payments, split payments (cash and check etc), over payments that don't cover a full month, so we wind up with a credit. We also have to deal with bounced checks, units in lien, auctioned units (sold to general public at auction). etc. Well I didn't mean to go on this long but I've really been frustrated in finding examples for this aspect of database applications. It seems all examples are for the same things and nothing on accounting at all. If I need to track inventory, ship goods or had to list suppliers I would be covered. g -- Joe Cilinceon "Brendan Reynolds" brenreyn at indigo dot ie wrote in message ... Another way of looking at that, Joe, is that this project is occupying 50% of your workforce! :-) The value of your time, of course, is how much you would have made if you had spent it doing, um, whatever you would have been doing if you hadn't been doing this! :-) -- Brendan Reynolds (MVP) SNIPPED for size |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Free Access Training | Timboo | New Users | 8 | August 17th, 2005 05:58 PM |
Exclusive access to the database | Steve Huff | General Discussion | 17 | December 24th, 2004 07:23 PM |
Unable to open Database in Design view. | Weeksrw | General Discussion | 4 | September 25th, 2004 09:54 PM |
finding design examples for Invoices and Payments | Paul James | Database Design | 2 | June 9th, 2004 07:14 PM |
How do I design a database based on the information that will be stored? - Copy of Tables and hirearchies.zip (0/1) | Jim | Database Design | 1 | June 1st, 2004 01:44 PM |