Access 2007 – Imports, Reports and Forms

1. Access 2007 – Imports, Reports and Forms Thomas I. Chittenden MBA, MCITP, VCP, A+, Network+ Lecturer, School of Business Administration Thomas.Chittenden@uvm.edu

2. Agenda • Quick Round Table • What do you want to get from today? • Access 2007 data import from Lotus Approach • Access 2007 Create Tab and New Interface • Query By Example • Working with Reports • Sorting & Grouping • Layout, Properties and Toolboxes • Report Expressions • SubReports • Working with Forms

3. Exporting from Lotus Approach • If you are making a switch to Microsoft Office or need to collaborate with someone who is using MS Access, you can convert your Approach database by first exporting to .DBF (the native extension for a dBASE file), and then importing that .DBF file into Access. Converting from Access to Approach is even simpler: Just open an .MDB file (Access' native file format, which Approach can read directly) in Approach and perform a File, Save operation. Approach will automatically convert the file. http://www.smartcomputing.com/editorial/article.asp?article=articles/archive/r0504/24r04/24r04.asp&guid

4. Importing into Access • Screen Cast • Lotus Approach DBF Import into MS Access 2007 • Example of where we will go from here.

5. The Create Ribbon New Perspectives on Microsoft Office 2007: Windows XP Edition

6. Query In Class Example • Rossi Recycling Group is a not-for-profit agency that provides recycled household goods to needy people and families at no charge. Donors give cash, goods, appliances, furniture, tools to Rossi. Due to change in US Tax Laws for charitable donations, Rossi is now required to track all donations from donors and report the cash re-sale value of the donation to the US government. To maintain accurate records so as to continue to offset the costs of this services in conformance with government regulation, Rossi has developed a simple 3 table database.

7. Rossi DB Relationships

8. Question 1: • The government requires a list showing the total Cash value of all donations from EACH donor. • Donor First Name, Last Name and Total Cash Value of Donations

9. Question 2: • Your database also tracks which agencies received the donations. The Government needs a report of the total Cash value of all donations to each agency. • Show the Agency Name and Sum of All their received donations

10. Question 3: • Agency ID’s that start with R or W are special state agencies. The state government needs a complete list of all (not the sum, but all) donations to all agencies with an ID that starts with R or W. • Agency ID, Agency Name, Donation Date, Donation Desc and Donation Value • Query Criteria vs. Filtered view.

11. Question 4: • The local City Board is interested in knowing how many donations from Rossi go to agencies out side of the home city of Salina. Generate a list of all donations going to agencies not in Salina. • Show Donation amount, Date of donation Agency ID, Agency Name and Agency City.

12. Question 5: • People thinking of donating items are often very curious the reported cash value of similar items. Create a query that will display all donation info for donations greater than a value entered by the user at the time of running the query (HINT: Parameter Query). • Show Donation Desc, Donation Value

13. Question 6: • Create a query that will show all donations from Brian Park (Donor ID: 36033) that ended up with ‘After School Youth’ (Agency ID: Y68). • Show Donor Fname, Lname, Phone number (Don’t Show DonorID) • Donation Description, Date & Cash Value • Agency Name and Agency Phone Number

14. Question 7: • Create a query that will show all donations from Brian Park (Donor ID: 36033) as well as all donations that were given to the ‘After School Youth’ (Agency ID: Y68). • Show Donor Fname, Lname, Phone number (Don’t Show DonorID) • Donation Description, Date & Cash Value • Agency Name and Agency Phone Number

15. MS Access Sorting & Grouping • Grouping allows you to separate groups of records visually and to display introductory and summary data for each group. • A group is a collection of records, along with any introductory and summary information displayed with the records, such as a header. A group consists of a group header, nested groups (if any), detail records, and a group footer.

17. Access Report Expressions • Combine or Manipulate Text • =[FirstName] & " " & [LastName] • =Left([ProductName], 1) • Common Headers & Footers • ="Page " & [Page] • ="Printed on: " & Date() • Arithmetic Operations • =[Price]*1.06 • =[RequiredDate]-[ShippedDate] • Functions: Sum, Average or Count… • =Avg([Freight]), =Sum([Sales]), =Count([OrderID]) • http://office.microsoft.com/en-us/access/HA011814491033.aspx#2

18. Sub Reports Sub reports are often used in situations where you have a need to print out repeating values for a header or footer field. • Examples Cont.: • Student • Course Grades* • Advisors** • Social Clubs** • Region • Sales Reps** • Customers* • District • Voters* • Elected Officials** • Municipal Offices** • State • Counties* • Representatives** • Company Headquarters • Examples: • Courses • Student Grades* • Instructors** • Faculty • Courses Taught* • Committees Served on ** • Department • Employees* • Contact Persons** • Vendor • Products* • Contact Persons** • Product • Component Parts* • Suppliers**

19. Sub Reports Cont. • Duplicate or repeating records in a report is a common sign that a sub report is needed to account for the second 1 to Many relationship in the data • Also happens when the underlying query includes data from multiple tables across multiple relationships • The Report Wizard often guides students to include too many fields from too many tables resulting in this issue • Guided Tutorial for Sub reports: • http://www.bsad.uvm.edu/files/tichitte/tutorials/accessreports/accessreports.html.

20. Sub Reports – Master/Child Fields • There needs to be a common field to link the main report to the sub report. • If N:M, include the intersection table in the SubQuery for the SubReport with the Foreign key that will link to the main report. • This is done through the Properties window for the Sub Report (Link Master/Child fields). • You need to the common field to be in BOTH the main query and the sub query.

21. Reporting Exercise • 2 Reports • 1st one is explicitly walked through • 2nd one is similar but up to you to construct • This Exercise DOES not address Sub Reports • Work with each other, make sure everybody is completing the steps on their own machine. • You have the finished product (in this case). Your task is to create an Automated version of the finished product you see..

22. Objectives of Exercise • Have you work through interfaces of an Automated Reporting Solution including: • Understanding what reports are made of • DON’T use a Wizard • Setting a record source • Navigating the Properties Window of the selected object • Working with ‘page space’ in a report • Applying Report Groupings with group header and footers • Working with Text boxes and Label Boxes (Bound Controls and Unbound Controls) • Working with Calculated Fields