Return to IFS 110 Notes!

Tutorial 3
Using Import Wizards, Advanced Queries, and SQL

 Getting Data from Other Resources

n     test data valuable while developing a project. 

n     “real” data not used during the development
(out of date by the time finished with project)

q     (i.e., the data will continue to be updated while they are working on this project). 

q     it is beneficial to work with data that is at least representative of the “real” data.

q     allows the developer to view the kinds of data that will be entered and will more easily identify potential problems.

Importing and Linking Data

n     Importing gets data from a source and places it in the database.

q     Importing does not maintain a link with the original data.

q     if the original data is updated, the updates will not be reflected in the imported data.

n     Linking – leaves data in current format and location, provides access to data (Access can link to dBASE, Paradox, FoxPro, SQL Server, and many other RDBMS database files.)

q     good alternative method if data used in both its original format and in Access

q     When Access used to link to another RDBMS (relational database management system), Access acts as a database front end.

Front and Back End

n     Front End - is the part of the database that the user will see.

q     The front end contains all the queries, forms, reports, macros, and modules that the user needs to access the data.

q     think of Front End as a window through which to view data.

n     Back End  - The data stored in the database tables 

q     Note: The concepts of Front End/Back End are usually applied when a database is complete and installed at the customer site (usually in a network environment).  

Using the Import Text Wizard

n      Import Text Wizard  - allows you to import data from a text file (flat file).

q     Text separated into records by placing carriage return and linefeed character combination at end of each row

q     Fixed-width text file – fields are fixed in size

q     Data is pulled from several different sources: spreadsheets, tables of data, other databases, etc.

q     Nearly any source of electronic data can produce a text file containing its data.

n      As the name implies, a text file contains text.

n      Text is simply a collection of characters
(ASCII in the case of a PC).

q     Because the data will need to be separated into the fields when it is imported, it is important that there is some character that serves as a delimiter (i.e., it signifies the end of a piece of data).

Delimiters

n    Delimiter The characters that identify the end of one field and the beginning of another field in a text file

n    Tab-delimited Text files in which the fields are separated by tabs

q    each record begins on a new line

Creating an Append Query

n      text manipulation functions included on page
not an exhaustive list but it does contain the most commonly used text manipulation functions.

q      function can also consist of a field name (or a variable name).

q      Example: the function InStr function can be used with a field name like this:  InStr(Name, “o”) .

n       In this case the InStr function will look for the letter “o” in the field Name.

n      Append Query is considered to be an Action Query. 

q      this type of query actually makes a change to the data.

q      differs from a select query which creates a temporary datasheet but does not alter data.

q      extreme care used so as not altering the data unintentionally.

n       It is sometimes quite difficult to recover from errors in an Action Query.

3 categories of Functions

1. Date and time

   •               Adding values to dates, subtracting one date from another, extracting parts of date

2. Text manipulation

   •               Trimming leading and trailing spaces, extracting part of string, counting number of characters

3. Data type conversion

•               Used to force particular data type

•                 Example: CInt – force text data to an integer

Importing an Access Object

n     Module An object in an Access database that is used to store VBA functions and procedures

q     import a module reasons

n      Many developers working on a project, and one of them may have created this module knowing that it would be required in the project. 

n      Another possible source is that modules are commonly imported to be used over and over again by different databases.

q     In this case, the imported module contains a function (already written by someone else) that will be used in the next section

ProperCase function

n      look up the vbProperCase function in the help files.

n      ProperCase = StrConv(strToChange, vbProperCase)

n      function returns its value through its name, string passed to the function (strToChange) is converted to proper case and then returned through the name of the function, ProperCase.

q     could send any string to the ProperCase function and it would be converted to proper case.

n      ProperCase Function Converted String strToChange (franklin) to Franklin

On Error statement will catch any errors that occur and resume processing at the next statement.

Creating an Update Query

n     backup table BEFORE creating and running any kind of Action Query.

q     The query might be written wrong causing undesirable results or the computer could get “hung up” in the processing of the query, etc.

n     update query - custom function is being used.

q     FirstName is being sent to the ProperCase function.

q     ProperCase function then converts the string to proper case

n      (first letter is upper case and all the rest are lower case).

q     When the function is complete, all the first and last names have been converted.

Using Import Spreadsheet Wizard

n    open the Movie3.xls file in excel.

q    help visualize the data (see figure 3-18 below)

n      determining what fields are needed for the tblTimeCards and tblHours tables.

q    Examine Spreadsheet data

n      Data arranged in rows, one type of data in each column

n      Column headings at top of row

q     Might want to remove spaces in column headings

n      None of cells imported should contain formulas

Query Properties (AC 105)

n    Output All Fields – show all fields in query’s underlying data source

n    Unique Values – include only unique results

n    Unique Records – return from underlying data source only unique records that are based on all the fields in the data source, not just the ones displayed in the query results

q    Property ignored when the query uses only one table

Query Properties (AC 105)

n     Source Database property – access external data that either is not or cannot be directly linked to your database

q     Source Connect Str property – specifies name of program used to create external file such as dBASE IV

n     Destination Table property – used for append and make-table queries

q     Destination DB property – name and path of database to which you are appending

q     Dest Connect Str property – specifies destination database type such as dBASE IV

n     Record Locks property – used in multi-user environment, locked so other uses cannot access records while action query is being run

3 Choices for Locking records

1. No Locks option – (default for select queries) records not locked while query is run

2. All Records option – allows users to read records but prevents editing, adding to, or deleting from underlying record source until query has finished running

3. Edited Record option – (default for action queries) locks a page of records as soon as query is run

q      Page Is portion of database file

q      Page is 4 KB in size in Access 2002

q      Depending on size of record a page may contain more than one record or a portion of a record

Archiving Data

n       Data in some tables grows rapidly.

q      examples of this, tblHours and tblTimeCards

q      To optimize performance previous year’s data stored in archived tables. 

n       archived data will be stored in the database or in a separate database.

q      Example: medical records, law requires records maintained for a certain number of years., likelihood of records being accessed is slim. archived data would be stored in a different database stored securely on some medium (such as table or CD) that will be protected from loss.

n      Differences between Make-table query and append-query.

n      make-table query - first empties the table (if the table already exists) and then fills it with new data.

n      append-query - add records to end of existing table.

Employing a Subquery

n     Subquery – is a query within a query

q     Subquery is a SQL statement inserted into query design grid as a field expression or as criteria for an existing field

n     Example: SQL code delete archived records in tblHours

3 Components of SQL

1. Data definition language – creation of database components such as tables and indexes

2. Data manipulation language – manipulation of database components using queries

3. Data control language – provide internal security for database

Versions of SQL

n     ANSI-92 SQL - current version of SQL

n     ANSI-89 SQL – used by Access 2002

q     Can set Access 2002 to be ANSI-92 compliant but it does not implement the complete ANSI standard (not fully compliant)

q     Access adds some features of its own to query language (uses built-in functions within SQL statements)

n     SQL-specific queries cannot be generated by query design grid; must be written in SQL view

n     Note: queries created using a query wizard or grid in Design view all comes down to SQL

3 Types of Queries

1. Data-definition Queries – creation of database components such as tables and indexes

2. Pass-Through Queries – passes a SQL statement through to an external database server (back-end SQL Server) without trying to interpret the statement

   q         Access acts as a front end

   q         Benefit – retrieve only wanted fields and records

   n        2 GB limit, query recordsets limited to 1GB, ODBC connection time out

3.  Union Queries – creates a union of two or more tables

q         Cannot be created in Query Design view

Access SQL Statement, Commands, and Clauses

n    SELECT command SQL command used to specify the fields to be retrieved from a table whose data is being returned to a recordset

n    DISTINCT keyword Access eliminates duplicate rows, based on the fields included in the query results

n    DISTINCTROW keyword Access eliminates duplicate rows, based on all fields of all tables included in the query  

Access SQL Statement, Commands, and Clauses

n    FROM clause - specifies the table or query from which the SELECT command takes its records

n    WHERE clause - used to limit the records that are retrieved by the SELECT

n    ORDER BY clause - clause determines the order in which returned records are sorted

Different Types of Joins

n     JOIN clause - clause varies depending on whether you’re using an inner join, left outer join, or right outer join.

n     Left outer join  - includes all records from the primary table and only the records from the related table where the joined fields from both tables are equal

n     Right outer join  - includes all records from the related table and only the records from the primary table where the joined fields from both tables are equal

Access SQL Statement, Commands, and Clauses

n     GROUP BY clause - modifies the results of a SELECT command by organizing the query’s recordset according to the fields named in the GROUP BY clause.

n     HAVING clause - used in conjunction with a GROUP BY clause whenever you want to add a search condition to the field(s) specified by the GROUP BY clause.  

n    Union statement The UNION statement is used to create the union of two or more tables.

q    UNION ALL operator forces Access to include all records, which would include duplicate rows

n      Improves performance because query runs faster

n      Omit the word ALL to avoid potential duplicate rows

n    Val function - converts text data type into a numeric data type.

IFS 110 Computer Assignment 3

Return to IFS 110 Notes!