Microsoft Access 4A: Review: Tables, Queries, Forms, Lookups, Subdatasheets, Reports

Tables:

• tables are the starting point for all data to be stored
• tables are named with a tbl in front of each name
• tables contain fields:
• fields may be any of the following type:
  • Text (names and or numbers not used in calculations)
  • Memo (allows you to key in a little note, example: a paragraph describing what happens in a movie)
  • Number (allows use of calculations-learned in future lessons)
  • Date/time (various formats)
  • Currency (rounds to two decimals)
  • Autonumber (sequential numbers, 1,2,3,4...)
  • Yes/no (allows user to select a check box)
  • OLE object (insert a picture-can only view in form)
  • Hyperlink (Internet address, when clicked on will open browser and go to site)
  • Lookup Wizard (a drop list appears to click on info. that is directly entered or comes from another table or query)

• Most of the above types also can have the following:
  • Format: Currency type: (dollars or euros etc.), Date type (19-Jun-04 versus 6/19/2004 etc.), Number type: (3 decimals, 4 decimals etc.)
  • Default Value: this is what will be put in automatically unless changed by the user
  • Validation Rule: only allows certain values (eg. years less than 2008, or a number less than 100 etc.)
  • Validation Text: this is a message that appears if a user tries to enter in a value that doesn't agree with the Validation Rule (above)
  • Required: if yes is selected a user can not leave this entry blank
  • Note there are other ones as well.

Forms:

• Forms are a "nicer" way of looking at records
• Forms are named with a frm in front of each name
• Forms show only one record at a time
• Pictures stored in tables can only be viewed in forms
• Forms may also have titles, logos or other modifications added to them
• You can "lock" certain fields so that a user can look at records without accidentally making changes
• Forms may also have subforms which have data from a related table (relational database) as shown below:Insert image of subdatasheet
• You can change where a form gets the information from after it has been created (this is useful when you need a form with a lot of changes like logos, colors, and other design features based on several different tables or queries)

Queries:

• Queries can display only certain fields in a table
• Queries are named with a qry in front of each name
• Queries can display the records of any field in ascending or descending order
• Queries can have parameters such as [Enter LastName] which will only display records that match what the user enters
• Queries can have criteria which restricts which records in a table are displayed (eg. Smith* would list all the Smith, Smithers and Smithson records)
• Queries can display records from more than one table

  Example: perhaps you have student numbers and book numbers in one table to record all the books signed out, a query can be built with more than one table thus allowing you to see all the student info. from a another table and all the book info. from another table

• Queries can be based on other queries (instead of tables) as well

Example: perhaps you have a query which lists only grade 12 students, you could make another query based on this query which lists only the females students in the query

• Queries can do "actions" like updating, deleting and moving data from one table to another...(learned in later lessons)

Reports:

• Reports are a "nicer" way of looking at the information in tables or queries (usually printed out)
• Reports are named with a rpt in front of each name
• Reports can "group" data in to sections
• Grouped sections can have their own headings and include summary information
  • Example: A list of students shows all the classes a student is registered in under the heading of each student's name, then at the bottom of each student section the total number of classes is calculated (e.g. student is taking 4 classes)
• Reports can be sorted using the report wizard or using the sorting/grouping feature instead of relying on the query

Pages:

• Pages are like forms (except they can be viewed in a web browser)
• By creating a page you can display information on the Internet and even have users enter information from their Internet browser
• To do this requires knowledge of how to set "access rights" on a network server

Macros:

• Macros are used to have the program do things automatically, however Macros are generally not used to do anything except automatically open a form when the user opens Microsoft Access

Modules:

• Whatever you can dream of doing in Microsoft Access but can't accomplish using tables/forms/queries/reports etc. you can probably do with a Module
• A module allows you to write computer programming code into the program to do very complex tasks and interact with other programs

This website and its content is copyright of FreeOfficeTutorials.com © FreeOfficeTutorials.com 2011. All rights reserved. Any redistribution or reproduction of part or all of the contents in any form is strictly prohibited.