PHS CyberSchool

Computer Studies SG 

Election database - Queries, Switchboard and Personalising the database

The election is coming and each constituency has been given a voters roll for the area. 

The roll has been given in spreadsheet format to allow it to be imported into a database, any type and version.  In the spreadsheet we will sort it again to hide identity and explore it with filters.  We will then import it into Access and work with   it.  Access is a relatively new application, and in the past Excel was used to do queries.

1. Open the Excel file Election.xls.  (It is huge - zipped 501 kb)  Sort different columns (not the records) selecting parts of the columns, the aim is to make sure that the fields of the records are muddled to retain privacy.  In order to keep postal codes with towns try not to separate those fields.  You cannot do this in Access.   
2. In Excel use Auto Filter to explore the voters roll.  Note strange things.  Save and close the spreadsheet.
3. In Access create a database called ElectionYourInitials.  Import the Election spreadsheet keeping the first row as the column headings. Make the ID Number the primary key.  Save the table as Ward XYZ.
4. In Design view look at the field names and datatypes of the table.  Leave the field types but adjust the field sizes so that space is not wasted.
5. Queries - Save all queries with meaningful answers.  (Use the tip sheets for assistance.)
   1. Make and Delete queries
      1. Create a Make-Table query of all those Deceased persons.  Save the new table as Deceased.  Save the query as Deceased Query.  
      2. Create a Delete Query to remove all those who are Deceased from the Ward XYZ table.  Save the query as Delete Deceased.
      3. Create a Make-Table query of all those Non South persons in the Ward XYZ table.  Save the new table as Non South Africans.  Save the query as Non South Africans Query.
      4. Create a Delete Query to delete all the Non South persons from the Ward XYZ table.  Save the query as Delete Non South Africans.
      5. Create a Make-Table query of all those Not On Pop persons.  Save the new table as Not On Pop.  Save the query as Not On Pop Query.  (Be careful!)
      6. Create a Delete Query to delete all the Not On Pop persons from the Ward XYZ table.  Save the query as Delete Not On Pops.
      7. Create a Make-Table Query of Suburbs and Postal codes.  Save the table as Suburbs and Postal Codes.  (You will need to have unique values.)  Save the query as Suburbs and Postal Codes Query.
   2. Mixed queries - Save the queries with valid names
      1. How many people are born in 1947?  (ID Numbers are known to begin with the numbers of the year in which a person is born.)
      2. How many people were born in 1902?  (I wonder if this is a mistake in the data gathering.)
      3. How many people were born in January 1947?
      4. Was anybody born on 11 November 1944?
      5. In which suburb(s) do Philander(s) live? 
      6. How many suburbs are found in this ward? (Use unique values)
      7. How many voters are in each suburb? (Use unique values and Totals (Count))
      8. Which suburb has the highest number of voters?  (Use unique values and Totals (Count), then sorted)
   3. Crosstab query
      1. Use the wizard to create a crosstab query to find in which suburb voters with the same surname live.  (Row heading - Surname; Column heading - Suburbname).  
         1. Name the suburb(s) in which people with the Miller surname live.
         2. Name the suburb(s) in which people with the Parker surname live.
   4. Parameter queries
      1. List all those voters whose surnames are between certain letters.
      2. List the particulars of all those voters who live in a street (for example Bloem*) between certain numbers.
      3. List all those persons born between certain dates.
6. Table design   (Use the tip sheets for assistance.)
   1. Use the table created in Suburbs and Postal codes to create a lookup wizard for the field Postal codes. Check that it works in Table design.
   2. Use the table created in Suburbs and Postal codes to create a lookup wizard for the field SuburbName.  Check that it works in Table design.
7. Personalising the database  (Use the tip sheet for assistance.)
   1. Add the political party's name in the area Application Title and the party's logo as the Application icon.
8. Switchboard
   1. Create macros of any five of the queries.
   2. With command buttons on a blank form create your own switchboard of five of the queries.  Do not use the wizard.
   3. Add a suitable political logo to the design of the switchboard.
   4. Create a feature so that the database opens with the switchboard.