Using The EMWAC database project Demonstrator ============================================= These notes provide guidance for using the EMWAC database project demonstrator application. It assumes that the database application has been successfully installed and other communications prerequisites are in place and that you are able to successfully attach a table stored on a remote server. Overview: ======== The application operates by allowing the user to create a query comprising selected columns from one or more statistical tables. Executing this query will return the required data as an access table or datasheet. The number of rows returned may be restricted by defining a "Filter". A Filter identifies the postcode sectors or output areas of the required rows. Queries and or filters may be stored for retrieval later. Sample session ============== The following is a typical user session: 1. Open the application -------------------- The demonstrator introduction screen will be displayed; you can scroll through the text which describes parts of the project. Click on "Continue" to enter the application and select a dataset to process. Click on "Exit" to go to the database window to examine the application. 2. Selecting a Dataset ------------------- The screen "EMWAC Demonstrator Data Sets" will display a list of the datasets available. Select a dataset either by double-clicking on it or by selecting it and then clicking on the button "Select Data Sets". This will display a list of the statistical tables available in the dataset. Notes: i. Access only version: All the data is dumy data; no passwords are required for any of the data sets. ii. This screen is always displayed on the first entry to the application if the application cannot locate the relevant control tables. Second or subsequent entries to the system, provided the correct tables are still attached, will invite you to "Continue using the previously selected data set". Answering "No" to this will display the screen "EMWAC Demonstrator Data Sets". 3. Select Statistical Tables ------------------------- The screen "EMWAC Select Statistical Tables" will display a list of the SAS tables available. Select a table either by double-clicking on it or by selecting it and then clicking on the button "Select". This will display a list of the columns available in the table. Notes: i. The "EMWAC Select Statistical Tables" screen displays an abbreviated table title; The full title can be viewed by clicking on "Zoom". The Zoom button opens the screen "EMWAC Show Table Information" screen. You can browse back and forward through the tables using this screen. ii. Closing the screen "EMWAC Show Table Information" leaves the currently displayed table as the "current table"; clicking on the "Current" button reselects the table that was current when the zoom button was pressed. 4. Select Columns -------------- The screen "EMWAC Select Columns" will display a list of the columns in the currently selected table. Add a column to the current query either by double-clicking on it or by selecting it and choosing "Add" in the "Columns" menu. query. Notes: i. You can drop a column from the current query by selecting the column and choosing "Drop" in the "Columns" menu ii. The "EMWAC Select Columns" screen displays an abbreviated column header and stub; The full header and stub of the column can be viewed by clicking on "Zoom". The Zoom button opens the screen "EMWAC Show Column Information" screen. You can browse back and forward through the columns of the current table using this screen. The "Add" button adds the column to the current query. iii. Closing the screen "EMWAC Show Column Information" leaves the currently displayed column as the "current column"; clicking on the "Current" button reselects the column that was current when the zoom button was pressed. 5. Adding a column to a query -------------------------- When a column is added to a query the screen "EMWAC: Column Added to Query" confirms this. The default name for this column is made up of the original table and column names. You may change this if you wish. The columns name specified on this screen appears as the name of the column when the query is executed. Click on OK to return to select the next column. Notes; i. The selected column is added to the current query ii. Repeat steps (3) through (5) to construct a query. iii. "Show Tables" in the "Tables" menu displays the list of tables in the currently selected data set (see step 3). 6. Viewing the current query ------------------------- "Show" in the "Queries" menu will display the current query. The screen name is "EMWAC: Show current Query" 7. Executing the current Query --------------------------- "Execute" in the "Queries" menu will display the screen "EMWAC: Do Query". Click on "Go" to execute the query. The result is displayed as a data sheet; you are given the option of automatically resizing the columns of the displayed query. Notes: i. The check boxes "Identifier", "County", "District", and "Postcode sector" respectively add the Identifier, County, District, and Postcode sector to the retrieved query. ii. The "Filter" checkbox causes a filter, if defined, to be applied to the retrieval. 8. Saving the results of a query as a local table ---------------------------------------------- Having constructed a current query and checked filter and other check boxes as appropriate (as in step 7 above), choose "Save Data" in the "Queries" menu. The system will prompt for a table name to store the result in. 9. Saving and restoring Queries ---------------------------- "Save Query", "Restore Query" and "Drop Query" in the "Queries" menu display the screen "EMWAC: Query Maintenance". This screen displays the names of the saved queries. A saved query can be selected by clicking on its name. The buttons are as follows: Zoom: Display the currently selected stored query in more detail. This will not affect any current query defined in working storage. Save: Save the current query. You will be prompted for a name to save the query as. The current query remains available in working storage. Drop: Drop (i.e delete) the currently selected stored query. The current query remains available in working storage. Load: Load the currently selected stored query into working storage. This will replace any "current" query residing in working storage. Close: Close the "EMWAC: Query maintenance" form 10. Defining a new Query -------------------- "New" in the "Queries" menu will start a new Query; any temporary query currently in working storage will be deleted. 11. Defining a Filter ----------------- "New" in the "Filters" menu will start a new filter; any temporary filter currently in working storage will be deleted. "County/Region", "District", and "Postcode" in the "Filters" menu allow the definition of a county/region, district, or postcode sector level filter. Each of the options work in the same way. When selected the screen "EMWAC: xxxxx Filter" screen is displayed. Just select the entities to be added to or deleted from the filter (duplicated entries will be ignored when the filter is applied during query execution). Note: i. During query execution (step 5 above) a filter will only be used if the "Use Filter" checkbox is checked. 12 Saving and restoring Filters ---------------------------- "Save Filter", "Restore Filter" and "Drop Filter" in the "Filters" menu display the screen "EMWAC: Filter Maintenance". This screen is very similar to the corresponding screen for queries; it displays the names of the saved filters. A saved filter can be selected by clicking on its name. The buttons are as follows: Zoom: Display the currently selected stored filter in more detail. This will not affect any current filter defined in working storage. Save: Save the current filter. You will be prompted for a name to save the filter as. The current filter remains available in working storage. Drop: Drop (i.e delete) the currently selected stored filter. The current filter remains available in working storage Load: Load the currently selected stored filter into working storage. This will replace any "current" filter residing in working storage. Close: Close the "EMWAC: Filter maintenance" form. 13. Selecting a new Dataset ----------------------- "New" in the "Datasets" menu selects a new data set. Any current filter or query is destroyed. The screen "EMWAC Demonstrator Data Sets", described in step 2 above will be displayed. 14. "Exit" menu options ------------------- "Exit" Exit from Access, leaving attached tables attached. "Exit + Tidy Tables" Exit from Access but delete any attached tables. "Close Form" Close the current form. If this is the only application form open, you are asked if you want to exit the application. "Close All" Close all currently open forms; exits the application. "Access" Display the Access database window. "Hide Access" Hide the Access database window. "Show Toolbar" Show the Access Toolbar. "Default User Name" Define the default user name to be used when working with password-protected live census data. 15. Starting the application from the database window ------------------------------------------------- Execute the macro "Autoexec" to restart the application from the database window. 16. Opening the database without entering the application ----------------------------------------------------- Hold the shift key down when opening the application.