Agenda 03/04

1. Agenda 03/04 • Answer MS Access query questions • Discuss the necessary components of a business application • Understand the role of the application developer • Discuss SDLC • Understand the function of forms in a business application • Discuss the basics constructs of creating forms with MS Access • Understand how to customize forms with MS Access

2. What is a business system? • What are examples of business systems? • What do they have in common? • Why do we create business systems? • Why do we automate business systems? • What are the technical components of a business system application? • How do we get business system applications?

3. Definition: Systems Development Lifecycle (SDLC) • The process used to develop, implement and support a technology-based application system. • Process visualizations include waterfall, spiral, SCRUM, etc. • Each step in the process may feed into another step. Each step may get feedback from another step.

4. Alternative SDLC Depiction (Boehm, 1988)

5. Steps in SDLC – Define the project • Project definition • Identify scope, budget, goals. • Identify risks, opportunities, constraints • Plan the rest of the process (as best as possible) • Tools used • Gantt chart • SWOT

6. Steps in SDLC: Analyze the problem • Systems analysis • Understand problem to be solved. • Separate symptoms from problem. • Identify potential solutions. • Tools used • Cause and Effect analysis: Ishikawa diagram • Value chain modeling • Process modeling: data flow diagrams, flow charts, swim lane diagrams, use case diagrams, activity diagrams, unified modeling diagrams…

7. Steps in SDLC – Design the solution • Systems design • Create detailed blueprint of the solution. • Includes very detailed description of input, data and output. • Design the visualization methods that are part of the solution. • Design the overall programs/technology that compose the solution. • Create testing specifications – how will you know the system works correctly? • Tools used • Data modeling: ERD’s, unified data models • Process modeling • Screen diagramming

8. Steps in SDLC – Buy a solution • Procure a solution • Many organizations have similar problems. • Application vendors sell both generic and parameter driven solutions. • Almost always less expensive to buy a solution. • Tools used • Request for Information (RFI) • Request for Proposal (RFP) • Demonstrations • Benchmarks • Cost/benefit evaluation

9. Steps in SDLC – Create a solution • Programming/Making the technology from existing tools • Choose appropriate technical environment. • Develop technical solution with appropriate tools and existing applications. • Test technical solution in parts. • Document technical solution for systems professionals. • Tools used • Data and process modeling. • Metadata repository. • Documentation repository. • Automated testing environment. • Regression testing.

10. Steps in SDLC – Install the solution • Installation • Test entire technical solution. • Decide how to convert data and existing processes. • Help people adjust to new system. • Perform training and create user-oriented documentation. • Tools used • Documentation repository. • Social networking tools: websites, blogs, wiki’s, etc. • Data upload/download and import/export tools.

11. Steps in SDLC – Support solution forever • Support • Assume a relatively long life span of computer applications. Even those that were developed quickly to solve an immediate business problem seem to last a surprisingly long time. • Assume that the application becomes the way people do work. • Ongoing training. • Ongoing documentation. • Ongoing testing. • Ongoing changes: • Government regulation changes. • New business conditions. • People use the application, learn new ways to complete work effectively, and then want changes to incorporate their learning into the application.

12. Components of a business application Data Access: Convert data to information and make it available (Queries, forms, reports) Data Access: Convert data to information and make it available (Queries, forms, reports) Data Maintenance: Ongoing adding, deleting, and changing data (Forms) Data Maintenance: Ongoing adding, deleting, and changing data (Forms) Initial Data Population Initial Data Population Data Storage: Database Data Storage: Database

13. Data Storage: Database • Application developer’s role: • Define what data should be stored • Design the database including tables, attributes, and relationships • Create the database with appropriate constraints to protect data integrity

14. Initial Data Population • Usually done by importing data from another computer application: Database, spreadsheet, text • Occasionally done through human input (rarely) • Role of an application developer: • Decide how to input the data • Create the methods • Supervise or actually conduct data population

15. Data maintenance • Add, delete, and change data • Done through: human input, computer connection, automated input devices, or combination of some/all • Role of an application developer: • Choose the appropriate methods • Design, program and test appropriate methods • Train • Rarely does actual data entry

16. Maintaining Table Data Using a Form • Maintaining data using a form is often easier than using a datasheet, because you can concentrate on all the changes required to a single record at one time

17. Data access • Converting data to information • Referred to as “accessing” or looking at the data: queries, reports, forms, viewing, visualizing, etc. • Role of an application developer: • Choose the appropriate methods in conjunction with discussions from the people who need the information for decision making • Design the methods • Program, test, and train users to access the information successfully

18. Data access methods in MS Access • Queries • Used to view sub-sets of data • Creates a “virtual table” to show information from underlying data • Used to create information by: • Combining data from multiple tables into a single table; • Performing, calculations, conditions, and grouped summaries • Forms and Reports • Used to look at data from tables and/or queries • Used to enhance the formatting of information by: • Adding navigation (buttons, combo boxes, list boxes); • Adding visually pleasing elements such as lines, colors, boxes, etc.

19. Forms vs. Reports

20. Purpose of using forms • To “mask” the underlying database from casual users. • To enter data into tables. • To look at data in a more informative format than available in datasheet view. • Forms are created by computer system professionals for use by people who are not computer system professionals. • If forms don’t make data easier to enter, then they aren’t useful. • If forms don’t provide more information than can be obtained from a datasheet view, then they aren’t useful.

21. Views of a form • Available views of a form • Form view: How the form will look and feel to the user of the system • Layout view: Beginning application developer view. Puts data into the form, but still allows some modification of the form. More visually oriented than design view for application development • Design view: Advanced application developer view. No data is in the form, allows everything to be manipulated on the form. Much more flexible than layout view

22. Creating a form • Simple form • Form wizard • Choose table/query • Choose fields • Choose format • More forms • Blank form

23. Form Structure • Form Header • Page Header • Detail • Page Footer • Form Footer • Layout and design tools • Drawing area • Properties

24. Form Views • Form view • Shows what the form will look like to the user • Use form view to test and make sure things are working the way you intend them to. • Layout view • Shows the design of the form with data in it. • Controls can be modified, created and moved in this view. • Layout view is easier to use but more constrained than design view. • Design view • Used to modify the form, no data is shown in this view. • All forms are composed of: Form header, detail, and form footer. • Property Sheet: • Available in layout and design views. • Explains the overall properties of the form as well as detailed information about each object on a form.

25. Controls • A control is an object on a form that passes information between the user and the form • Examples of controls are labels, boxes, buttons • Types of controls are: • Bound control: Connected or bound to a field in the database • Unbound control: Not connected to a field in the database. Includes labels, lines, pictures, etc. • Calculated control: Displays a value that is the result of an expression. Expressions are just like the ones in queries: IIF, sum, count, mathematical expression • The goal of these navigation methods (buttons and combo boxes) is to provide an “easy to use” method of data entry.

26. Combo Box • A combo box is a control that provides the features of a text box and a list box; it lets you choose a value from the list or type an entry • You use the Combo Box tool in Design view to add a combo box to a form • A Control Wizard asks a series of questions and then uses your answers to create a control in a form or report (used for controls such as combo boxes and buttons).

27. Creating a form with a main and sub form • When you create a form containing data from two tables that have a one-to-many relationship, you actually create a main form for data from the primary table and a subformfor data from the related table

28. How to add a Subform to a Form • You use the Subform/Subreport tool in Design view to add a subform to a form • In the Controls group on the Design tab, click the More button to open the Controls gallery, make sure the Control Wizards tool is selected, and then click the Subform/Subreporttool • Main form/subformstructure follows the database. Main form is the parent table, and subform is the child table.

29. Subform in Form view

30. Displaying a Subform’s Calculated Controls in the Main Form • Create a textbox in the footer of the subform that will perform the calculation. • Name the textbox in the subform accordingly (txtInvoiceAmtSum). • Use the name of the textbox from the subform as the control source for the textbox on the main form that will display the information in the main form. • [SubformName].[Form]![TextboxName]

31. Displaying a Subform’s Calculated Controls in the Main Form Text box on subform will perform the calculation Text box control on main form shown in design view. Set to [SubformName].[Form]![TextboxName] Text box control on main form in property sheet

32. Tips for creating forms • Save. Always save before doing something new. • Only save something new after it’s been tested and it works. • Test. Never invest time in making anything pretty until you test it! • Make it pretty and save it if it works • Don’t put spaces in names of controls! • Make sure to review the property sheet. The property sheet is always the key to understanding the contents of a form.