Data Clean-Up and Management: A Practical Guide for Librarians

By Margaret Hogarth and Kenneth Furuta

Data use in the library has specific characteristics and common problems. Data Clean-up and Management addresses these, and provides methods to clean up frequently-occurring data problems using readily-available applications. The authors highlight the importance and methods of data analysis and presentation, and offer guidelines and recommendations for a data quality policy. The book gives step-by-step how-to directions for common dirty data issues.

• Focused towards libraries and practicing librarians
• Deals with practical, real-life issues and addresses common problems that all libraries face
• Offers cradle-to-grave treatment for preparing and using data, including download, clean-up, management, analysis and presentation

• Language: English
• Publisher: Elsevier Science
• Release date: Oct 22, 2012
• ISBN: 9781780633473

Margaret Hogarth

Margaret Hogarth is Electronic Resources Coordinator and Subject Specialist for Environmental Sciences, Water and Soils for the University of California, Riverside Libraries. She has a B.A. in English from the University of California, Santa Barbara, an MLIS from San Jose State University and an M.S. in Environmental Studies from California State University, Fullerton. She has been a librarian since 1999.

Book preview

Data Clean-up and Management

A practical guide for librarians

Margaret Hogarth

Kenneth Furuta

Table of Contents

Cover image

Title page

Copyright

List of figures

List of tables

About the authors

Chapter 1: Introduction (why this book is needed)

Abstract:

What makes this book unique?

Why library data is important

The book’s outline

Chapter 2: Commonalities

Abstract:

Microsoft Office Excel

MarcEdit

Microsoft Access

XML

Commonalities

Capture and use

Standardization

Data import issues

Technical skills

Project management challenges

Chapter 3: Defining data

Abstract:

Rule 1: define data points

Rule 2: apply data point definitions

Rule 3: count the right apples

Rule 4: avoid capturing redundant data

Chapter 4: Types of data issues

Abstract:

Microsoft Excel vs Microsoft Access

General data-handling edicts

Data issues: importing data

Chapter 5: Microsoft Excel techniques

Abstract:

Creating datasheets

Selecting cells

Copying

Sorting

Filter

AutoSum

Sum

Fill

Chapter 6: Data clean-up in Excel

Abstract:

Common dirty data scenarios

The usefulness of delimiting

System limitations

Removing extra characters

Chapter 7: Excel: combining data

Abstract:

IF statements

The TEXT function

PivotTables and filtering

VLOOKUP

HLOOKUP

MATCH

Chapter 8: Additional tools

Abstract:

PDFs

Notepad

Microsoft Word

Global update in an integrated library system

Regular expressions

Excel

Access

Macros

XML

MarcEdit

The MARC tools window

Chapter 9: Access techniques

Abstract:

What is a database?

Access

Planning a database

Preparing data for a database

Adding a table to a database

Chapter 10: Access forms

Abstract:

Types of form

Parts to a form

Form controls

Validating data

Option buttons

Combo boxes

For a Spin Button:

Tab control techniques

Multiple-table forms

Chapter 11: Access reports

Abstract:

Creating a report using the Report Wizard

Controls

Making additions to a report

AutoFormat a report

Working with report properties

Inserting a control into a report

Conditional formatting

Sizing reports

Moving controls in Access

Publishing reports

Sorting and grouping options

Adding calculations to reports

Launching reports

Creating a subreport

Chapter 12: Access queries

Abstract:

Sorting in Access

Filtering in Access

Queries

Entering data

Query properties

Access relationships

Chapter 13: Data clean-up in Access

Abstract:

Prevention is the best cure

Extra characters

Access data upload errors

ISSN issues

Chapter 14: Access – combining data

Abstract:

Combining data from one or moredata sources

Query with a sum

Types of operators

Totals queries

Parameter queries

Action queries

Update queries

Delete queries

Make-Table queries

Append queries

PivotTable queries

SQL in Access

Parameter Queries in SQL

Export data to Excel

Finding unique values in a dataset

Matching on ISSN

Chapter 15: Strategies for missing data

Abstract:

Resources are missing ISBNs

Resources are missing ISSNs

Richard Jackson’s OCLC look-up strategy

Chapter 16: Qualitative data

Abstract:

The definition of qualitative data

Qualitative data is valuable

Types of qualitative data

Qualitative data techniques

SWOT analysis

Tools

The whole picture

Chapter 17: ROI

Abstract:

Chapter 18: Data collection and analysis

Abstract:

What data do you need to answer the question?

Does the data measure what you need to measure?

Analysing data

Data presentation

Charts

Stacked charts

Chapter 19: Data quality policy

Abstract:

Poor data quality

Data as an asset and a product

Apply quality principles

Process design

Framework for a data quality policy

Chapter 20: Next steps

Abstract:

Appendix 1: Excel techniques

Appendix 2: Excel functions

Appendix 3: Access quick keys

Appendix 4: Redman’s model data policy

Bibliography and references

Index

Copyright

Chandos Publishing

Hexagon House

Avenue 4

Station Lane

Witney

Oxford OX28 4BN

UK

Tel: + 44 (0) 1993 848726

Email: [e-mail]

www.chandospublishing.com

www.chandospublishingonline.com

Chandos Publishing is an imprint of Woodhead Publishing Limited

Woodhead Publishing Limited

80 High Street

Sawston

Cambridge CB22 3HJ

UK

Tel: + 44 (0) 1223 499140

Tel: + 44 (0) 1223 832819

www.woodheadpublishing.com

First published in 2012

ISBN 978-1-84334-672-2 (print)

ISBN 978-1-78063-347-3 (online)

© M. Hogarth, 2012

British Library Cataloguing-in-Publication Data.

A catalogue record for this book is available from the British Library.

All rights reserved. No part of this publication may be reproduced, stored in or introduced into a retrieval system, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written permission of the Publishers. This publication may not be lent, resold, hired out or otherwise disposed of by way of trade in any form of binding or cover other than that in which it is published without the prior consent of the Publishers. Any person who does any unauthorized act in relation to this publication may be liable to criminal prosecution and civil claims for damages.

The Publishers make no representation, express or implied, with regard to the accuracy of the information contained in this publication and cannot accept any legal responsibility or liability for any errors or omissions.

The material contained in this publication constitutes general guidelines only and does not represent to be advice on any particular matter. No reader or purchaser should act on the basis of material contained in this publication without first taking professional advice appropriate to their particular circumstances. Any screenshots in this publication are the copyright of the website owner(s), unless indicated otherwise.

Typeset by RefineCatch Ltd, Bungay, Suffolk

Printed in the UK and USA

List of figures

3.1. LibStats data entry form 16

3.2. Entity and relationship model 20

3.3. Possible relationships between entities 21

3.4. Librarians and patrons are related a minimum of zero and a maximum of many to reference service 21

3.5. Entity relationship diagram for LibStats database 23

4.1. Excel spreadsheet summarizing COUNTER Database 3 data 34

4.2. Excel spreadsheet summarizing non-COUNTER- compliant Database 3-type data 35

4.3. Example download showing ISSNs with a space and quote or 'no data' in the field 38

4.4. Example download showing comma delimitation on the title 38

4.5. Example showing scientific notation for a number in a field 38

4.6. Example of downloaded data skewing into multiple columns 39

4.7. Example of XML 42

5.1. Columns describe each entry, transaction, person, thing or entity 44

5.2. Excel example of VLOOKUP 46

5.3. Excel Paste Special 46

5.4. Excel Paste Values 47

5.5. Excel table sorted alphabetically by Resource Name 48

5.6. Excel data toolbar 48

5.7. Excel sort dialog box showing Sort by, Sort On and Order choices 49

5.8. Excel Sort On value choices 49

5.9. Excel table sorted, largest to smallest, by Sum of Hits 50

5.10. Excel table sorted by Record #(BIB) and by title 50

5.11. Excel Sort by dialog box 51

5.12. Excel Sort by Spec Note with additional field, Amount Paid, largest to smallest, added 51

5.13. Excel table sorted by Spec Note, then Amount Paid, largest to smallest, for Anthropology 51

5.14. Excel table sorted by Spec Note, all disciplines 52

5.15. Excel table sorted by ISSN to illustrate inconsistent or dirty data 53

5.16. Excel table sorting on ISSN A-Z 54

5.17. Excel table sorted on ISSN 55

5.18. Excel table, clean ISSNs are copied from Column H to Column A 55

5.19. Excel table sorted on Column J. Copy ISSNs from Column J to Column A 56

5.20. Excel table sorted on Column K to show misplaced data 57

5.21. Excel table showing outlier ISSN in Column P 58

5.22. Excel table showing non-normalized ISSNs in Column A 59

5.23. Excel table, Columns B and C highlighted as two new columns are added to the right of Column A 59

5.24. Excel table showing two new columns added to the right of Column A 59

5.25. Excel table showing Text to Column dialog, Step 1, Delimited data type 60

5.26. Excel table showing Text to Column dialog, Step 2, Delimit on semicolon and text qualifier " 60

5.27. Excel table showing Text to Column dialog, Step 3, set data format and destination 61

5.28. Excel table showing Text to Column Dialog, Step 4, should the contents of the destination cells be replaced? 61

5.29. Excel table showing results of Text to Columns exercise, separating the eISSN from the print ISSN 62

5.30. Excel table showing Text to Columns dialog, Step 1, Fixed width data type 63

5.31. Excel table, Text to Column dialog, Step 2, setting the break line 64

5.32. Excel table, Text to Column dialog, Step 3, set column data format and destination 64

5.33. Excel table, Text to Column dialog, Step 4, should contents of the destination cells be replaced? 65

5.34. Excel table showing Text to Column dialog, Step 5, (online) in Column C 65

5.35. Example of skewed payment and other data in an Excel spreadsheet. This file would require extensive clean-up 66

5.36. Excel table sorted by Call Number order 67

5.37. Excel table sorted by call number order showing TN941.C571 pushed to the top by adding a space in front of the call number 67

5.38. Excel column filter button, used to set filter criteria 68

5.39. Excel table, Column C dialog box initiated. Defaults to Text Filter, Select All 68

5.40. Excel table showing Custom AutoFilter dialog for all variations of Anthropology 69

5.41. Excel table showing Custom AutoFilter results for all values of Anthropology 70

5.42. Filter icon showing a filter has been applied to a column 70

5.43. Excel table showing Filter settings for all values of Anthropology 70

5.44. Excel table showing filtering values above average 71

5.45. Excel table with Above Average filter applied to Column D, Amount Paid. Report is along the bottom of the window 72

5.46. Excel AutoSum button on Home tool bar 73

5.47. Excel AutoSum functions 73

5.48. There are many Excel AutoSum functions to choose from 73

5.49. Excel AutoSum. Position the cursor in the cell below the last figure in the column 74

5.50. Excel column showing AutoSum's prediction of a range of cells 74

5.51. Excel column showing AutoSum-installed sum of column once Enter is pressed 75

5.52. Excel table showing AutoSum-prediction of row sum 76

5.53. Excel table showing AutoSum formula in E Column copies to multiple rows 77

5.54. Excel table showing AutoSum formula in Row 16 totaling each column, Columns B-E 77

5.55. Excel table showing a sum formula for several non-adjacent cells 78

5.56. Location of Fill command on Home Tool Bar 80

5.57. Options for specifying the direction of the Fill in Excel 80

5.58. Types of Series in Excel 80

6.1. A file imported into Excel has dropped the leading zeros in the ISSNs 84

6.2. In the Excel Import Wizard, by changing the Data Format to Text, leading zeros will not be lost 84

6.3. Excel displaying numbers in scientific or exponential notation 85

6.4. Column format changed to Number in Excel 85

6.5. Button to remove decimal spaces in Excel 86

6.6. Two extra decimal spaces removed from an Excel table 86

6.7. Title delimited on the comma into multiple columns in Excel 87

6.8. Excel file type delimiting dialog 87

6.9. Excel file type delimiting dialog showing Tab delimiter 87

6.10. System showing export delimiter dialog 88

6.11. System with all data exporting into Column A 89 89

6.12. Columns Wizard Dialog in Excel, choosing Fixed Width 89

6.13. The Excel Text to Columns Wizard 90

6.14. Division for INTL USE in the COPY USE column in the Text to Columns dialog in Excel 90

6.15. Excel Text to Columns Wizard, with break lines in the correct position 91

6.16. Convert Text to Columns Wizard in Excel, where data format and destination are chosen in Step 3 91

6.17. Columnar data sorted into appropriate columns in Excel 92

6.18. System without a limitation on the number of characters in the title field 93

6.19. Using the Excel LEFT command to list the first 24 characters of the title field 94

6.20. ISSNs without hyphens 95

6.21. Missing hyphens replaced using the MID command in Excel 96

6.22. ISSNs missing hyphens and sometimes leading zeros in the Print ISSN column 96

6.23. Dialog box to sort the Print ISSN column smallest to largest in Excel 97

6.24. Print ISSN column sorted smallest to largest, grouping similar digits 97

6.25. Excel spreadsheet showing the CONCATENATE function being applied to fill in an incomplete ISSN 98

6.26. The CONCATENATE formula modified to add four zeros instead of six 98

6.27. Using CONCATENATE in Excel to join several cells and text phrases 99

6.28. Excel table showing ISSNs missing leading zeros and hyphens 99

6.29. Example in Excel using the Format Cells function 100

6.30. Format Cells option, Number, Custom and format 0000–0000 100

6.31. Example in Excel with the Custom Number format for ISSNs applied; ISSNs with X in them are not formatted 101

6.32. Excel Print ISSN column sorted by ISSN groups 101

6.33. Excel example showing application of the MID function to put a hyphen in an ISSN 102

6.34. Excel table with Print ISSNs formatted using the Format Text option 102

6.35. Excel PivotTable example showing ISSNs formatted as numbers 103

6.36. Excel example showing downloaded data with a space and quote mark before each ISSN 103

6.37. Excel Find and Replace dialog box, with [space]" in the Find what box and Replace with box blank 104

6.38. Excel spreadsheet with all of the 'used' cells selected 105

6.39. The Delete Cells function in Excel 105

6.40. Excel file showing extra spaces at the beginning of ISSNs in rows two and three 106

6.41. Excel spreadsheet showing the TRIM function applied to ISSNs 106

6.42. Excel spreadsheet with cleaned ISSNs replacing dirty ISSNs with the Paste Special function 107

6.43. The ASCII Code Chart 108

6.44. ASCII values 0–31, 127 = non-printing control characters 108

6.45. Excel file showing ASCII character 7 in cell A1 108

6.46. Excel file showing application of the CLEAN function in cell C1 109

6.47. Excel file showing the SUBSTITUTE function used to replace ASCII eight-bit characters with seven-bit characters for spaces 109

6.48. Excel example showing the TRIM and SUBSTITUTE functions being applied simultaneously to change ASCII eight-bit non-breaking space to the seven-bit space character and then removing the spaces 110

6.49. Excel SUBSTITUTE function used to replace the eight-bit DEL character with the BEL character 110

7.1. Excel file showing reference data 112

7.2. Excel file showing If statement applied to location_name; the result is reported in Column G 112

7.3. Excel table showing usage data 113

7.4. Excel worksheet showing application of nested If statements 113

7.5. Excel worksheet with nested If statements corrected to include the value 0 114

7.6. Excel table showing reference transactions 114

7.7. Excel file with TEXT function used, calculating the day of the week based on the date 115

7.8. Using the TEXT function in Excel to add a currency symbol 115

7.9. Using the TEXT command in Excel to calculate the cost per download and format the result 116

7.10. Excel worksheet showing selected data in order to create a PivotTable 117

7.11. Excel PivotTable dialog 117

7.12. Excel PivotTable dialog where fields for the report are chosen 118

7.13. Excel PivotTable column labels, row labels and values are chosen in this step of the PivotTable dialog 119

7.14. PivotTable Field List in Excel, showing Sum of question_id 119

7.15. Value Field Settings in the Excel PivotTable 120

7.16. Value Field Settings dialog box in Excel PivotTable 120

7.17. Questions summarized by location and day of the week in a PivotTable in Excel 121

7.18. Data in an Excel PivotTable showing location within libraries 121

7.19. Data in an Excel PivotTable showing number of questions by location in the library by day 122

7.20. Dialog box to format numbers in an Excel PivotTable column 122

7.21. Format Cells dialog box in Excel 123

7.22. The Data, Refresh All option in an Excel PivotTable 123

7.23. How to drag and drop a field name into the Excel PivotTable Report Filter box to begin the Filter dialog 124

7.24. The Select Multiple Filters option in a Excel PivotTable Filter dialog 125

7.25. PivotTable > Options in an Excel PivotTable 125

7.26. Choices for report layout and display in Excel PivotTable Options 126

7.27. Specifying the display order in the Excel PivotTable Display 126

7.28. Dialog box to specify where totals are displayed in an Excel PivotTable 127

7.29. Dialog box to specify Display and Field list characteristics in the Excel PivotTable Option dialog 127

7.30. Dialog box for repeat printing of row labels in PivotTable Options 128

7.31. Dialog box for associating source data with PivotTable data in PivotTable Options 128

7.32. Options for grouping data in an Excel PivotTable 129

7.33. The Group option for a column in Excel 130

7.34. The Hours option for grouping data in an Excel PivotTable 130

7.35. Reference question count grouped by hour in an Excel PivotTable 131

7.36. The choose fields dialog box in an Excel PivotTable 132

7.37. Data grouped by hour and day of the week in an Excel PivotTable 133

7.38. The data shown in Figure 7.37 presented in an Excel PivotChart instead of an Excel PivotTable 133

7.39. COUNTER data for an Excel PivotTable with more than one column 133

7.40. Dialog box for choosing fields to add to a report in an Excel PivotTable 134

7.41. Each journal has a total line in Excel by default 135

7.42. Dialog box to turn off subtotals in an Excel PivotTable 135

7.43. Two Row Labels displays better without the subtotals enabled in the Excel PivotTable 136

7.44. Dialog box to remove blank lines in a column in an Excel PivotTable 137

7.45. Dialog box to remove blank lines in a column in an Excel PivotTable; the (blank) values of the Journal column are not selected 138

7.46. The Excel PivotTable without the (blank) values showing 138

7.47. Example Notepad text file of VLOOKUP statements 140

7.48. Excel VLOOKUP example in which the formula is looking up the circulation value from another spreadsheet, matching on the shorter title on the left 141

7.49. Excel HLOOKUP matches a value and copies the value over horizontally 143

7.50. Excel HLOOKUP table in which the formula returns the third value in column A 143

8.1. Word paragraph options 147

8.2. ILS single record example showing holdings statement in the electronic check-in record 148

8.3. ILS single record example 149

8.4. MARC record showing 007 field 150

8.5. MARC record showing the 793, 856 and 910 fields 151

8.6. ILS 'global update' showing deletion of the 007 field in the MARC record 151

8.7. ILS 'global update' showing deletion of the 793 field in the MARC record 151

8.8. ILS 'global update' showing deletion of the 856 field in the MARC record 152

8.9. ILS 'global update' removing '/SCP' from the 910 field in the MARC record 152

8.10. ILS 'global update' deleting the 910 field containing 'SCP' 153

8.11. ILA 'global update' showing a summary of change commands 153

8.12. ILS 'global update' showing a summary of changes made 154

8.13. Innovative Millennium showing the Matching condition used for the list search 155

8.14. Sort example in Excel showing that leading spaces sort first 170

8.15. Microsoft Access wildcards 172

8.16. Microsoft Access query example looking for cell variations of 'bnd' in the holdings statement 172

8.17. Access query results for all variations of 'bnd' in the holdings statements 173

8.18. Access query statement looking for all holdings statements with 1st, 2nd, 3rd, 4th, etc 173

8.19. Access query results looking for holding statements with 1st, 2nd, 3rd, 4th, etc 174

8.20. The Access query is modified to exclude 'bnd' 174

8.21. Access query results showing 'bnd' excluded 175

8.22. Access query limiting to numbers with 'st,' 'nd,' 'rd,' or 'th' 175

8.23. Access query results limiting to digits adjacent to 'rd,' 'st,' 'th,' and 'nd' 176

8.24. Access query to find double spaces before letters 177

8.25. Access query results with double spaces before letters 177

8.26. Access query to find double spaces before digits 178

8.27. Access query results showing double spaces before digits 178

8.28. Access query to find holdings statements with 'set' in them 179

8.29. Access query results showing records with 'set' in the holdings statement 179

8.30. Access query to find holdings statements with 'ser' in them 180

8.31. Access query results showing holdings statements with 'ser' in them 180

8.32. Access query to find holdings statements with 'no.' in them 181

8.33. Access query results showing records with 'no.' in them 181

8.34. Access query looking for holding statements with ':no.' in them 182

8.35. Access query results showing records with the sequence ':no.' in them 182

8.36. Access query looking for holding statements with ':' followed by letters 183

8.37. Access query results showing records with ':' followed by letters 184

8.38. Access query to find all variations of v. 1:1with or without a space 184

8.39. Access query results showing variations of 'v. 1:1' with or without the space 185

8.40. Access query to match all 'keeps' holdings statements 185

8.41. Access query results showing 'keeps' holdings statements 186

8.42. Access query looking for holdings statements with 'kept' in it 186

8.43. Access query results showing holdings statements with 'kept' in them 187

8.44. Access query looking for all variations of 'keep' in the holdings statement 187

8.45. Access query results showing holdings statements with all variations of 'keep' 188

8.46. Access query limiting to all variations of 'keep' and not 'keeps' 188

8.47. Access query results showing holdings statements with 'keep' in them 189

8.48. Macro Express example adding a new electronic resource note for the 910 field 192

8.49. Macro Express example adding a revision note for the 910 field 193

8.50. Macro Express example adding a 793 note showing a journal package 193

8.51. Macro Express script inserting holdings, package, access information and a link in the 856 field 194

8.52. The Microsoft icon in the upper left of an Excel screen used to enable macros 194

8.53. The Excel Options button at the bottom of the dialog box 195

8.54. The Show Developer tab in the Ribbon option 195

8.55. The Developer tab included on the toolbar 196

8.56. Options available in the Developer tab in Excel 196

8.57. The Disable all macros with notification option in Macro Settings in Excel 196

8.58. Example weekly usage report, which is processed the same way each week 197

8.59. The 52 lines of data in the example report 197

8.60. Example of the weekly report in Excel once processed 198

8.61. Example of a completed Record Macro dialog box 198

8.62. The Record Macro option in the Code pane of the Developer tab 199

8.63. The Stop Recording option in the Developer tab Code pane 199

8.64. The stop recording icon in Excel 200

8.65. The Macro Security option in the Code pane of the Developer tab in Excel 200

8.66. The Disable all macros with notification option in Macro Settings 201

8.67. The From XML Data Import option in Excel 203

8.68. The MarcEdit main menu 204

8.69. The File menu in MarcEdit 204

8.70. The MARC Tools option from the main window 205

8.71. Input and Output file options and their location in MARC Tools 205

8.72. How to specify converting from MARC21XML to MARC in MarcEdit 206

8.73. The Tools > Export Tab Delimited Records option in MarcEdit 206

8.74. The Set File Paths option and the field delimiter in MarcEdit 207

8.75. The Add Field button in the Utilities Dialog in MarcEdit 207

8.76. An example of a file after MarcEdit processes it and exports certain fields 208

8.77. The Add/Delete Field in the Tools menu in MarcEdit 209

8.78. The Add/Delete Field Utility in MarcEdit 209

9.1. Example of a data autopsy for the ARL report questions and where the answers come from 212

9.2. A typical COUNTER J1 report in Microsoft Excel 213

9.3. The entity relationship between Journal_Cost (a minimum of zero) and COUNTER_J1_TYD_Total (a maximum of many) 214

9.4. Views available in Microsoft Access Home 216

9.5. Access Design View 216

9.6. Access SQL View for a query from two tables 217

9.7. Access Pivot Chart View comparing costs for Art and Biology titles 217

9.8. Access Pivot Table View summarizing the amount paid for various print disciplines 218

9.9. Access Datasheet View showing electronic journal information 218

9.10. Typical COUNTER J1 report for the first half of 2011 in Microsoft Excel 221

9.11. Modified master column headings for COUNTER J12011 reports 221

9.12. The Blank Database option in Access 221

9.13. The Blank Database dialog box in Access 222

9.14. Datasheet View in Access 222

9.15. Blank table in a new Access database, ready for the fields to be defined 223

9.16. The External Data tab in Access 223

9.17. The 'Import the source data into a new table in the current database' option in Access 224

9.18. The Show Worksheets option in Access 224

9.19. The First Row Contains Column Headings option in Access 224

9.20. Field Options, including the Data Type field, in Access 225

9.21. Field Options, including the Do not import field, in Access 225

9.22. The Let Access add primary key option in Access 225

9.23. The Import to Table: option in Access 226

9.24. Access auto-numbered records in a table 226

9.25. A fictional COUNTER Journal 1 report 226

9.26. The Table option on the Create tab in Access 227

9.27. A newly created table defaults to the Datasheet View 227

9.28. Datasheet View in Access 228

9.29. The Save As option in Design View in Access 228

9.30. The table field in Design View in Access 229

9.31. Data Type options in Access 229

9.32. The General Field Properties tab in Access 230

9.33. The Input Mask Wizard dialog box in Access 231

9.34. The Order_date input mask in the General Field Properties box in Access 231

9.35. An input mask applied to the Order_date field in Datasheet View 232

9.36. The Copy option for an Access table 232

9.37. The Paste option for an Access table 233

9.38. The Structure and Data option in Paste Table As for an Access table 233

9.39. The Structure Only option in Paste Table As for an Access table 234

9.40. Field Name and Data Type columns for an Access table 234

9.41. Field Name and Data Type columns for an Access table, with Field Names updated 235

10.1. The Form Wizard in the Create tab in Access 238

10.2. Options in the Form Wizard in Access 239

10.3. The Columnar layout option in the Form Wizard in Access 239

10.4. The Tabular layout option in the Form Wizard in Access 240

10.5. The Datasheet layout option in the Form Wizard in Access 240

10.6. The Justified layout option in the Form Wizard in Access 241

10.7. Style options in the Access Form Wizard 241

10.8. The Modify the form's design option in the Access Form Wizard 242

10.9. Design View in the Access Form Wizard 242

10.10. The Show/Hide button to display or suppress the Form Header or Footer on an Access form 243

10.11. Separator bars in Design View on an Access form 244

10.12. Access form in Form View 244

10.13. Form label text is changed in the Caption field on the Format tab 246

10.14. The View option in Access 246

10.15. Options in the status bar in Access 247

10.16. The Create tab in Access 249

10.17. Fields on an Access form, which can be added to existing table fields 249

10.18. The Design tab in Form Design Tools on an Access form 250

10.19. Comparison operators available for an Access form conditional control 251

10.20. The Conditional formatting option in an Access form control 251

10.21. Conditional Formatting options on an Access form 252

10.22. The Field Has Focus option in the Conditional Formatting dialog box on an Access form 252

10.23. The Add > > button at the bottom of the Conditional Formatting dialog box 253

10.24. Assigning the Journal Form control a keyboard shortcut by adding an '&' in the caption field on the Format property tab 254

10.25. The keyboard shortcut Journal: 255

10.26. Choices for data input masks in the Access Input Mask Wizard 256

10.27. The Use Control Wizards button in the Controls Group of the Access Form Control Wizard 258

10.28. The Option Group button in the Access Form Control Wizard 258

10.29. Label Names in the Option Group Wizard 258

10.30. Adding Label Names in the Option Group Wizard 259

10.31. 'Yes, The Default Choice Is' option in the Option Group Wizard 259

10.32. Assigning a unique value for each option in the Access Option group 260

10.33. Types of controls in the option group and styles in the Option Group Wizard 260

10.34. The option group caption in the Access Form Option Group 261

10.35. The completed Access Option group as it appears in Design View 261

10.36. The completed Access Option group as it appears in Form View 262

10.37. All of the Access Form Option Group choices are summarized on the property sheet 262

10.38. The Combo Box in the Control Wizards 263

10.39. The 'I will type in the values that I want.' option in the Combo Box Wizard 263

10.40. Adding the number of columns in the Access Combo Box Wizard 264

10.41. The 'Store that value in this field:' option in the Access Combo Box Wizard 264

10.42. The label dialog box in the Combo Box Wizard 265

10.43. A complete Combo Box form in Design View 265

10.44. A complete Combo Box form in Form View 266

10.45. The Controls Group in the Access Design tab 267

10.46. The ActiveX Controls button in the Controls Group, Access Design 268

10.47. The Microsoft Forms 2.0 SpinButton option in ActiveX Control 268

10.48. The SpinButton control dimensions 269

10.49. The text box Property Sheet Other tab 269

10.50. Options on the Other tab of the Property Sheet 270

10.51. The Built Event option 270

10.52. The Code Builder option in Choose Builder 270

10.53. The default Code Builder is an Updated event 271

10.54. Change event in the drop-down list 271

10.55. SetFocus on the SpinButton 271

10.56. The return to Access option 272

10.57. The SpinButton Control Source 272

10.58. The Control Source on the Property Sheet Data tab 273

10.59. The completed spin button 273

10.60. The Line or Rectangle tool 273

10.61. The line