Microsoft Access Course 2 Basic techniques Topic Summary

Microsoft Access Course 2. Basic techniques

Topic Summary more on. . . Ø Ø Table Design Query Design Forms Design Report Design

Table Design Key symbol indicates Primary key Field Size provides additional sizing info Caption value is used to label the column when shown on a form Validation is condition that new data must obey. Text is error message Indexed parameter specifies indexing on column: Yes, (duplicates OK) No, Yes (no duplicates) Key button sets Primary key

Query Example Result

Group by Query Example Shows definition of alias value Sigma button gives Extra ‘Total’ Line

Form Design 1 Ø Form Design is the most complex aspect of database development Ø A form can be based on a table or query and is said to be bound to the table/query. Ø Text boxes on the form show column data in the underlying table/query. Changes to the data in the boxes causes the underlying data to be changed. Ø A form may also be unbound i. e. it does not show any table/query data. Used for special applications such as menus. Ø The form may also use a number of other ‘controls’ – labels, buttons, list boxes, combo boxes, sub-forms etc

Anatomy of a form 1 Form can have 3 sections as shown. The Header and Footer are optional. The Header usually contains simple identification labels The Details section holds the data boxes and labelling etc When built using a Wizard the labels contain the column names. The Footer section usually contains various buttons for exiting, saving etc

Anatomy of a form 2 This box is the ‘form select box’. Clicking it causes the form to be selected. The textbox and the label are ‘tied’ together – both can be moved as one unit. Shows the label selected Shows the textbox selected

Object Properties Ø The form, form sections and form controls are all viewed as ‘objects’ with properties Ø The properties affect the appearance and behaviour of the object Ø The properties also include ‘event handlers’ – i. e. specification of action to be taken when certain events occur: eg. a button is pressed, a textbox value is changed. Ø The ‘property box’ shows the properties for the selected object. It can be opened from the right mouse button

Object Properties 1 Showing part of the property list for the Asset. Id textbox Tabs select available groups of properties Position properties Size properties Font properties

Object Properties 2 Showing event properties for the Asset. Id textbox. Associated with each event such as Before Update will be a procedure defined as a macro or VBA module.

Adding controls to a form – the Toolbox Most commonly used controls Drawing cursor Label Radio button Combo box Command button Wizard toggle. When this is depressed, a wizard will start when certain controls are placed on form Textbox Check box List box Image To use toolbox, click on tool. Cursor adopts tool icon. Pointing on form at target position, drag to give required size.

Summary of Form Controls Textbox – container that hold a variable data value or shows the content of a table/query value. Label – container that holds a constant value. Button – metaphor for a pressable button, used to activate a command - ie. macro or VBA code. List Box – static list of selectable options. Can be preset values or obtained from table/query. Combo Box – drop down list combining List box and textbox. Check Box – specifies a settable option. Radio button – specifies one of a set of exclusive options. Image – holds a graphical image.

Report Generator Ø The Access report wizard can automatically produce complex reports based on multiple tables and showing levels of summarisation. Ø Necessary joins are resolved automatically and incorporated into the report design. Ø It is sensible to use the wizard to generate the initial report but the layout will generally need further fine tuning.

Basic Report Example

Summary Report Example This is an example of a summarised report using the Asset and Branch tables. It effectively groups by Branch code/name and totals the value of assets within the branch. It was created initially using the wizard then modified. The design for this report is shown on the next slide.

Report Example Show the design for the Asset/Branch report. Note pairing of headers and footers. Compare with the structure of the output.