Access Assignment

1
Access Assignment

• Sharing data at the enterprise level

2
Your Access assignment

• Your FAG needs to do several things
• Share data with three other FAGs
• These are External Tables (where you will SPECIFY
  which FAG can see which table)
• SMG creates FIVE independent tables for CEO (more
  detail later)
• Create a set of data tables for you OWN FAG to
  see
• This is an internal set of tables that ONLY
  people in your own FAG (plus the Executive Suite
  and Senior Managers) can see
• MUST have AT LEAST TWO (2) RELATIONAL TABLES
  associated with a primary table
• ALL FAGs (including SMG) produce this set of
  tables
• Populate some standard tables with data
• a MAIN table
• a DETAILS table
• Populate a QUERY table with the queries you write

3
Table Relationships
4
Main Table (tMain)

• Generic data goes in this table
• There is a form in the db to allow you to add
  data to this table
• Employee_ID (this is your FAKE CUID from My Page)
• 11 relationship with tEmp_Details
• Name
• Office phone and location
• Supervisor (this is your Senior Manager get
  her/his FAKE CUID for this field)
• FAG identifier
• Etc
• tFAG is a lookup table

5
tMain Lookup Tables

• tFAG contains
• FAG_ID (same as in tMain)
• FAG_ID gives FAG_Name

6
Employee Details Table (tEmp_Details)

• Private information about employees is stored in
  this table, such as
• Gender
• Salary
• Birth Date
• Etc
• 11 relationship with tMain
• tProv is a lookup table

7
tEmp_Details Lookup Table

• tProv contains
• Short_Name (same as in Details file)
• Long Name

8
Generic Tables (Non-SMG)

• Every FAG must create SIX tables (3 3)
• Your partner FAGs will ask for 3 data sets (one
  each), and you must create a set for INTERNAL use
  (principal and 2 related tables)
• SMG does EIGHT tables (3 internal 5 for the
  CEO)
• All data are made up, fake, faux, fantasy, fairy
  tale
• EXCEPT that you must ANSWER THE QUESTIONS your
  partner FAGs ask! But you make up the data to
  answer them.
• These tables have a SPECIFIC NAMING CONVENTION,
  as
• Sffnnx
• See next slide for details

9
Generic Tables (Non-SMG)

• Sffnnx
• s section of the course
• ff FAG (from tFAG)
• nn a sequential number (00 - nn)
• x is for internal tables, and is either a, b or c
• the PRINCIPAL INTERNAL TABLE is sff00a
• the RELATED INTERNAL TABLES are sff00b and sff00c
• So, if you are in Sales in Section B, you will
  have
• b0800a (SECTION b, FAG 08 and ID 00 internal
  table 'a') for the PRINCIPAL INTERNAL TABLE and
  b0800b and b0800c for the related internal tables
• the three EXTERNAL tables would be b0801, b0802,
  b0803
• Or if you are in Logistics in Section F, you will
  have
• f1100a, f1100b, f1100c, f1101, f1102, f1103

10
Generic Tables (Non-SMG)

• You create the SIX tables
• Using the naming convention
• Don't name your fields using SPACES in the names.
• Don't name a field 'Total Sales'. Use
  'Total_Sales' instead.
• Don't use special characters in field names
• Such as !_at_ etc... Use ONLY letters,
  numbers and the '_' (underscore character). So
  don't use a field name like 'Server'. Use
  'Server_ID' instead.
• You must then record their existence in two other
  tables
• tTable_Name
• tTable_Control
• And then put the queries that you write into yet
  another table
• tQuery

11
Name Table (tTable_Name)

• tTable_Name contains description information for
  tables. It contains three fields
• Table_ID
• Tables are named as described earlier using the
  sffnnx convention
• Description
• The name of your FAG, PLUS a synopsis of the
  QUESTION you are answering for the requesting FAG
  and THEIR FAG NAME
• EXAMPLE Sales Total Sales by Region for
  Manufacturing
• For the zero-zero (internal) tables, you only
  need to provide a description for the 'a' table.
  Thus
• FAG NAME - INTERNAL TABLE A Your description of
  this table
• EXAMPLE Sales - Internal Table A - Sales By
  Customer
• Owned_By
• Your FAG from tFAG

12
Sample
13
Sample

• Then, once the tables are created and are entered
  into the tTable_Name table, you need to control
  viewing permission to them by using the
  tTable_Control table
• EVERY table must be exposed to the Executive
  Suite (FAG 0) and the SMG (FAG 14) and to the
  CREATORS of the table (your FAG)
• It is NOT necessary to expose internal tables 'b'
  and 'c'. Exposing the 'a' table will be
  sufficient since ONE query provides the data from
  all three tables.
• Your INTERNAL tables grant view permission to
  THREE (3) FAGs
• If the table is being created as a request for
  another FAG, their FAG must be given view
  permission
• Thus, your EXTERNAL tables must grant permission
  to FOUR (4) FAGs (your FAG, the requesting FAG,
  the SMG and the Executive Suite)

14
Sample

• The Permission_ID field is AutoNumber
• In this example, IS/Telecom/Datacom (section a)
  is allowing the Executive Suite (Allowed 0),
  themselves (09) and the SMG (14) to see their
  INTERNAL table (e0900a) through Permission_IDs 2,
  3 and 4
• They are also giving permission to RD (FAG 4) to
  view their EXTERNAL TABLE 01 in Permission_ID
  8. They also give viewing permission to the
  Executive Suite, the SMG and themselves. And so
  on...

15
Queries

• Each internal and external table MUST have a
  field that allows the selection of a SUBSET of
  data
• The value must be a UNIQUE VALUE and NOT A RANGE
• DO NOT use a DATE/TIME field or a TEXT field for
  this value
• For example, if you want to select data for the
  MONTH of MARCH, use the month NUMBER (3) and not
  the NAME (March)
• The query you write must return the data in the
  table RELATED TO THE FIELD IN QUESTION, even
  though you will have MORE DATA in the tables than
  you need
• Example CUSTOMER SERVICE asks SALES for TOTAL
  SALES BY PRODUCT LINE FOR THE PREVIOUS QUARTER.
  SALES might have data referring to the previous
  10 quarters, but their query might return only
  the CURRENT quarter, or the last 4 quarters. So,
  bottom line, you must have a WHERE CLAUSE in your
  SELECT SQL statement and EVERY table you produce
  MUST CONTAIN MORE DATA THAN IS RETURNED BY THE
  QUERY!

16
Sample Table

• In this example, IS/Telecom/Datacom is providing
  Accounting with the number of PCs in their FAG at
  various points in time.
• If Accounting asked IS/T/D the question "How many
  PCs were currently in use at close of FY 1995?",
  the SQL statement that IS/T/D would write to
  answer the question for Accounting would be
• SELECT Number_PCs_End_FY FROM e0903 WHERE
  Fiscal_Year 1995
• This query would be copied to the tQuery table
  from the SQL pane in the Query area of Access.

17
Queries

• Once the tables are created and the queries are
  written and tested, you need to COPY the queries
  into another table, the tQuery table
• We will use this table to show the data on the
  petPRO intranet
• You use the Table Name as the Key field (see
  slide 27)
• The query that you copy goes into the field named
  'Query'
• EVERY FAG must include a query for their 00a
  table which JOINS their a, b and c tables
• The name of this query is sff00
• The other queries are similarly named, as in
• sff01, sff02 and sff03
• These are the FOUR (4) queries required for the 6
  tables

18
The SMG

• The SMG produces EIGHT tables
• The set of related internal tables for the SMG
  itself
• named s1400a, s1400b and s1400c (where s your
  section of the course)
• Then one for each manager - containing data that
  the CEO asks for, related to the FAGs that s/he
  is responsible for
• These tables are named s1401, s1402 s1405 (s
  your section)
• The SMG produces SIX (6) queries one for each
  of the five CEO tables one for the INTERNAL
  tables

19
Sample Data - 00a Table
20
Sample Data - 00b Table
21
Sample Data - 00c Table
22
Relationships
23
Sample 00 Query Design View
24
Sample 00 Query Table View
25
Sample 00 Query SQL View
26
Sample 00 Query Copied and pasted into tQuery
27
Copy the Query

• Copy the query from the SQL View window into the
  Query Field of tQuery, adding the appropriate
  Query_ID
• The Query_ID for your INTERNAL tables is
• SFF00 (Section, FAG and 00)
• For your EXTERNAL tables (to answer the questions
  posed by your partners), you use the same
  convention
• SFF01, SFF02 and SFF03

28
Stuff to watch...

• When you create your queries in Access, DON'T
  NAME THEM the same as the table name.
• The query must be IDENTIFIED by the name sffnn
  (as in A0301) in the tQuery Table, but if you
  create a query and save it with that name, Access
  will complain, saying that you already have an
  object with that name.
• This object is your table. So give the query a
  different name, like Q0, and Q1, Q2. In the
  example we just used, the 00 query was named
  'internal'.
• When you copy and paste the SQL into the tQuery
  table, give it the correct name at that point.
  This will save you a lot of trouble.
• Make SURE you review the contents of each query
  in the tQuery table. You will notice that you
  have to expand the item in order to see all the
  text in it. (See slide 25 for clarification).

29
Stuff to watch...

• Make sure that your INTERNAL tables are related,
  and that you only have ONE QUERY to retrieve the
  data. In fact, you don't even need to give
  permission (in tTable_Control) for the 00b and
  00c tables. It won't hurt if you do. So ONE QUERY
  must return the data from 00a, 00b and 00c. This
  query MUST contain TWO JOINS! The data MUST BE
  related. Make sure you understand this point.

30
Stuff to watch...

• Try to avoid building queries that use data in
  the tMain or tEmp_Details tables as these tables
  get renamed when the petPRO master database is
  created. Your queries will return an error
  because they will not be able to find these
  tables. The data are still there, but the table
  names are different. Make up your own data.
• If you really MUST use the tables, here is the
  mapping
• tMain s0000
• tEmp_Details s0001
• Where s your section

31
Stuff to watch...

• Do NOT create 'Parameter Queries'. Parameter
  queries ask the user to enter a value in response
  to a prompt. Your queries must run as they are
  WITHOUT any intervention from the user. An
  example of a parameter query is
• SELECT b0202.Quarter FROM b0202 WHERE
  b0202.QuarterEnter Quarters(1,2,3,4)
• The user is asked to enter a 1 or 2 or 3 or 4 in
  response to the prompt "Enter Quarters (1,2,3,4)"
• This type of query will NOT run on the petPRO
  intranet!

32
Stuff to watch...

• Once again
• Don't name your fields using SPACES in the names.
• Don't name a field 'Total Sales'. Use
  'Total_Sales' instead.
• Don't use special characters in field names
• Such as !_at_ etc... Use ONLY letters,
  numbers and the '_' (underscore character). So
  don't use a field name like 'Server'. Use
  'Server_ID' instead.
• Do NOT use a NON-ENGLISH version of Access to do
  your assignment as the way the data are stored
  will cause problems when they are imported into
  the main petPRO database. USE AN ENGLISH LANGUAGE
  VERSION OF ACCESS!!!

33
Stuff to watch...

• Don't do SELECT statements using DATE/TIME fields
  or TEXT
• They will work properly on the petPRO intranet
  site BUT you will NOT be able to test them on the
  2400 website if they have dates or text as the
  target of the SELECT
• Example that WON'T be testable
• SELECT field1, field2 FROM sff001 WHERE
  Sales_Region 'Western Ontario'
• Rather, use this
• SELECT field1, field2 FROM sff01 WHERE
  Sales_Region 1
• In this case, there would be another field that
  would identify what the region is

34
Don't forget!!!

• Someone in EVERY FAG must log into My Page and
  click the 'Share Data' button. It is here that
  you specify your questions for your partner FAG,
  and supply the rationale for your questions that
  your instructor needs in order to mark you on the
  quality of your questions.
• This MUST be done 2 weeks before the Access
  assignment is due.
• SMG RECEIVES questions from the CEO (but no
  rationale... If the boss wants it, the boss gets
  it -)

35
Main Form (Provided in db)
36
Detail Form (Provided in db)
37
In General

• The tables and queries MUST BE constructed
  EXACTLY the same as they are specified here or
  they will not be importable
• Good luck!

38
Personal Access Assignment

• Once the group work is done and the data are
  posted, the individual part kicks in.
• You need to go to My Page, and click the "Do
  Personal Access" button.
• You need to create a query in the text boxes that
  appears. This query must retrieve a
  'security_code' from tMain from the petPRO
  database using your FAKE CUID in the WHERE clause
  of the SQL statement.

39
Personal Access Assignment

• Once correct, your query will retrieve a
  'security_code' which was added by us to your
  tMain table (renamed see next slide) when it
  was imported into the petPRO intranet database.
  Once your code is retrieved, you need to click
  the 'Send Email' button.
• This will send an email to your instructor,
  informing her/him that you have completed the
  assignment.
• You can only do it ONCE, so make sure you get it
  right... -)

40
And finally...

• Your tMain table is RENAMED to S0000 (where S is
  your section) when it is imported. The field name
  to retrieve is 'security_code' and your Fake CUID
  field name is well, YOU look it up in your
  database table tMain.
• If you don't complete this part, 10 is deducted
  off the top of YOUR Group Access mark (whole
  group doesn't lose, just you)
• Have fun!!!