Exam 2 Review

1. Exam 2 Review Harish Palakurthi

2. Exam 2 • Must Have WVU ID to get in • Show up 15 minutes early on your exam day • Similar in format to previous exam • Covers Access

3. Exam 2 Review • The items covered here may or may not be on the exam. • Other items not covered may be on the exam. • Study EVERYTHING from Text, Supplemental Content, MyItLab, Homeworks, and Lectures. • Review all lecture notes and exercises

4. Access Review – Import the files To import a file click on External Data and then select the file type required. Remember when importing a file you can: A) Import just the structure (Empty table) B) Import structure and data (Table & records) C) Append to an existing table (Append records) 4

5. Tables Design View • Fields • Primary Key • COMPOSITE KEYS • Data Types • AutoNumber, Number, Currency • Text, Memo • Yes/No • Lookup Wizard Datasheet View • Add Records • View Records • Edit Records • Delete Records

6. Tables Always create tables in Design view Add the Field Names ( Column Labels ) in Design View Flip from Design View to Datasheet View to enter the Records Field Names Records 6

7. Creating Tables – Contd. Select proper field names and data types Data Types: Text – when you are entering text, can be numeric or alphanumeric Memo – when you need more than 255 characters Number – when you are entering number: Single & Double – Use these field sizes to store DECIMAL numbers Integer & Long Integer – Use these field sizes to store WHOLE numbers Currency – when storing currencies Date/Time – when storing dates and times Auto Number – auto incrementing number – note it is sequential so if you delete a record the associated Auto Number is gone. So don’t panic if your auto numbers are missing some values

8. Creating Tables – Contd. Use Memo for Analysis question responses Select proper primary keys – a primary key is something that UNIQUELY identifies records The test, much like HW-5, will have some tables that will be imported and some that will be created

9. Making Relationships Make sure neither table is open when you are creating the relationships. If they are you will get an error. Always relate fields with the same Data Type and same Content In general related fields will have similar names – Country and Country Abbreviation, etc If the relationships aren’t set up properly it is very likely the queries won’t work 9

10. Forms Forms with Sub Forms • Always use the Form Wizard under Create - More Forms • Add in the entry for the Main Form, then add in everything else for the Sub Form • Hit Next • You will see Form with subform at the bottom, if you don’t then either your relationships aren’t correct or you didn’t add the entries in properly 10

11. Reports Reports Again, use the Report Wizard Add in all fields as required Add in any viewing, grouping or sort levels

12. Queries • Always create queries in Design view • Add in ONLY the required tables • Add in ONLY the required fields • When setting criteria make sure the criteria makes sense, for instance setting Math as the criteria under StudentID is meaningless • For SQL queries simply change the view to SQL View and copy and paste the code in 12

13. Queries – Contd. • To format decimal places, always run the query first before going to the properties • If a query asks for Sum, Group By, Average, etc remember that it is a totals query and you have to hit the Totals button to get the Totals Row • Remember calculated fields must have any references to fields in your table named exactly as they appear in your tables. • COMPACT AND REPAIR DATABASE