| Schedule by week | Information to cover |
|---|---|
|
Please check the weekly schedule multiple times per week, I sometimes add
information during the week. Please keep copies of all work you submit until you receive your final grade
at the end of the semester. | |
| Unit #14 | Read chapter #15 and check back! |
| Unit #13 | Read chapter #14 on navigation systems. Go through the examples and plan a naviagation system for a database you develop. Assignments: This assignment is also the final with a few macros that I will ask you to add. I want you to develop a new database with multiple tables, forms, reports etc and a good navigation system following the model in chapter #14. I would like it to be very complete and include a lot of what you have learned over the first 13 chapters. |
| Unit #12 | Read chapter #13 on advanced forms. Assignments: Design an input form. Set up fields and bind/link to the data and setup a few fields for calculation results. Validate the user entries. Associate this with one of your databases. |
| Unit #11 | Read chapter #12 on forms. Assignments: Design two input forms for one of your databases to take in data using the Form Wizard. I am thinking take in data for two different tables. Check back! |
| Unit #10 | Read Chapter #11 on Designing Advanced Reports. Assignments: Looking at the design that lets you go in and work with the actual design layout. I want you to make two reports using the features they talk about in this chapter to layout a report. Make a report with multiple columns. Focus on the first part of the chapter. Grouping and totals would be a nice addition. |
| Unit #9 | Read Chapter #10 on creating reports. Assignments: I want you to experiment and make 4 different reports for me - explain what you did. I would also like you to export a report as a word document and as a pdf and send those as well. |
| Unit #8 | Finish reading about queries and start reading chapter #9 and #10 about reports. Assignments: Redo the problems in unit#7 using SQL. In addition, I want you to do an update query, an append query and a delete query as well as the make table query. |
| Unit #7 | Finish reading chapter #7 and start reading chapter #8 and chapter #9. Essentially I want to keep working on queries. Assignments: If you want to add a table to try these on, it is fine. Problem #1: Do a simple calculation in a query using a field and a literal to do the calculation. Problem #2: Do a more complicated calculation in a query using multiple operations and nesting. Problem #3: Using a name that is in the format first last, convert it to last followed by a comma and then first. Don't forget that & allows you to concatenate. Problem #4: Experiment with multiple ways to show the date using different queries. Problem #5: Do a calculation where you check for null values. Problem #6: Make 5 different queries using the expression builder. Problem #7: Do one or more queries that experiment with things like sum, count, average etc. Problem #8: Do a query that experiments with Group By. Problem #9: Do a query that involves totals and multiple tables. Problem #10: Create two different crosstab queries using the wizard. Problem #11: Create two different crosstab queries from scratch. |
| Unit #6 | Read chapter #6 completely and start reading chapter #7 on queries. Be sure to go back and look at the samples for 2010 and 2013 under Notes and examples. I would also look at the Presentation for if queries in Access - it uses an older version but the logic is the same. Assignments: Using the two databases that you created last week, do the following queries. Half of them should be done using the user interface and half should be done using SQL. 1) Create a list of all students with a major of (you choose the major) who have a gpa > 3.0. Show the student id, the major code, the major name and the gpa. Sort by gpa. 2) Create a list of all students a major in (you choose the major) or a major in (you choose the major) who live in a particular state and have a gpa less than or equal to 3.0. 3) Create a list of all employees including employee name and id that have worked on a specifc project (you choose the project). 4) Create a list of all employees including employee name and id that have worked on a specifc project (your choose the project) for more than a 5 hour block (modify the number if you need to). 5) Create a list of all employees including there employee id, name, department number and name who have worked on a particular project (you include the project) and list the project number, the project name and the date they worked on the project (which means if they worked on a project more than once they will appear more than once on the list). If you do not get an employee working more than once on a project please add a record to the project list so you get this. 6) Create a list of all projects that were worked on during a particular year. Include project number and project name as well as the dates. 7) Create a list of all employees that work in department (you pick the department) and have either medical withheld or other deductions withheld. 8) Recreate the list in #6 but also include the department name in the list. 9) You modified the student database last week. Now create a query using three tables in the student database and put a condition A and either condition B or condition C criteria on it. 10) Use AND and then use OR within a cell in the user interface. In other words below major you might ask if the major is BU or CI. |
| Unit #5 | Read chapter #5 completely and start reading chapter #6 which is more about queries.
I hsve laid out two relational databases. I want you to read and ask questions if you do not follow them.
Then I want you to implement them (details below). Assignments: Using Access, I want you to create the following two databases: Student database example Payroll database example After you have laid out the basic student database, I want you to think about how to modify it to carry the courses that students take. This means you need to set up another table with courses (the primary key would be courseid). Carry the name of the course and the credits. Then set up another table called studentcourses where you will keep track of the courses the student has and is taking. This table will have a primary key made up of studentidnumber, courseid and the semester and year taken (for example S14 could be spring 2014). The field on this table would be the grade. This allows a student to take the course multiple times. Note that the grade relates to the whole key which is required for normalization. Please be sure to look at the example I did in class on this topic. It is under Smartboard and Audio for CIS120 on April 17, 2014. For both of these, I want you to build the tables in Access and populate them with data. Be careful. If you use the department number in one table and want to link to the department number in another table you have to have matching department numbers. Next week we will query these databases. |
| Unit #4 | Read chapter #4 and chapter #5. I want you to spend some time researching realtional databases. I want you to read some introductory material from the web: This is a nice introductory series I want you to read the first two parts (you can go on if you find it interesting). \ Normalization Continue reading about putting your database in first normal form, second normal form and third normal form using the links at the bottom of the article. We are going back to what the CIS120 course I teach for a review. Here is a quick introduction to databases with multiple tables and using it to design and develop an Access database. I am doing this to make sure you remember the concepts. This course is mainly about design, but I think a quick review of constructing a database set of tables will help you understand the design concepts we will be looking at. Normalization (3rd normal form) Student 4 table database Assignments: Do something to show me you can work with: 1) Setting Default Values 2) Indexes 3) Input Masks 4) Validation Rules 5) Lookups I would like two examples for the first two and with the last three, I would like to see three examples. Tell me an article you found that explained normalization well. |
| Unit #3 | Read chapter #3 in the text book. Do some research on line if you want other references. Assignments: I want you to use the table you created last week and test some of the new things in these chapters. To do that you need to try it in the table and writeup quickly what you did to direct me as to what to do when I test it. Need to send me the notes and the database. These are the things to try: 1) Resizing rows and columns 2) Hiding columns 3) Freezing columns 4) Sorting on one field 5) Sorting on multiple fields 6) Two types of filtering - your choice 7) Searching 8) Spell checking 9) Getting Quick Totals for a Column 10) Printing |
| Unit #2 | Read chapters 1 and 2. Chapter 2 goes into a lot of informtion that is helpful in setting up a table with a variety of fields. Assignments: I want you to create a table with a variety of fields and experiment with some of the concepts covered. Explain what you did and why you made the decisions you made in setting up the table. |
| Unit #1 |
There are 15 weeks during the fall/spring semesters and I will post the same number of units during the summer. That means that you will have
to pace yourself to do more than one unit some weeks to get through the material. I would like to give you flexibility, so I will
post a few units in the next week so you can work at a pace that matches your schedule. Please note in the text of the information
I will end up using unit and week interchangeably. You will receive a Dreamspark invitation that will let you download Access2013 (you can choose to use Access2010 if you have it and would prefer). I would like you to look at my notes on creating an Access database with a single table. I would also suggest that you can check out some of the many resources available. There are several videos on YouTube and lots of tutorials. Most of the training schools let you watch introductory material for free. Be sure to take a look at this database and also the Smartboard that explains it. It will help with the homework. Marketing Database in 2013 Notes to accompany Marketing Database in 2013 Assignments: Part 1 of the assignment: Develop a table that would be useful to you in your professional or personal life. I want you to define a table within a database that you start and enter data into the table (should be a mixture of text and numeric data). Explain why you made each field the type and size you decided on. Populate the table. The next set of notes and assignments will have you query the table you created. If you have questions, please let me know! In developing your database please note the following guidelines: (1) field names should not have embedded spaces and special characters other than Underscore (most database systems are very strict about this and I want you to be aware of this), (2) use appropriate types of data (text vs numbers) and be sure to pick appropriate sizes, (3) designate a primary key that uniquely identifies each record. Part 2 of the assignment: Once you have developed the table I want you to do these six queries: (1) Display 4 fields and have them meet a certain criteria. For example, if state is one of your fields you could display only those that have "MA" in their state. (2) Display records that meet two criteria in an AND relationship. (3) Display records that meet two criteria in an OR relationship. (4) Display records that have one criteria that has to be true and either of two other criteria must also be true. (5) Display records that have two criteria that have to be true OR to different criteria that have to be true. (6) Display records where you use the or in one cell so you might ask "MA" or "RI". This could be done using the criteria line and the or line, but I want you to do it with just the criteria line. |