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
|
|||
|
|||
Summarize transations amts on the "Master" record.
Hi,
I’m using Access via XP Access Pro running in XP Pro with SP3. Please note that this question was posted in both the Database design and Form coding forums (if I did it right). I am donating my services to a homeless charity. They have about 80 homes and apartment builds that they rent, at a greatly reduced rate, to people who are rebuilding their lives. Most people stay in the homes for a year or less. Currently, they are tracking all of the information on an Excel spreadsheet. I’ve been asked to put this into an Access database. Expected transaction count per renter is about 36 transactions per year with approximately 160 renters per year or approximately 5800 transactions per year. My tables a Building – Keyed by building name. This will contain the name of all of the physical buildings. There will be a code to tell me if this is a home or apartment. Unit – Keyed by building name and apartment number. This will contain all of units that are in the building. Renters – Keyed by auto number and indexed by name. This will contain the renter’s information. I don’t know if I will have a Lease table or if I will combine it with the renter’s information. Transaction - Keyed by auto number and indexed by renter’s number from the Renter’s table. The transactions will be Deposit, Rent, Late Fee, NSF Charge, Misc Fee, and Adjustment. There will be a “Due” and “Paid” version of each of the preceding transactions. Our main objective with this project is to be able to have a report and inquiry screen that shows how much each person owes as of the current minute. There are Here are my questions: I’m still new to Access and I’ve never written anything with a “Transaction” file where I have to summary the results in a Master file. I have downloaded the Inventory Managements template from Microsoft. Does anyone know if there is a better example for what I want to do? Are there any suggestions or traps that I might want to avoid or hints to assist me in efforts? Thank you for your assistance. -- Dennis |
#2
|
|||
|
|||
Summarize transations amts on the "Master" record.
I don’t know if I will have a Lease table or if I will combine it with the
renter’s information. Use one table with field to identify Renter/Leaser. Need a field for Inactive – Yes/No. Need to add fields to set payment schedule – Period (d- Daily, w- Weekly, b- Bi-weekly, m- Monthly) Unit needs a Autonumber field as primary key. All transactions to use a combo to select type from list/table. Charge use a Currency field. Need Note/Comment to define Misc Fee and Adjustment. Use an Append query to add new Rent transaction record for all active renters when the date is past the Maximum Due date. Query would use Period to determine next Due date – DateAdd([Renter].[Period], 1, Max([Due])) to add. Use last Charge for new record. Others might suggest how to record partial payments. -- Build a little, test a little. "Dennis" wrote: Hi, I’m using Access via XP Access Pro running in XP Pro with SP3. Please note that this question was posted in both the Database design and Form coding forums (if I did it right). I am donating my services to a homeless charity. They have about 80 homes and apartment builds that they rent, at a greatly reduced rate, to people who are rebuilding their lives. Most people stay in the homes for a year or less. Currently, they are tracking all of the information on an Excel spreadsheet. I’ve been asked to put this into an Access database. Expected transaction count per renter is about 36 transactions per year with approximately 160 renters per year or approximately 5800 transactions per year. My tables a Building – Keyed by building name. This will contain the name of all of the physical buildings. There will be a code to tell me if this is a home or apartment. Unit – Keyed by building name and apartment number. This will contain all of units that are in the building. Renters – Keyed by auto number and indexed by name. This will contain the renter’s information. I don’t know if I will have a Lease table or if I will combine it with the renter’s information. Transaction - Keyed by auto number and indexed by renter’s number from the Renter’s table. The transactions will be Deposit, Rent, Late Fee, NSF Charge, Misc Fee, and Adjustment. There will be a “Due” and “Paid” version of each of the preceding transactions. Our main objective with this project is to be able to have a report and inquiry screen that shows how much each person owes as of the current minute. There are Here are my questions: I’m still new to Access and I’ve never written anything with a “Transaction” file where I have to summary the results in a Master file. I have downloaded the Inventory Managements template from Microsoft. Does anyone know if there is a better example for what I want to do? Are there any suggestions or traps that I might want to avoid or hints to assist me in efforts? Thank you for your assistance. -- Dennis |
#3
|
|||
|
|||
Summarize transations amts on the "Master" record.
Karl,
Sorry, I was not clear. The Renter table would contain the names of the people who rent or lease the apartment. Based on your comment, I should change the name of the table to Contract table. The Contract table would contain the details of the lease or rental contract. It is unlikely, but possible that a renter could move from one facility to another within their year in the program. The Lease table would allow me to track each rental agreement they had. -- Dennis "KARL DEWEY" wrote: I don’t know if I will have a Lease table or if I will combine it with the renter’s information. Use one table with field to identify Renter/Leaser. Need a field for Inactive – Yes/No. Need to add fields to set payment schedule – Period (d- Daily, w- Weekly, b- Bi-weekly, m- Monthly) Unit needs a Autonumber field as primary key. All transactions to use a combo to select type from list/table. Charge use a Currency field. Need Note/Comment to define Misc Fee and Adjustment. Use an Append query to add new Rent transaction record for all active renters when the date is past the Maximum Due date. Query would use Period to determine next Due date – DateAdd([Renter].[Period], 1, Max([Due])) to add. Use last Charge for new record. Others might suggest how to record partial payments. |
#4
|
|||
|
|||
Summarize transations amts on the "Master" record.
Karl,
I like your idea about the Append query. I have not though of that. I was going to write a batch program, but your idea is much simpler. Dennis -- Dennis |
Thread Tools | |
Display Modes | |
|
|