1 / 15

Homework 5 Overview

Homework 5 Overview. HW5 – Step 1-3 . Download the Data Files Extract the downloaded Data Files Use the External Data tab in Access and import the XML files using the XML file option. Import using the Structure and Data option. HW 5 - Step 4.

jackson-daniel
Download Presentation

Homework 5 Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Homework 5 Overview

  2. HW5 – Step 1-3 • Download the Data Files • Extract the downloaded Data Files • Use the External Data tab in Access and import the XML files using the XML file option. • Import using the Structure and Data option

  3. HW 5 - Step 4 • Note: Relationships for this assignment are NOT created in the relationships window. They need to be created in the queries ! • Only 3 fields are needed • Select one where all field values will be unique to use as a primary key • Use a lookup field to give a drop-down list • Remember how things were typed for query use later!

  4. HW5 – Step 6 • Creating the Queries (longest part) • You will establish relationships in the queries as opposed to using the Relationships window ! • You will make copies of queries to reuse the relationships • Remember, unless otherwise specified, clear old sorts and criteria out when the queries have been copied to reuse the structure !

  5. HW5 – Step 6 Look at the relationships shown in step 6a... • An additional table will need to be created as a copy of AIRPORTS to have one for the origin and one for the destination. (Resulting tables: Airports & Airports_1) • An additional copy of the AIRFARE table is need as well and you will end up with this after relating the two copies. (Resulting tables: Airfare & Airfare_1) • Simply double click the tables a second time in the “Show Table” window to add the additional ones.

  6. Step 6 – Create and Relate the Two Additional Table Copies In Query 6a Move your tables around to look EXACTLY like this ! Make sure the “_1” versions are on top !

  7. HW5 – Step 6a • Note: At the bottom of the diagram for 6a there are hints on how to setup the query too… • Remember how to sort and that multiple fields can be sorted on from left to right in hierarchy. • Remember you can alias a field name by typing a name and a :to the left of the field info • Make sure you have 6a right before you move on as 6b is based on it !

  8. HW5 – 6b • Copy query 6a (right click… copy/paste… name) • Add criteria where needed • Remember “And” conditions from prior lecture • Uncheck where needed so the fields do not show in results …

  9. HW5 – 6c • Requires two “Airports” tables and an “Airfare” table. Build like 6a, but Airlines tables are not needed. • From a previous lecture, remember about: • “OR” conditions under 1 field, like: >value Or<value • Calculated fields using Expression Builder • If you see “<<Expr>>” in expression builder, delete it or things will not run ! ! ! • Run the query once before setting the 3 decimal places on the calculated field properties. • Beyond the formula, think about how to figure for the Distances mentioned. Could be same field with and OR condition.

  10. HW5 – 6d • Copy what it tells you and do not forget to remove old field & Criteria. • Remember the table field types shown in step 1 • We are looking at more than one Hub field here, so you need to add some fields and criteria

  11. HW5 – 6e • Make the 3 tables and relationship look exactly like what is shown (Note: one is Airfare_1) • Use what is shown in the grid as a hint • Type it all in meticulously! • If you get an error, check what you typed • Make sure to do each aspect described • Notice that the Total row is used and fields are set to “Expression”

  12. HW5 – 6f • Make a new query and flip to SQL view • Copy and paste the SQL code from the PDF file version of the directions into SQL view • Do not try to type it in, remember to change the pointer using the Select tool in Acrobat • This is way easier than creating SQL code from scratch ! 

  13. HW5 – 6g • Set “Show Table” to “Queries” to pick Query6F • Only need two table objects from Show Table: “Airports” & “Query6f” • IIF () is in expression builder under Functions > Built-in Functions > Program Flow • Look for this in Expression builder and replace the parts it gives with what you need • Think about when you had “yes” and “no” back in Excel with the “IF” function.

  14. Homework 5 Continued… • Step 7 - Critical Thinking Questions Table • Create a table to hold the answers to the questions. • Remember that this step alone is 20%of the grade on this project.

  15. Step 8 - Additional Access HW Information For the remaining Access work in the course, please do the following prior to uploading your files: While in Access: Office Button > Manage > Compact and Repair Database * If you do not, your file may be too big to upload!

More Related