Database Programming and Management with Access - CIS152/61

Weekly Schedule

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.
Final Exam Create a navigation systems to work with one of your existing databases or a new database you develop. Follow the guidelines in chapter #14.
Please pass the final in by Wednesday, May 14th. I also want you to pass in a list of the assignments you have done to verify the work I received.
Week #14
Week of April 28th
Read chapters #14 and #15 on navigation and macros.
Write three very basic macros similiar to the examples in chapter #15.
Week #13
Week of April 21st
Read chapter #12 and #13 on forms.
Design an input form for one of your databases to take in data using the Form Wizard.
Check back!
Week #12
Week of April 14th
Catch up week!
Week #11
Week of April 7th
Read Chapter #11 on Designing Advanced Reports.
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.
Week #10
Week of March 31st
Read Chapter #10 on creating reports.
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.
Week #9
Week of March 24th
Read chapters #8 and #9. The assignment this week will involve demonstrating some of the things covered in chapters #7, #8 and #9.
This assignment can be done using one of your existing databases or you can create a new database to test. Your examples should not be exact duplicates of the examples in the text - I am looking for experiments!
1. Demonstrate the use of a calculated field in a query.
2. Do another calculated field where you add two things together and then multiply by a third thing.
3. Do another calculated field where you do a subtract and then divide the result of the subract by another number.
4. Take two text fields and join them together and display them. Between the two fields have a comma and a space.
5. Demonstrate the use of the round function.
6. Demonstrate the use of the expression builder from chapter #6.
7. Format a number in currency and format a number in percent.
8. Use the Fix() or Int() function, the Val() function, the Format() funcation, the UCase() function, the Mid() function, the Len() function and the InStr() function.
9. Use the Date() function, the Now() function, the DatePart() function and one other date function.
10. Do a query that does an average, a max and a sum.
11. Now do a query that does an average and a sum for a group - the group could be dept, state or somee other field that is repeated.
12. Demonstrate joins in a total query (see chapter 8).
13. Demonstrate crosstab queries (again see chapter 8).
14. Do a second example of a crosstab query.
We are now looking at action queries from chapter 9. I would select you work on a copy of your database.
15. Demonstrate an update query.
16. Demonstrate an append query.
17. Demonstrate a make-table query.
18. Demonstrate a delete query.
Spring Break
Week of March 17th
Catch up time!
Week #8
Week of March 10th
Read chapter #7.
Database Assignment (political campaign) If you want me to look at the design of the tables before you implement, send it to me and I will take a look.
SQL queries using the Political Contribution Database:
1. I want to see all contributions that are over $1000.
2. I want to see all contributions that are for over $500 and gave to a specific activity.
3. I want to see all contributions that are either gave over $500 or gave to a specific activity.
4. I want to see all contributions that are for over $200 AND either gave to one activity you specify OR another activity you specify.
5. I want to see all contributions that are over $200. I want you to link to the contributors database and give the persons name as well.
Week #7
Week of March 3rd
Read chapter 6.
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.
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.
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.
Week #6
Week of February 24th
Read chapter #5 completely and start reading chapter #6 which is more about queries. I have 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. Assignments: Using Access, I want you to create the following two databases:
Student database example
Payroll database example
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 anothe table you have to have matching department numbers. Next week we will query these databases.
Week #5
Week of February 17th
For help sessions, check under CIS120.
Read chapter #4 and start reading chapter #5.
Please check back.
Do something to show me you can work with:
1) Setting Default Values
2) Indexes
3) Input Masks
4) Validation Rules
5) Lookups
Check back!
Week #4
Week of February 10th
Read chapter #3 in the text book.
Do some research on line if you want other references.
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
Week #3
Week of February 3rd
Read chapter #2 in the text book.
I want you to create a table within a database that you design so you can test the things in chapter #2. I want you to pick 20 things that are discussed and try them on your table. You need to include a list of the things you tried so that I can check them out.
Check back!
Week #2
Week of January 27th
I have sent you the information about the book that we are going to use. Hopefully you can get in this week or next. I would like you to read the Introduction and Chapter #1.
Note, I put out some information that Janelle is using for a class that I thought would be useful.
You will hopefully get an invitation to DreamSpark this week which will allow you to download and install Access. Notes to help you do this are under Notes and Handouts.
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
Query Assignment
Week #1
Week of January 20th
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.
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 three 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.

Send e-mail to Mrs. Grocer:

Back to the Top
Return to home page