Problem 2 - Real Estate Listing

Create a database consisting of one table of homes listed for sale with the fields shown below. Create the table with the fields in the order shown. When you save the table you can ignore the Primary Key field option. Save the table for a future Excel problem.

Be very careful about input mask, validation rules and the field formats (Date/Time, Number,, etc.)

1. Street address (Text or Number, etc?)
2. City (Text or Number, etc?)
3. Selling price (Text or Number, etc?)
4. Number of bathrooms (Validation Rule or Number Format)
5. Number of bedrooms (Validation Rule or Number Format)
6. Square feet (Text or Number, etc?)
7. Date listed (use the input mask)
8. Age of home (Text or Number, etc?)

Criteria For Data Entry

Data Entry Directions Part 1: Read the criteria forst, then enter ONLY 2 of the 20 records ; see the Data Entry Directions Part 2 below for entering the rest of the Data.

Your file will consist of at least 20 records (homes). Make sure the records you enter into the table have at least one home that meet each of the criteria identified.

1. At least five areas represented i.e. Santa Cruz, Watsonville, Aptos, Felton, Capitola, Boulder Creek, etc.
2. Some in Watsonville and Capitola
3. Houses over 1500 square feet and some under
4. Houses over 1500 square feet, less than 10 years old, with at least 3 bedrooms, two baths, and a selling price of less than 400,000
5. Houses with a selling price under 200,000 and over 300,000
6. Houses between with a selling price between 300,000 and 400,000
7. At least one house in Boulder Creek with a selling price under 200,000
8. Some with listing that are more than 60 days old and some that have been listed less than 60 days
9. Houses with 1, 2, 3, 4, and 5 bedrooms
10. Houses with 1, 2, 3, and 4 baths

Data Entry Directions Part 2: Using Forms to enter data into a table.

Forms

A form is a database object that you can use to:

• enter,
• edit,
• or display data from a table or a query.

You can use forms to control access to data, such as which fields or rows of data are displayed. For example, certain users might need to see only several fields in a table with many fields. Providing those users with a form that contains just those fields makes it easier for them to use the database. You can also add buttons and other functionality to a form to automate frequently performed actions.

Think of forms as windows through which people see and reach your database. An effective form speeds the use of your database, because people don't have to search for what they need.

A visually attractive form makes working with the database more pleasant and more efficient, and it can also help prevent incorrect data from being entered.

Microsoft Office Access 2007 gives you new tools to help you create forms quickly, and provides new form types and features that improve the usability of your database.

You can create a form using several tools under the "Create" tab:

• Form Button
• Split Form Button
• Blank Form Button
• Form Design Button
• More Forms > Form Wizard

Create a form by using the Form Wizard

1. Click on your table. (or you could make a form of a query based on one or more tables, but not for this exercise)
2. Click on the Create Tab
3. choose "More Forms > Form Wizard" and follow the directions
4. Modify your form layout and appearance (see in class demo or use the mouse to right click and experient with option or try the different views like the layout view, design view and form view choices)
5. Save the Form often.

Use the Form you made to enter the rest of your data records. Remember you are to have at least 20 records following the criteria in the Data Entry Directions, Part 1 above.

After the table is created and all the data entered, create a report that contains all of the fields in your table, fit to ONE page. This format will be used for all of your reports. A sample format of your report is shown below. Make sure you use a three line title that is centered across the columns and the fields are listed on the report in the order shown. Be sure the column headings are the same as the sample (two lines for most, aligned as shown). All of your reports will look similar to this format. If they look much different, they are wrong. However, you may choose any style presented by the Report Wizard. The sample is the Formal style. See additional directions from the Student Listing exercise. Use color/font styles, etc. to create unique reports that emphasize the information queried.

Reports Format

Queries and Printed Reports

1. The entire table sorted on selling price in ascending order.
2. All the listings except those in Santa Cruz.
3. Houses with a selling price between 300,000 and 400,000.
4. Houses with at least 3 bedrooms and at least 2 baths.
5. Houses with at least 3 bedrooms or at least 2 baths.
6. Houses with less than 3 bedrooms OR with a selling price less than 200,000 and in Boulder Creek. Hint: Enter the second part on the "and" line then use the "or" line in the query.
7. Houses over 1500 square feet, less than 10 years old, with at least 3 bedrooms, at least two baths, and a selling price of less than 400,000.
8. Those houses in Watsonville or Capitola.
9. Those houses that are not in Watsonville or Capitola.
10. Houses that have a listing date that is at least 60 days old based on the current date. Hint: < means older
    

Submit your file via the Blackboard assignment tool, Access Problem 2. (50 points)