Jan's Working with Databases

Exercise Access 3-3: Scouts

---

What you will do:

Import data and objects from external sources in different formats Create relationships Correct imported queries Export results of a query

---

[You will use the database created here in the exercises for Forms and Reports also.]

Some years ago when I was the Service Unit Manager (head local volunteer), I created a database for managing the Girl Scout troops and leaders in our service unit.  I have reduced the number of records and objects and have somewhat normalized the database for use here. I have learned more over the years about how this should have been done to start with!

You will import data in various formats to create the tables, so you won't have to enter many records. (Hurrah!) I actually took my working copy of the database and exported the objects that you will import. The process of exporting changed some of them! You will see what kinds of difficulties that creates! It is fairly common to share tables with others or to purchase names/addresses list in various formats. You need to know what kinds of issues that can raise.

Disclaimer:
All of the names, phone numbers, and addresses are fictional and any correspondence to actual persons is coincidental. Girl Scouts of the USA has not approved this database in any way.

Start with:   Access open with no databases open, resource files

Importing:

Note: Be careful that you choose the correct file type in the Open dialog or you won't see the files you are looking for.

1. Create a new blank database and name it ex3-3-scouts-Lastname-Firstname.accdb , using your own first and last names.
   

2. Import all of the tables and queries in the database gscout-for import.accdb which should be in your resource files .
   There are 2 tables and 14 queries. Some of the queries use tables that you must import separately.

   If you have not downloaded the whole set of resource files, you can find this file and others for the exercises at:
   http://www.jegsworks.com/Lessons/resources/databases resources .
   

3. Import the text file Training Types.txt from your resource files as a new table named Training Types.
   This table lists the various types of training that were available for leaders.
   
   For the Import Text Wizard you need to know:
   • The file is comma delimited text.
   • There is only 1 column of data.
   • The first row contains column headings.
   • The text qualifier is a double quote (").
     If you do not specify the text qualifier, all of the values will include the quote marks!
   • Choose the field Event as the primary key. The values are already unique.
     
   • Open the Table Design View and reduce the field size of the Event to 50.
     
4. Import the spreadsheet file Adult Info.xls from your resource files as a table named Adult Info.
   
    For the Import Spreadsheet Wizard you need to know:
   • First Row contains column headings.
   • Import all fields.
   • Select AdultID as primary key.
   • Name the table Adult Info, removing the underscore that Access adds.

   Note: The original field names Home Phone # and Work Phone #  were changed during the export process, dropping the # symbol and instead using a period, like Home Phone . and Work Phone . That's strange enough, but, when you imported these fields, the periods were left off. Go figure!
   

5. Import the dBase file TRAINING.dbf or the XML file TRAINING.xml from your resource files.
   The Import Wizard runs all by itself. If you import the XML file, choose to import both structure and data. Access 2013 and 2016 will not import a DBF file. The XML file version was created from a dBase file which was created from another file format. When exporting in another format, sometimes features get changed. Beware!

   You don't need to make any choices! But if you imported the DBF format file, you need to know some quirks about DBF files.

   About DBF:

   • dBase names are limited to 10 characters
   • dBase names can contain only letters, numbers, and the underscore character. No spaces!
   • dBase names must start with a letter.

   DBF format has often been used for transferring data between different programs over the years. You must be aware of the disastrous effects that these restrictions on names can have!

   Exporting into DBF format made several changes from the original. Sometimes these differences make a big difference.

   • Name of the original table Training Taken was changed to just TRAINING, because of the space in the name.
   • The file's name and the field names were turned into all caps.
   • AutoNumber data was changed to just a number.
6. Rename the table back to its original name, Training Taken.
7. Change the EVENT field to be a Lookup field, using the Training Types table as its source. Sort the types Ascending. The imported table is already in alphabetical order but any new training types will go at the end of the list.
   
8. Delete the TRAININGID field, which is not an AutoNumber field, and create a new AutoNumber field named TrainingID. (Notice the capitalization). Make it the primary key. Move it to be the first field in the list.
   AutoNumber data type: Once there is data in a record anywhere, you cannot change a field's data type to AutoNumber, even if all of the values are different! AutoNumber fields are exported in other formats as just number fields.

Relationships:

A troop can have several leaders or assistant leaders. An adult can be a leader in more than one troop and can take many different trainings. So you have several One-to-Many relationships. You can create joins but to enforce referential integrity (to get the symbols on the join lines) you must make some changes because some data types don't match.

There are 3 fields named AdultID or ADULTID among the tables. They do not all have compatible data types! This is common with imported data when the primary key values are imported and are numbers. Access will not assign AutoNumber as the type to such fields. Complicating matters, once there is data in a record anywhere, you cannot change a field's data type to AutoNumber! You could delete the ID field and create a new ID field that has AutoNumber data type like you did above, BUT there are already values for AdultID in related tables. What a mess! The work-around is to use Long Integer data type. A field with that data type can be joined to an AutoNumber field.

1. In Table Design View, change the data type for all three fields (AdultID or ADULTID) in three different tables to Number: Long Integer.
2. Add the remaining tables to the Relationships window and create relationships between the tables as shown below.

   
   

3. Enforce referential integrity on the three new joins and allow Cascading Updates.
   With these changes, when you change an AdultID or Troop #, the related records can update automatically. Sweet! The downside is that the ID fields are not AutoNumbers. You must number them yourself.

4. Examine the records and the design view of each table.
   Do you understand what kind of information goes in each one?

   What kinds of questions was this database designed to answer? What reports could be created? (That's in the next project)

   • Who are the troop leaders for each troop?
   • What training has each adult taken?
   • Report: Attendance list for a training event
   • Report: Contact Info for troop leaders or for all adults in the database
   • Report: List of troops with troop leaders' contact info

Correcting Broken Queries:

Some queries that you imported to start with use tables that you imported from other sources. You changed the name of the Adult Info table. That broke relationships. Some field names may be different that in the original database! The dBase format, in particular, does not like long names or names with spaces and does like all caps. Eek!

1. Open each query in Query Design View and create simple joins where needed.
   Run the query. If you fail to create the joins, the results will fail or be very odd!

   Problem: No tables showing above the grid
   Solution: Check the scroll bars, both horizontal and vertical. The tables are just out of sight.

   Problem: List of table fields shows only *
   The display of the fields is just too short.
   Solution: Drag the edge of the table's field list to make it taller.

   Problem: One or more tables show but do not show any fields
   The named table does not exist in the database. Either it was renamed or the name in the grid no longer matches because of the way it was imported.
   Solution: Make notes on what fields are being pulled from the missing tables and what choices are in the design grid for those columns. Add the correct table. Change the field name by selecting from the drop list for that column. Once all corrections have been made, only then can you delete the blank tables. You must then recreate the columns that used data from those tables.
   

   Repair before deleting anything!
   If you delete a 'broken' table that show no fields from the Design View, any columns that used fields from that table vanish! You can delete the broken table only after you know you can recreate what the query used from it!
   

2. Correct field names:
   Some queries lost track of the source for fields when you changed the names of the fields.

   For any field that starts with Expr and a number, like Expr1:[Date] or Expr2:[Adult Info].[Last Name], revise to use an existing field with the name in the brackets [ ] from one of the displayed tables.

   Capitalization makes a difference! Event and EVENT are not the same to Access!

   Once you have the correct tables in the grid, you may only need to reselect the field name from the drop list in the grid or delete the Expr1: or similar part of the name. Or, you can delete the column and drag the correct field down again yourself.

   Problem: Error message says there is syntax error
   You may have typed something incorrectly. If you were selecting table name and field name from drop lists instead of typing, Access is just confused.
   Solution: Click on OK to close the error message and then on the ESC key to undo your changes to the field. Drag a new copy of the field from the correct table to the grid and delete the old column.
   

---

Export and Print Query Datasheet

1. Run the query QTroop Info Brownie Filter.
2. Export the datasheet as ex3-3-export-Lastname-Firstname.txt to your Class disk in the databases3 folder. Be sure to choose to export with formatting.
3. Open the text file in Notepad with Word Wrap off.
4. Change the Page Setup to use Landscape orientation.
5. Change the header to put your name at the left, the file name in the middle, and Ex. Access 3-3 at the right.
6. Print.
   (2 pages from Notepad. Text wrapped anyway!)