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.

Create a form by using the Form Wizard

1. Under Objects, click on Forms
2. Click the New button
3. Choose (highlight) "Design View"
4. In the Tables/Queries pull down menu find your table by name and choose it, then click OK
5. A new Forms window opens up with it's own menus along with a smaller fields box window that shows the fields from the table
6. double click the fields box on the top to highlight all the fields, then drag and drop them onto the Form grid in the Forms window
7. Notice that you are in the Form view and can switch to the Design View or Data View
8. While in the Design view, you can grab each box and move it around using the grid to align with until you like the layout
9. Use the right button on the mouse to click on the page grid, the text box field names and their data entry text boxes to customize the appearance, color and order of data (in class Demo)
10. 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)