DATABASE TABLES: CARS



Apartments Database

• You will create a new database file; save the file as Apartments by your name

• Create a table in design view with the following information: Save the table as Apartment Residents by your name. Take OFF the primary key: Select the Row in the Design View with the Primary Key. With the Row Selected, Click on the Primary Key button in the ribbon. When the Primary Key disappears it has been turned off.

|Field Name |Data Type |

|Apartment |AutoNumber |

|Last Name |Short Text |

|First Name |Short Text |

|Deposit |Currency |

|Rent |Currency |

• Switch to datasheet view and enter these records:

|Apartment |Last Name |

|Apartment |Number |

|Utilities Paid |Yes/No |

|First Floor |Yes/No |

• Switch to datasheet view and enter these records:

|Apartment |Utilities Paid |First Floor |

|1 |No |Yes |

|2 |No |Yes |

|3 |No |Yes |

|4 |No |Yes |

|5 |No |Yes |

|6 |Yes |No |

|7 |No |Yes |

|8 |Yes |No |

|9 |Yes |No |

|10 |Yes |No |

|11 |No |Yes |

|12 |Yes |No |

|13 |No |Yes |

|14 |No |Yes |

|15 |Yes |No |

• Create a relationship between the Apartment Residents table and the Apartment Info. Table – use the Apartment field. (Hint: Database Tools – Relationships button – show all tables – connect Apartment fields and save)

Queries:

• Perform the following queries – copy and paste each one into a word file – in the order listed below. (Hint: Change the page layout to columns if needed)

• Save the Word file as Apartments by your name. Put your name in the header of the word file, save and print.

1. Create a select query using the following fields: Last Name, Deposit, and Utilities Paid. Show only those residents whose last names begin with the letter D. Sort by Deposit. Save the query as: Letter D (Hint: Use Text Filters or the Criteria: D*)

2. Create a new select query using the following fields: Apartment, Deposit, Rent, Utilities Paid, and First Floor. Show those residents whose rent is $300 or below and live on first floor. Sort by Apartment number. Save the query as: Below $300 on First Floor

3. Create a new select query using the following fields: Last Name, First Name, Deposit, First Floor. Show those residents who put down a deposit of over $300. Sort by Last Name. Save the query as Deposit

4. Create a new select query using the following fields: Last Name, Rent, and Utilities Paid. Show those residents who do not pay utilities and pay $250 or less for rent. Sort by Last Name. Save the query as No Utilities

5. Create a new select query using the following fields: All fields from Apartment Residents table and only the First Floor field from Apartment Info table. Show those residents who do not live on first floor whose last names begin with B. Sort by Last Name. Save the query as Second Floor

Print the Word file –Save to Your Edisk.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download