C
columbo1977
Guest
Hi
Was wondering if anyone could help with the attached database
The question I am on at the moment is Number 3? The arrears seem to work but when i put in a date in paid on the arrears will not go back to £0.00 it stays on £250???
Please see below.
thanks for the help
headintheclouds
-----------------------------------------------------------------------------------------------------------------------------------------
Opal Property Management – Property Letting Database Specifications
Opal Property Management currently uses paper-based forms to manage the property lettings section of the business. Opal would like to replace this paper-based system with a database system.
Rents are collected on a weekly basis (due on Fridays) and Opal receives a flat 10% commission of the weekly rent.
You are required to develop a database to maintain the information specified below and keep track of property/tenants details and rents.
TASKS
1. Create a new database (using Microsoft Access) and set-up appropriate tables based on the information provided. Optimise the tables as required and use appropriate field names, type and sizes.
2. Define appropriate relationships (Hint: the rents table links to the tenants).
3. Design/Create a form (and subform) to show tenant details with payments due/received and a total of any arrears.
4. Test your database by entering appropriate test data (ensure your test data will allow you to test each task below).
You should enter at least 5 properties, 10 tenants and any appropriate other data as required.
5. Create an Append Query to add the weekly rent records to the rents table (only add records for properties that are currently occupied). When run the query should prompt the user to enter a week ending date and pick up the amounts from the property list.
Run the query at least three times to create additional test data and then manually enter payment dates for some of the records to show that the rent has been paid.
6. Create a Payment History Report (showing a list of due payments and any arrears due) for each tenant.
7. Add button to the button to the tenant details form (see 3 above) to preview the Payment History Report for the record that is being displayed on the form.
8. Produce a report to print mailing labels for all tenants – use an appropriate two column label. The tenant’s name should be shown as <Title> <Initial> <Surname> (eg: Mr J Smith).
9. Produce a rent overdue letter (report) for the tenant record being edited/viewed on the tenant form (see 3) and add a button to the form to preview the report. See wording below (replace items in <> with appropriate field names/formulas) – the address should not have any blank lines (save as: rptLetter).
10. Create a macro to change the “Credit Reference” to uppercase (using the UCASE function) after it has been entered/updated on the form (see 3 above). Make the appropriate changes to the form to ensure the macro run as required.
11. Produce a Contact Renewal Report to show a list of tenants who’s contracts are due for renewal between two dates specified by the user. Before the list is produced the user should be prompted for the start and end dates.
12. Create a query (save as qryMakeCurrentTenants) to make a new table (tblCurrentTenants) to hold the property id, address, owner’s name, current tenants name and date he/she moved in (start date).
13. Create a menu including links to the form and appropriate reports/queries and make it start-up automatically when the database is opened.
Was wondering if anyone could help with the attached database
The question I am on at the moment is Number 3? The arrears seem to work but when i put in a date in paid on the arrears will not go back to £0.00 it stays on £250???
Please see below.
thanks for the help
headintheclouds
-----------------------------------------------------------------------------------------------------------------------------------------
Opal Property Management – Property Letting Database Specifications
Opal Property Management currently uses paper-based forms to manage the property lettings section of the business. Opal would like to replace this paper-based system with a database system.
Rents are collected on a weekly basis (due on Fridays) and Opal receives a flat 10% commission of the weekly rent.
You are required to develop a database to maintain the information specified below and keep track of property/tenants details and rents.
TASKS
1. Create a new database (using Microsoft Access) and set-up appropriate tables based on the information provided. Optimise the tables as required and use appropriate field names, type and sizes.
2. Define appropriate relationships (Hint: the rents table links to the tenants).
3. Design/Create a form (and subform) to show tenant details with payments due/received and a total of any arrears.
4. Test your database by entering appropriate test data (ensure your test data will allow you to test each task below).
You should enter at least 5 properties, 10 tenants and any appropriate other data as required.
5. Create an Append Query to add the weekly rent records to the rents table (only add records for properties that are currently occupied). When run the query should prompt the user to enter a week ending date and pick up the amounts from the property list.
Run the query at least three times to create additional test data and then manually enter payment dates for some of the records to show that the rent has been paid.
6. Create a Payment History Report (showing a list of due payments and any arrears due) for each tenant.
7. Add button to the button to the tenant details form (see 3 above) to preview the Payment History Report for the record that is being displayed on the form.
8. Produce a report to print mailing labels for all tenants – use an appropriate two column label. The tenant’s name should be shown as <Title> <Initial> <Surname> (eg: Mr J Smith).
9. Produce a rent overdue letter (report) for the tenant record being edited/viewed on the tenant form (see 3) and add a button to the form to preview the report. See wording below (replace items in <> with appropriate field names/formulas) – the address should not have any blank lines (save as: rptLetter).
10. Create a macro to change the “Credit Reference” to uppercase (using the UCASE function) after it has been entered/updated on the form (see 3 above). Make the appropriate changes to the form to ensure the macro run as required.
11. Produce a Contact Renewal Report to show a list of tenants who’s contracts are due for renewal between two dates specified by the user. Before the list is produced the user should be prompted for the start and end dates.
12. Create a query (save as qryMakeCurrentTenants) to make a new table (tblCurrentTenants) to hold the property id, address, owner’s name, current tenants name and date he/she moved in (start date).
13. Create a menu including links to the form and appropriate reports/queries and make it start-up automatically when the database is opened.
