1 / 16

Access Lookup Tables: Data Validation and Integrity

Learn how to use lookup tables for data validation, enforce predefined formats with input masks, and create custom forms in Microsoft Access. Explore advanced queries and form filtering techniques.

rdrake
Download Presentation

Access Lookup Tables: Data Validation and Integrity

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Access Chapter 5-Table Tricks, Advanced Queries and Custom Forms

  2. Lookup Tables • Use to limit user input into a table • Data validation • Data integrity • Wizard shields you from relationship complexity, Key columns • “Lookup table” must have a defined relationship with the main table • Can use SQL statement as data source PgP MIS 342 Access

  3. Lookup Tables • Examine Wizard results by viewing ‘field properties’ on the Lookup tab PgP MIS 342 Access

  4. Northwind.mdb Orders Table Properties Examine the properties PgP MIS 342 Access

  5. Table Input Masks • Enforces predefined format • Study input mask characters • Experiment with the Wizard PgP MIS 342 Access

  6. Table Input Masks • Note literal display characters can be stored in database. You may not think this is necessary, but it is wise to store the characters if you plan on exporting the table to a text or other external file. • Consider when interfacing with other systems. • XML, EDI, other databases PgP MIS 342 Access

  7. Table Input Mask Exercise • Using blank Access database file, • Create new table, with two fields • PhoneNumberWithMaskCharacters • PhoneNumberWithoutMaskCharacters • Use Input Mask Wizard to setup fields • Enter data into both fields of a record • Export table to Excel file • Examine results PgP MIS 342 Access

  8. Table Input Mask Exercise • PhoneNumberWithMaskCharacters • !\(999") "000\-0000;0;_ • PhoneNumberWithoutMaskCharacters • !\(999") "000\-0000;;_ • Sections separated by ‘;’ • What does each section mean? PgP MIS 342 Access

  9. Table Input Mask Exercise • Section 1-The Mask • 19 possible characters, search Help for InputMask Property • Section 2-Store which characters? • 0- store all characters • 1 or blank- store only typed characters • Section 3- Placeholder character PgP MIS 342 Access

  10. Query-Pattern Match • Query criteria, Like combined with wildcard characters-*,?,# PgP MIS 342 Access

  11. Parameter Query • Specify criteria at run time • Access provides the Prompt box,… • End user supplies the parameter • Offers a degree of flexibility • But-user needs to know value to enter! PgP MIS 342 Access

  12. Custom Forms • Start from scratch, • Modify existing form or… • Begin with the Form Wizard and modify the results… • Modifications are done in Design View PgP MIS 342 Access

  13. Custom Forms-Items Needed • Learn the available objects • Text Box, Command Buttons… • Refer to the Field List • Realize different objects have different properties • Learn how to select, move and position controls PgP MIS 342 Access

  14. Custom Forms-Labels vs Text Box • Label- displays static text • Text Box- the most basic “data bound” control. Used to display data from tables, queries, calculated fields or expressions PgP MIS 342 Access

  15. Custom Forms-Tabs • Efficient use of space • Requires good logical grouping of fields to be effective • The more familiar you are with positioning, resizing and aligning of controls, the easier this will be PgP MIS 342 Access

  16. Form Filtering • Use to target certain field values • Filters that you use often can be saved as a query • Can also apply queries as filters • Filter by Form • Apply query as filter PgP MIS 342 Access

More Related