Intro to Access & Macros

1. Intro to Access & Macros CSCI130-03A Instructor: Dr. Imad Rahal

2. Introduction • What is a database? • Collection of related data arranged into related tables of rows & columns • Different from Excel? • Suitable for large data sizes • Models a complete business • Provides strongerbuilt-in data access functionalities • Rather than building them from scratch as in Excel • Provided thru a DBMS • Works with data of interest only • What is a database management system (DBMS)? • A collection of complicated software programs that organizes the data in a database and allows users to insert, update, delete or retrieve subsets of the data • Other provided features include: Access Control, Recovery, Concurrency etc …

3. Introduction • ATableholds data for one entity of the business • University example • Table is divided into rows calledrecords (or rows) • One row describes one unit in our table • E.g. one employee, product, customer, etc … • Row is divided intofields (or columns) • Properties of the entity • E.g. properties of an employee: Name, address, salary, position, etc … • Student table in a university database? • Fields? Records? • Every table must have aunique key • Unique value for every record in the table to assist in maintaining data integrity (Key field) • If more than 1 key exist, one is designated as primary key • Student table in university database • Unique attributes (key fields?) • Primary key?

4. Designing a Table in Access • Office ButtonNew under “Blank Database” on the right • Name your database file • Choose its storage location • Click on Create button • A window appears: under the “AllTables” section on the left • Click on the down arrow • Select “Object Type” • Click on the down arrow again and observe that now you can see all objects that can be created in Access: Forms, Tables, Queries, Reports, Macros, etc … • Select “Tables” and double click on the only table in the menu (usually called Table1) • In the upper menu (right above “Table1”): Click on View and select “Design View”…save table if prompted • Field part: Name and description of every field • Properties part: specific properties for each field

5. Designing a Table in Access • In addition to the type (Field Part), we need to set the following properties (Properties Part) • Required: Determines if the user is required to enter a value for this field for every record • Default Value • Input Mask: (for Text and Date fields only) • Sets a suitable pattern mask for data to be entered • like ###-###-#### for SSN • We can choose from common masks • #  number, space, or plus or minus • L A letters • A a letter or number

6. Designing a Table in Access • Validation Rule: • Determines what is a valid for this field • Department in EMPLOYEE table must be limited to certain names • In (“Sales”, “Production”, “Management”, “Design”) • < #1/1/1995# • LIKE "#####-####“ • LIKE "*smith##*" • LIKE "??00####" • Validation Text: specifies the message output to the user in case in valid input is provided (e.g. ‘Not a Valid Department’) • Set one field as the primary key • Select the field • Click on the key icon on the toolbar (notice the key appears next to the field)

7. Queries • A selection function • selects certain fields and recordsfrom a table according to certain criteria & displays results in a new table for a report • Record filter • Update, delete, & insert data into a table • Select Queries are very common • The produced table is a subset of the database • Limited by selected fields and matching records • Used to summarize or do simple calculations on the data in the database

8. Queries • Click on the down arrow on the left next to where it says “Tables” • Select “Queries” and double click on the only table in the menu (usually called Table1) • On the top menu, click on the “Create Ribbon” and then click on the “Query Design” button in the “Other” group • to be displayed in the query or • that participate in the selection criteria • (Not limited to the given fields) • Derive others

9. Queries • For each field we can decide to • Show it • Specify criteria to be met • (Numbers) • = 25 • <= 10 • Between 5 and 10 • (Strings) • “Management” • “Sales” or “Design” • Not “Sales” • In (“Sales”, “ Design”, “Production”) • Like “S*” • (Dates) • < #1-May-99#

10. Sample Queries on Gargoyle DB • 01 Accounting Employees (simple selection query) • Select first name, last name & Dept • “Accounting” Dept • 02a Management Fulltime Employees (uses AND) • First Name, Last name and Salary • “Management” and “Fulltime” • Same “Criteria” Row • 03 Sales or Management Employees (uses OR) • First Name, Last name, and Department • “Management” OR “Sales”

11. Sample Queries on Gargoyle DB • 02b Management or Fulltime Employees (uses OR for different fields) • First Name, Last name and Salary • “Management” OR “Fulltime” • Different “Criteria” rows • 04 Employees by Department (parameter query) • First name, last name • User inputs department • Parameter Queries • Substitute an input box for a criterion specified by the user each time the query is run • Good when criterion may vary but with the same selected fields • 05 First Names beginning with m • First names • Like “M*”

12. Sample Queries on Gargoyle DB • 06 First Names with 'a' as the second letter (wildcard char) • First names • Like “?a*” • ? Any character • * Any set of characters • 07 Names (Derived fields:uses Concatenation) • First name & “ ” & last name, Department, Salary • Reference fields in expressions between square brackets [] • Expressions have names that appear in table • Not limited to table fields • Sorting on one attribute (department) • 09 Salary with Bonus (Derived Fields) • “Lastname, Firstname” , salary + bonus • Sorting on one attribute (Full name)

13. Sample Queries on Gargoyle DB • **08 Salaries of employees hired in 1999 (uses a date function) • Lastname, firstname, & salary • Year, Month, Day • Hired in November of 2000? • Sort on LastName • 18 Orders within the last 5 years year (use of Now()) • OrderDate > Now()-5*365 • 10 Salary Data (uses severalaggregate functions) • Sum, Avg, max, Min salary • Notice the new Total row (3rd) • Can be added by clicking on the Totals icon in toolbar (SIGMA) • 11 Salary Averages (uses grouping and a aggregate functions) • Average salary by department • Format currency

14. Sorting Displayed Data • On a single field • Click anywhere in the corresponding column • Click appropriate sort icon in toolbar • More than one field • Order fields by sorting preference • Sales or Management Employees

15. Action Queries • Don’t create tables meeting search criteria • Unlike select queries • Change the data in tables in all records based on some criteria • Their actions can’t be undone(once saved) • Why do we need them…why not just do it in datasheet view? • Four types • Update queries • Edit data in an existing table • Delete queries • Remove records that match some criteria from a table • Append queries • Add new records from table to the end of another • Make-table queries • Create a new table consisting of the records that match a given criteria

16. Action Queries • To create a new action (update) query • Create a Select Query and make sure it selects the desired data (Add appropriate criteria) • Choose type from Query Type group • Query 14: Rename “Sales” to “Profit” (update query) • On table Employee • Update “Sales” dept. to " Profit” • Query 15: 5% raisefor full-time employees (Update Query) • On table employee • %5 raise for full time employees • Query 19: Delete employees with first names starting with “Ma” (Delete query)

17. Reports • Display data in an attractive form usually for printing • Reports are usually based on queries (MUST CREATE QUERY FIRST) • Start at the Database window • Click on “Create” ribbon • On the report group, click on “Report Wizard” • Select which query to base your report on • Select the required fields • Choose fields for grouping levels • Has its entries as the headings on the report • Employees by department • Cangroup on more than one level • Choose layoutand print style and then name it

18. Reports • Build a report based on the demo query • Show singlegrouping level • More than 1grouping level • Click on grouping field then on > sign • Show design view • (query 12) Employee And Manager List (relationship query) • Department, Manager and Employee Full name • Build sample report on

19. Relational Queries • (query 12) Display vendor contact info (contact person and phone number) for inventory products (relationship query) • Query: Inventory Product Info

20. DEPARTMENTS Department name* EMPLOYEES Employee#* Department 1 M 1 M PURCHASE ORDERS Product number Emp Number M 1 Inventory Product number* Relational Concepts • Employees of a department make orders for requested products • Not all tables are linked • Only related ones • Two tables can have multiple relationships • Table relationships/links can be • One-to-many • One department can have many employees • One-to-one • Limit every department to one employee • In Access, can’t be many-to-many

21. Using Multiple Tables • Linked fieldsdon’t need to have the same name E.g. Department & Department name • But must contain data of same data type • Field must bekey in at least one table • primary table vs. related table • Referential Integrity: • Every department in EMPLOYEES (related table) must exist in DEPARTMENTS (primary table) • The link is activated by a Join operation • Inner Join • Left Outer Join • Right Outer Join • Try Vendors and Inventory

24. Using Multiple Tables • How to create relationships between tables? • Click on the “Database Tools” tab • On the Show/Hide group, click on the “Relationships” button • Right click on pane and select “show table” • To remove tables from window • Select Table • Right Click  Hide Table • Join tables • Click on the field of one table and drag it over to the corresponding field in the other table • A window appears that describes the relationship

25. Key Field 3 Types of Joins 1 to many Used when a record of Departments (primary (1st) table) has its Join field changed  what would happen to corresponding fields in Employees(related (2nd) table)records? If not clicked  no ref. integrity If clicked  Every record in related Table must have a matching record in primary table; two other boxes  Cascade Update  Cascade Delete

26. The 1 is for the primary table Referential Integrity: We can’t have a record in the related table that has no matching record in the primary table Cascade Update If join field of some record in primary table is updated then update corresponding field in related table records E.g., “Design”  “Development” in Departments, then automatically all “Design” in Employees become “Development” Cascade Delete If record of some join field in primary table is deleted then delete corresponding records related table E.g. if we delete the “Design” department from Departments, then automatically delete all Employees record in the “Design” Department

27. Types of Joins • Decide how we go about concatenating records from related tables into 1 table • 3 options are given :Reason: not all records in primary table have matching records in related table • E.g. Department = { (‘CSCI’, SJU), (‘MGMT’, SJU) , (‘ACCT’, CSB)} • Student (name, Department) … 2 CSCI, 1 MGMT and 2 HIST • Inner Join (1): includes data only from records have matches records in both tables • Left Outer Join (2): includes all records from primary even those that have no matches in related • Right Outer Join (3): includes all records from related even those that have no matches in primary • in case referential integrity is not specified • NO JOIN!

28. Types of Joins • (Query that JOINS Vendors &Inventory) • Drop Join and show result • Inner Join: Inventory items with vendor information • Left Outer Join: All vendors will be shown even those not providing inventory items • Right Outer Join: All inventory items will be included even those with no vendor info • NO JOIN!

29. Forms • To view, input and update data in a database one record at a time • What about reports? • Allows for easier input and edit operations • Based on a table • unlike reports which are based on queries • To create a form • Click on Create tab • On the Formsgroup, click More Forms >> Form Wizard • Select the table used as a basis for the form in addition to all fields to be included • Forms for input shouldinclude all fields • Forms for editing need only toinclude the edited fields

30. Forms • Select one of the built-in layouts • Can be edited or changed later on • Choose the background pattern • Type in a name for the form • Change the form to look as you’d like • Add pictures, move elements, add common elements like menus, list boxes, or buttons • To modify, the form’s design either • Select Modify on final window of Form Wizard • Open form in Design mode • Form window is split into 3 regions (header, detail and footer) • Build Employee form • Add & Update records

32. Forms • To add a header • Place cursor on the boundary between header and detail • Drag till you get proper space • Add text to you header by • Clicking on Label icon in the VBA toolbox • Moving to the header • Entering text • At the bottom of the form, notice the number of the records we are viewing • Use arrows to move from record to record for editing • Click on the arrow followed by a * to get a blank form to fill a new record

33. Macros in Access • Macros are usually assigned a command button so they can be executed with a single mouse-click • Two ways: • Using built-in actions (from a drop down menu) • Create macro and then hook a button to it • Using VBA macros • Create button along with macro • Access has built-insubroutines called actions • segments of VB code written to perform common tasks • A macro consists of one or more actions put together to accomplish a task for the user • In general, Access macros are created using a menu system to choose the desired actions to be performed

34. A simple macro: • - “Move to next record” button • GoToRecord action • If you go beyond the available records you’ll get an error message

35. Creating Macros • Using built-in actions • Select Macros from list of objects on the database window • Choose Create >> Macro >> Macro • Add desired actions along with their descriptions • E.g. GotoRecord, Msgbox, OpenForm, OpenQuery, OpenReport, OpenForm, Quit, RunMacro, Save, Maximize, Close, etc … • OutputTo • Save macro with an appropriate name • Insert button (Cancel button wizard if it starts) • Right Click  Properties ALL Tab •  Change Name & Caption(Use name conventions) • (Scroll down to)  On Click • Click inside entry and then ondown arrow • (DO NOT CLICK on the three dots button) • Select from listof available macros

36. Accessing Access from VB • Concept of a RecordSet (cursor) • Form is linkedto a table through a recordset • An exact copy of the table that we can manipulated in the form • Important code comments • (1) Dim myDB As Database • (2) Dim myRecords As Recordset2 • (3)Set myDb = OpenDatabase(App.Path & "\Gargoyles.accdb") • (4)Set myRecordSet = myDb.OpenRecordset("Employees") • Any table name or could set it to a query • (5) myRecordSet.Index = "PrimaryKey“ • Relative movements are defined in PrimaryKey Order

37. Accessing Access from VB • Operate on a record set • myRecordSet![FieldName] • to access value of field for current record in recordset • myRecordSet.EOF • to check if we are at the end of the recordset • myRecordSet.MoveNext • Does not change the record currently displayed in form • Same applies to the following commands • myRecordSet.MoveFirst • myRecordSet.MovePrevious • myRecordSet.Move 10 • Moves 10 locations from current record • Relative Movement

38. Accessing Access from VB • Get the info from the current record in the recordset • MyRecordSet![FieldName] • MsgBox myRecordSet![FirstName] & " " & myRecordSet![LastName]