Access 1A - Liberty University



[pic]

[pic]

Access 1A

Notes

Helpful Terms:

← Rows run left to right = Records

← Columns run top to bottom = Fields

← FIELD: single characteristic: (last name)

RECORD: Related set of fields

← TABLE: group of related records

← DATABASE: includes related tables, queries, forms, records

← QUERY: question about table information (unpaid accounts)

← FORM: Maintain, View, Print records

← REPORT: Formatted printout (either onscreen or hard copy.

← TABLE; Collection of Records

← Design View: This is the design of your database, no entries are visible here.

← Data View: This is where you can see the records

Quick Tips

← Only 1 database can be open at a time. (Unless you open multiple Access Programs)

← To resize columns: In the data view (this is where you can see the individual records) Highlight the columns you wish to resize using the black down arrow ((), gain the double headed arrow, double click.

← Change layout from Portrait to Landscape: File/Page Setup/Page/Orientation.

← The Wizards allow you to choose the fields you wish to include. If you use a special type of Wizard, it usually chooses all the fields. After using a wizard, you can change the look by going to design view and editing.

Access 1B

Notes

Quick tips:

When creating a Table:

← Description does not matter to the computer, this is user information only

← FieldNames: do not use spaces

← Data Type: Choose from pull-down list

← Use the “text” type for dates and other numbers not used in mathematical equations.

← For dates, numbers, etc. under format in the Field Properties there are choices you can make.

← For the unique field choose indexed: no duplicates to insure that no two entries are alike

← If you do not choose a primary key the computer will choose one for you or you can have a table without a primary key. The primary key is unique and is sorted by default A-Z.

← To import records from another table: the fields have to match in order and number. They must be alike.

← The sidewise arrow (() from the gray block on the side will choose your whole record row. Use this for deleting or adding rows

← To Change the LOOK of your document use the design view. To change the information in your records use the data view.

Access 2C

Notes

Quick Tips:

Table Relationships:

← One to many allows you to use tables with one like field to join the tables. Use the “relationships” button on the database toolbar.

← Usually the “enforce referential integrity” and the two boxes under it are checked.

← Once the relationship is established, you can use the multiple tables as though they were one table when doing queries and reports

Queries:

← Sorting one field can be done either in data or design view.

← Sorting two or more fields must be done in design view. Remember that the computer sorts in order from left to right. The field to be the first sort must be to the left of the sorted fields.

← To change the order, and keep all of the other information intact, use the down pointing arrow ((), highlight the column, click and hold and drag the field to the correct position and release the mouse button.

← In Design View, remove the check from the ‘Show’ box if the item is needed for the computer to complete the query correctly but you don’t want it visible on the printout or screen of the query.

← Filter by selection: click in the cell like the information that you desire to see. Click the funnel with the lighting bolt to filter the query. Click the funnel to remove the filter.

← Exact match: in design view, in the criteria row under the field you wish to match type the exact match you want queried out. ( 1/15/01 to see the records with that date)

← Comparison: And for two or more criteria that must be met (Same row). Or for either this or that (Different rows)

← Math functions are done from the design view, using the builder (magic wand) from the toolbar. This is done in a blank field. It is a wizard. Remember to end you comment such as “total” with a colon (: ) to separate the formula from the comment.

← Aggregate Functions: use the ( symbol from the tool bar in the design view. This adds another row to the fields. Choose from the drop down list. ( sum, ave, count, etc) Remember to use a : to separate your comment from the function.

Access 2D

Notes

Quick Tips:

← Form formats can be changed by using the AutoFormat under format in the design view

← Find: Under Edit, or the binoculars on the toolbar, allows you to find specific records easily. Make sure you have the correct field chosen, type in the information that you desire to see and “find next”

← The asterisk * is used as a wildcard character as a placeholder for information that you do not know (5* will give you all the numbers beginning with 5)

← Forms with subforms can be created using the design wizard, just remember to choose the secondary table or query before moving to the next step.

← Reports with subtitles are done in the same manner. Choose all table and/or queries before moving to the next step

← To insert any information in a report in design view you need to choose the type of information from the Tool Box. These are toggle items, either on or off, you do not need to drag them just click and move your mouse to where you want to put the information and click again.

Converting Access 2003 to a Previous Version

Access 2003 includes a feature that lets you convert a database to Access 97 or 2000. When you convert an Access 2003 Database to Access 97/2000 format, any functionality that is specific to Access 2003 is lost. However, that database’s structure and data will be usable in the converted database. To convert a database Open it in the Database window (normally), click Tools on the menu bar, point to Database Utilities, point to Convert Database, and then click database format desired. The Convert Database Into dialog box will open, in which you can specify a filename and location for the converted database. After you click the Save button, Access 2003 will convert the database and save it as an Access 97/2000 database, so that Access 97/2000 users will be able to open the database as if it were created in Access 97/2000.

If you try to open an Access 97 database in Access 2003, the Convert/Open Database dialog box will open and offer you two options: convert the database to Access 2000 format, or open the database in Access 97 format. When you are sharing a database with Access 97 users, you should select the “open” option so the database will be usable with both Access versions.

Access 3

Notes

Quick Tips:

← Lookup Field: Allows user to select a value from a list of values. (Example; ID # & name list so that the names show instead of only the ID number)

← Lookup wizard is found in the design view under the data type area.

← Input Mask: predefined format used to enter data in a field. (Often used for number fields)

← Pattern Match: selects records that have a value for the selected field matching the pattern of the simple condition value. Begins like” “

← List-of-Values Match: selects records whose value for the selected field matches one of two or more simple conditions values: In (“ “, ” “)

← Non-matching Value: selects records whose value for the selected field does not match the simple conditional value: Not in (“ “,” “)

← And/Or criteria sets two criteria. This and that or This or that.

← Parameter Query: prompts you for information when the query runs: [Enter the ]

← * is a wildcard character used when you are not using all of the record name ( * Smith would find all the people whose last name is Smith)

← ? represents any single character

← # represents any single number

Forms:

← Bound Control is part of the database

← Unbound Control is not part of the database: date, page number, label

← Calculated Control displays a calculated value from date from one or more fields.

← The Tool Box allows entry of data to the form

← Form Headers & Footers show only on the first and last page of the report

← Page Headers & footers show on all pages

← Filter sets the criteria that describes the records you want to see

← Filter By Form button pulls up an empty duplicate of the form for entrance of the type of date you wish to view.

← Filters may be saved as Queries. Use the Save feature on the toolbar.

Access 4

Notes

Reports

Quick Tips:

← If Function lets you assign one value to a calculated field or control if a condition is true and a second lets you assign one value if the condition is false.

← Change label: Click the label box to select it, click inside it once more to edit the text

← Resize: right-click; size;

← Alignment: right-click; align

← Several may be done at the same time by clicking, holding the shift key and clicking each one desired.

← Add Date: Tool box; text box; click unbound text box area; remove the word “unbound” type =Date() press enter

← Add Page Numbers: Insert: Page Numbers; choose the formatting & position & alignment click OK [The expression in the text box is: =”Page” & [Page] and means that the report will show the word page followed by the page number when the report is printed.]

← Format unbound boxes: Click text box and use the formatting toolbar at top of page. Tool bar will be active when ready to format data.

← Conditional Formatting for a control: Click control to select it; click Format on menu bar, Conditional Formatting, Select & enter conditions click OK

← Sort & Group: From design view choose the sorting & grouping button from the toolbar, choose the field you want to sort. If you want the field grouped choose yes for the group footer

← Group Totals & Overall Totals: In footer area place an unbound text box; in the data part type =Sum([InvoiceAmt]) then enter. In the field footer part it will give you the group total, in the report footer it will give you the overall total.

← Hide Duplicate: Right-click text box for the field whose duplicate values you want to hide. Click properties; Hide Duplicates; yes, close box.

Integrating:

← Importing: becomes an object in document

← Embedding: Source program maintains editing properties. Changes made only reflect where they are made

← Linking: Connection is preserved. Changes are reflected both directions

← Hyperlink: The hyperlinked document is opened where it resides

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download