Database Workshop

 Data - Information stored by the computer. Your most valuable commodity and requiring organisation and security. A database can be made available to other programs to use as a source, either by exporting it or by reading it directly through a tool such as ODBC.
  • Field Labels
  • Importing & exporting Databases
  • ODBC
  • Peforming an ODBC Query from Excel
  • Filtering a Query (Setting Criteria)
  • Analysing Queried Data in a Pivot Table
  • Analysing Queried Data in a Filter
  • Create a Proft & Loss Analysis

    Field Labels
    Ensure the fields in your data tables are meaningful. This is particularly significant if you may in the future use the ODBC facility to call up data from another application. Don't allow generalisations such as 'Address1', 'Address2' etc. Instead, use 'Streetnumbername', 'district' etc. These will give you more chance of filtering and sorting fields for reports and queries.

    Back to Top

    Exporting/Importing Databases
    Most Databases have an import/ export function (Usually in the File menu). This allows data to be brought in from outside and extracted to use in other programs.
    Although there are several types of file to use, a Comma Separated Values (.csv) file is useful. It separates each 'segment' of data by a comma, with a line break being the start of a new record and the first line being the column headings.
    This is fine for simple databases, without commas in their entries, but sometimes another type of 'delimited' file is called for. You can separate fields by TABS or by enclosing them in quotation marks.
    If Excel were to import a .csv file into a blank worksheet, each separate field would go in a new cell left to right until a new line, when the next row is started.
    Try exporting a simple database as a csv file or similar and view it in NotePad or WordPad and see how logically it is structured.

    Back to Top

    ODBC
    ODBC stands for Open Data Base Connectivity. It is a mechanism to allow data to be transferred between one application and another. Basically it means that Access or Excel can 'query' a compatible (e.g. Sage) database and extract relevant data for use in analyses and reports. All MS Office applications and some third party applications are ODBC capable but users of other applications such as Lotus 123 will find it limited but possible.
    So, a Sage or Access database can provide source data for Excel (e.g. for a mail merge or label printing). This is done for these programs using Microsoft Query - one of Office's utilities. Other applications may well have ODBC querying software.
    Your host system needs a relevant ODBC driver (e.g. Sage) and MS Query 16Bit or 32Bit (faster) installed. See the Control Panel to see if ODBC and the relevant driver is installed.
    Foxpro, DBASE, Paradox, Excel, Lotus 1-2-3 and Sage Line50/100 are all programs and databases that support the ODBC protocol in their more recent versions. The latest ODBC drivers and support information should be freely available on the respective manufacturer's web site.
    What you need to remember is that a database simply stores data in predefined fields (columns), with one record for each instance (rows) all in table format. The ODBC takes this common string and allows you to access any information stored thus.
    Back to Top

    Peforming an ODBC Query from Excel
    1. Open an Excel sheet.
    2. Go to Data - Get External Data - Create New Query
    3. Chooe Data Source (must have been previously set up in Start-Settings-Control Panel-ODBC first so it is visible)
    4. Enter Login information (User Name and Password)
    5. MS Query opens. A list of all visible tables in the Data Source should appear
    6. Choose relevant table(s) from list. (N.B. In Sage, look for recogniseable names such as 'Purchase Ledger' and ignore others that are meaningless, they are simply part of the database structure.)
    7. If querying multiple tables, in Sage for instance, you must link the field 'This Record' to 'Parent Record' (One to Many relationship). This tells Query which is the Master File and which is the related record file where there may be many entries for a field. Every record should have a field that relates it to a parent table if multiple tables are to be shown.
    8. Choose Fields from tables in the top pane by clicking on them. They appear in the bottom pane as column headings
    9. Use Query Now (!) button to see the query data appear
    10. Edit Field columns and remember to Update the query (!)
    11. If ready, choose File-Return Data to Excel or if you want to filter data beforehand, see next section

    Back to Top

    Filtering a Query (Setting Criteria)
    1. In MS Query view, click on a Column and Select Criteria-Add Criteria
    2. Specify parameters of selection for that particuar field. The Help file is useful if you get stuck
    3. Essentially you are just specifying what specific entries you want to view in the query, rather than seeing every record
    4. Edit a Query by selecting an Operator (equals, less than, is between etc) and Quotes around a string to search for e.g. "CH64" or " " (blank string).
    5. When finished Click File - Return Data to Excel

    Back to Top

    Analysing Queried Data in a Pivot Table
    Once your query data has been sent to Excel, you could use a Pivot Table for analysing it.
    Example Pivot Table(13k) - Click on the link to Download an example Excel97 spreadsheet showing simple use of a Pivot Table to summarise data.
      To create a Pivot Table:
    1. Highlight the data you wish to analyse
    2. Choose Data - Pivot Table Report
    3. Follow the wizard
    4. Go back to the Pivot Table Wizard and choose Layout.
    5. Simply drag fields onto Pivot Table (Layout Menu). Remember the numerical data must be placed in DATA area.
    6. Click on the field name in the DATA area to secify SUM, COUNT etc.
    7. It takes a few goes to get used to but it's well worth it
    8. You should end up with a table wherein you can use drop down menus to turn certain fields on & off easily
    9. Hint: Try putting a main field in the PAGE area on the Layout Menu and see what happens!
    10. N.B. If data underlying a Pivot Table changes, then you need to Right-Click the Pivot Table and refresh it (!).

    Back to Top

    Analysing Queried Data in a Filter Table
    This is easy! SImply highlight the whole table of returned data and choose Data-Auto Filter.
    Each column heading should now have a dropdown box that lets you choose which record(s) you want to show.

    Back to Top

    Create a Proft & Loss Analysis
      On a new sheet in same workbook you already have the data in a Pivot Table:
    1. Create an Income Column with income fields in rows
    2. Create a 2nd column with (e.g) costcode identifiers (Hint, if codes in the returned data start with zeros (i.e. 0001), combining the code column with a new column with an ampersand creates a new category reference e.g. P0001). Then refresh your Pivot Table.
    3. Use VLOOKUP function to create a third column that returns the balance of a costcode reference by looking up the pivot table column of codes and returns the corresponding balance.
      Example Lookup Table(16k) - Click on the link to Download an example Excel97 spreadsheet showing simple use of the VLOOKUP function.

    Back to Top

    Hints on Editing Pivots & Queries
    You need to refresh Data once it has been changed in the Query and in the Pivot Table. On the relevant toolbar it is a ! symbol.
    You might like to ensure a Pivot Table covers unused cells that may be filled with records in the future because of the query: Right-Click a cell in the Query and Choose DataBase Properties. Make sure the bottom box is ticked. This means that if the number of rows of data being queried increases, then it will be taken into account.
    If you want to create a second Pivot Table based on the same data, you can take Excel's advice and base your new Pivot Table on the First Pivot Table.

    Back to Top

    Excel Overview Workshop
    Excel Detailled Workshop

    KnowledgeBase Index