&RPSXWHU 7UDLQLQJ 6XSSRUW 6HUYLFHV 1HWZRUNLQJ6HUYLFHV $FDGHPLF&RPSXWLQJ :HEHU%XLOGLQJ
Using Delphi Data with Excel and Access Using Delphi Data
The raw data used to create the CSU financial, human resource, and student reports are available for you to create your own custom reports, queries, graphs, etc. This data is available through Delphi, which is simply a database system that stores all administrative data.
All you need to access Delphi is a computer that has network access; a Delphi login and
password; ODBC connectivity on your computer; and software that will allow you to link to that data from your own personal computer.
Delphi Application
A Delphi login and password are required to access the data in the Delphi data warehouse. Only one Delphi login is required to access all the systems. However, an application must be completed for each distinct system. Authorization to the Delphi data will be granted after the application has been signed by the user, department head, data custodian (HRS, FRS, CIS, ISIS, etc.) and the Delphi administrator.
How do I know if I have access to Delphi tables?
If you are not sure whether you have access to Delphi tables or if you don’t know which Delphi tables you have access to, contact Pat Spooner, Information Systems Department at 1-1587.
How do I get access to Delphi tables from the HRS, ISIS, CIS and FRS systems if I already have a Delphi login?
If you have a Delphi login and would like access to any or all of the Delphi tables listed above, print a Delphi application for each system and complete it. Be sure to list your current Delphi login on all applications.
If you have a Delphi login and want to add CIS and/or FRS Delphi tables be sure to select the Delphi box on the CIS/FRS application.
If you have a Delphi login and want to add ISIS or HRS Delphi tables complete the Delphi application for each system.
The web address for each application is listed below.
Business and Financial Services
FRS - Financial Reporting Systems CIS - Campus Information Systems Contact: John Hunter, 1-1956
Human Resources Services
HRS - Human Resource System Contact: Rebecca Payne, 1-0948
Application available at: http://www.colostate.edu/Depts/IS/forms/hrsauth.pdf Enrollment Services, Systems Support
ISIS - Integrated Student Information Systems Contact: Jerry Greenwell, 1-6123
Applications available at: http://www.colostate.edu/Depts/SystemSupport/syssup.htm
Once the Delphi application has been approved, the Delphi user will be able to access specific Delphi tables. The HRS and ISIS application list the actual table names the user can access.
Delphi Warehouse Data Dictionary
Visit the Delphi Warehouse Data Dictionary web site, which is maintained by the Department of Information Systems, for a list of table names available for each application. Click on the application (FRS, HRS, etc.) to view a list of available tables. Each table will have a brief description as to what it contains. Click on the actual table name to get a list of all the fields in the table and a brief description of the fields.
http://delphi.is.colostate.edu:8889/owa_is/owa/dictf001$.startup Delphi Warehouse Table Name Cross-Reference
In an on-going process, Information Systems is currently updating and changing table names from the old eight character table names to a more descriptive table name. Visit the
Information Systems Delphi Warehouse Table Name cross-reference web site for a listing of old and new table names.
http://delphi.is.colostate.edu:8889/owa_is/owa/dictw003 Getting your machine set up for Delphi
Before you can access Delphi data using Access or Excel, your system administrator must make sure that you have the drivers necessary to connect to Delphi. The drivers you need will provide ODBC (Open Database Connectivity) so you may transfer data from Delphi into other
applications such as Excel or Access. Instructions are available on line through the Information Systems web page or at
http://www.colostate.edu/Depts/IS/lanman/cisw32.pdf Delphi Mailing List
computer system. You can even link Delphi data to any related data you may have in a another spreadsheet or database.
Using Delphi Data (or any external data) with Access
Access provides two choices for using data from external sources -- you can either import the data or create a link to it.
Import vs. Link
If you need to leave your data in another database or data format but wish to use Access tools to manipulate the data, you should link the external data to an Access database. Typically this is how you would handle Delphi data. When you create a link to Delphi, the data remains in Delphi, but you are able to use Access filters, sorts, queries, forms, and reports with the data. Additionally, you can join data you obtain from Delphi to other related data which resides in an Access database.
Note: when using Delphi data, you will not be able to update the data from within Access. You must go through the normal systems, e.g., FRS, HRS, to actually change the data.
If you wish to make the data a permanent part of your Access database, then you should import the data. Normally, this would be done when converting from one database to another, such as from Paradox to Access.
You can import or link tables from the following applications or formats:
Paradox, FoxPro, dBASE III and IV; Btrieve; SQL Databases (this includes Delphi data); other Access databases, Excel spreadsheets, and text files.
Linking Data from Delphi
Once you have decided whether to import or link the data, you can place the data into a new or existing database. Remember that an existing database can contain Access tables as well as linked tables.
Creating a Link
1) Open an Access database;
2) From the File menu, choose Get External Data, then Link Tables;
3) A Link dialog box will appear. From that box you must choose the proper format for the tables to be linked. In the case of Delphi you would choose ODBC Databases from the Files of type drop down list. [See Figure below.]
5) This opens the Select Data Source box. Select the Machine Data Source tab (as shown below) and choose Delphi from that list. Be aware that this list is created by your system administrator through the setup of the ODBC driver on your machine. If you fail to see Delphi listed here or an error message is displayed when you click on it, then you should check with the system administrator for your machine.
6) You will now be prompted for your Delphi login and password to logon to Oracle (the type of database where the Delphi data resides).
Refreshing the Data in Access
Once you have created a link, the tables will remain linked and you will not need to repeat the above steps again, unless you choose to link to a different table. Each time you open the database containing the linked tables, the tables are refreshed as you open them.
Managing the Link
If the name of a Delphi table changes, then you will need to update your link. This menu item can also be used to view or refresh links when the structure or location of a linked table has changed. The Linked Table Manager lists the paths to all currently linked tables. From the Tools menu, choose Add-ins, then click Linked Table Manager.
Importing Data
The steps for importing data are essentially the same as for linking. Choose File, Get External
Data, then choose Import. You still need to select the correct format from the Files of type list.
Once you import data, the data then permanently resides in Access. This means that the data will not be refreshed but remains the same as when it was imported, except for changes made to the tables in Access.
Database Maintenance
Following are some database features that you may wish to use once you have linked to Delphi tables. For example, you can copy linked tables to other databases, rename tables, and delete them when no longer needed.
Copying Database Objects
To copy any database object to another database (or to another Windows application), click once on the object name (in the database window) and choose Edit, Copy. Open the receiving
database (or application) and choose Edit, Paste. Renaming Objects or Databases
To rename an object, right click on the object name and choose Rename from the menu. Enter in the new name of the object.
To rename a database, use My Computer or the Windows Explorer to change the name of the database file. Note: the database file must be closed before you may rename it.
Deleting Objects or Databases
To delete any object in a database, click on the name of the object and press the Delete key on the keyboard. To delete a database, use My Computer or Windows Explorer and delete the database file name.
Queries in Access
Action queries can be useful particularly in conjunction with external data. Below are the four kinds of action queries:
1) Make-table query -- makes a new table from all or part of another table; 2) Delete query -- deletes records according to your criteria from a table; 3) Append query -- adds a group of records from one or more tables to another table;
4) Update query -- changes the data in a group of records.
To view the data before applying the action, click on the datasheet view first before clicking on the “Run Query” button. This way you have an opportunity to preview the records that are going to be effected by your query.
Joining Delphi Tables to Access Tables
You can join linked Delphi tables to Access tables that contain related data if there is a common field that resides in both tables. For example, if you have your own student data and wish to link to student data from Delphi, you could do so if your student identifier is of the same format and type as the student identification number used in Delphi.
To join tables together, create a new query using the Query Design View as follows: 1) From the query grid, create a relationship between the two tables;
2) Drag the related field from one table (by holding down the mouse button while you move the mouse) until the mouse pointer is over the related field in the second table. Release the mouse button and a join line should be displayed between the two related fields;
3) For the join to work, the two fields must contain matching data in related records and the related fields must be present in both tables.
Printing
Among the items you can print are: datasheets, forms, reports, and definitions of objects. To print a datasheet, form, or report -- open the object and from the File menu choose Print. To print selected records in a form or datasheet -- from that view, select the records you want to print. From the File menu, choose Print (or click the Print button on the toolbar). In the Print Range group, choose Selection.
Note: you can easily transfer data between Access and Excel if you change your mind after linking the data, or if you wish to use both applications for different aspects of managing your data.
Retrieving External Data in Excel
In Excel, you may choose to retrieve the external data into a new spreadsheet or append it to an existing sheet.
1) Open an Excel sheet and position the cursor (active cell) where you want to place the data;
2) From the Data menu, choose Get External Data. You will see a submenu displayed. If you have not previously linked to data, or have not saved a query to retrieve the data, then choose
Create New Query.
3) A dialog box will open from where you can Choose the Data Source;
4) Unless you have previously created and saved a query to use to retrieve the data, you should click on the Databases tab and choose Delphi from the list. Click on OK.
5) You will now be asked to log onto Oracle. Enter your user id and password.
6) If you have checked Use the Query Wizard to create/edit queries in the Choose Data Source dialog box (see above), then the wizard will appear to help you create a query. Note: If you do not choose to use the wizard, then you will need to be familiar with Microsoft Query (somewhat different than query in Access) and create your own query to retrieve the tables and fields you want.
7) The Query Wizard allows you to choose the table and fields you wish to bring into Excel. You can also preview the data in any field by selecting the field name from the table and clicking on the Preview Now button.
8) Once you have selected the table and fields you wish to use, you can click on Next which will take you to the Filtering screen where you may apply criteria to your query. For
example, if you are retrieving student information, you may want to see only students from a specific college with a specific major. Filtering allows you to bring in only the records you need. Note: In Access you must first link to the table, then run a filter or query after the table
is linked.
Using an Excel Template to Automatically Retrieve External Data
If you want to automatically run a query and retrieve the most current data from the external source then you can create a report template. A report template can contain an external data range or PivotTable. If the appropriate ODBC driver is installed, Microsoft Excel will
automatically refresh the external data range each time the report template is opened. Microsoft Excel also updates all the formulas and charts when it refreshes the data. When you save the report template, the external data range is removed.
To create a template that automatically retrieves external data when you open it:
1) Retrieve external data (as described above), creating a data range on your worksheet (this method also works with Pivot Tables);
2) Format the workbook the way you want;
3) Choose File, Save. In File name box, type the name you want;
4) In the Save as type box, click Template (*.xlt) and select the drive and folder where you wish to store the template. Click Save.
5) When asked if you wish to remove the external data range from the workbook and automatically refresh it the next time the report template is opened, click Yes.
Note: You can check the status of a query that’s running in the background and taking a long time to return the data. While the query is executing, click Refresh Status on the External Data toolbar. To cancel the query, click Stop Refresh.
Saving a Query
Installing the Microsoft Query Wizard
If you chose the Typical installation option during Setup, Microsoft Query is not installed on your computer. If the query wizard does not appear when you try to create a new query, then MS Query, which includes the query wizard, needs to be installed. For instructions on how to install Microsoft Query, search Excel’s Help menu for Installing Microsoft Query.
Excel Tools
Filtering Data
If you wish to apply a filter to an entire list, select a single cell within that list. To apply the filter only to selected columns, first select the labels for those columns. From the menu select: Data,
Filter, AutoFilter.
Excel places drop-down arrows on each column label. Click on the arrow to display a list of items. Selecting an item will display only those rows containing the item selected.
Removing a Filter
To remove a filter from a single column, select All from that column’s list. To completely remove a filter, choose Data, Filter and then select AutoFilter to clear the command. Custom Criteria
If you wish to filter items that meet certain conditions (e.g., all persons whose salaries are greater than 25000 and less than 40000) select Custom from the filter list.
Filtered lists may be printed, formatted, edited, charted or sorted.
Pivot Tables
The PivotTable allows you to interactively change the arrangement of your list. You can create a customized report from your data by specifying the items to be displayed and then changing your view of that data.
