Skip to main content

Access Examples

To determine the FAC code for Y12 from the ORISEWDS_d1 - Master roster file, click on the header for FAC1:

Access HEader Example

That will display the variable level for each header.

Access variable display

Selecting the FACCODE codeset will list all of its associated values.

You will find that Y12 is Facility Code 6:

Access facility code

Example overview using Microsoft Access 2010

Download the following files from the CEDR website.

  1. Under Browse Data Sets menu
  2. Select Working Data File Sets
  3. From the Site combo box select ALL Working Sets
  4. Scroll down to and click on the ORISEWDS data file set
  • ORISEWDS_d1 - Master roster file
  • ORISEWDS_d2 - Employment data
  • ORISEWDS_d6 - Master vital status file
  • ORISEWDS_d7 - Death file

Import the files into MS Access. For the import process all data types were defined as Text.

Access table

After importing the ORISEWDS_d1 - Master roster file, select everyone that worked at Y12.

Refining the cohort

  1. Select all males at Y12

Determine the vital status for a cohort

  1. After importing ORISEWDS_d6 - Master vital status file, show the vital status of all males at Y12
  2. After importing ORISEWDS_d7 - Death file, select all deceased males who worked at Y12 and what their cause of death was.

Determine a cohort who worked in a particular time period

  1. After importing the ORISEWDS_d2 - Employment data, select all males who have ever worked at Y12 between 1980 and 2000.
  2. Select all Y12 males that were ever employed between 1980 and 2000 and their associated vital status.

'q01_All Males at Y12
'Example 1 - Selecting Y12 male workers
'FAC code 6 is for Y12
'FAC1-Fac10 are checked to determine if they were ever employed at Y12
'

SELECT id FROM [tblORISEWDS_d1 Master Roster]
WHERE (((sex)="M") AND ((fac1)="6")) OR
(((sex)="M") AND ((fac2)="6")) OR
(((sex)="M") AND ((fac3)="6")) OR
(((sex)="M") AND ((fac4)="6")) OR
(((sex)="M") AND ((fac5)="6")) OR
(((sex)="M") AND ((fac6)="6")) OR
(((sex)="M") AND ((fac7)="6")) OR
(((sex)="M") AND ((fac8)="6")) OR
(((sex)="M") AND ((fac9)="6")) OR
(((sex)="M") AND ((fac10)="6"));

'q02_Y12 Male vital stats
'Example 2 - Selecting vital status for Y12 males
'Import the Master vital status file
'

SELECT [tblORISEWDS_d6 Master vital status].*
FROM [q01_All Males at Y12] INNER JOIN [tblORISEWDS_d6 Master vital status] ON
[q01_All Males at Y12].id = [tblORISEWDS_d6 Master vital status].id;

'q03_CoD for Y12 males
'Example 3 - Selecting deceased males at Y12 and their cause of death
'Import the Death file
'

SELECT [tblORISEWDS_d7 Death].*
FROM [q01_All Males at Y12] INNER JOIN [tblORISEWDS_d7 Death] ON
[q01_All Males at Y12].id = [tblORISEWDS_d7 Death].id;

'q04_Y12 Males worked between 1980-2000
'Example 4 - Employment data for all males who were ever hired at
'Y12 between 1980 and 2000
'Import the Employment data
'

SELECT DISTINCT [tblORISEWDS_d2 Employment Data].id
FROM [q01_All Males at Y12] INNER JOIN [tblORISEWDS_d2 Employment Data] ON
[q01_All Males at Y12].id = [tblORISEWDS_d2 Employment Data].id
WHERE (((IIf(IsDate([hiredate])=True,CDate([hiredate]),Null))>=#1/1/1980#) AND ((IIf(IsDate([termdate])=True,CDate([termdate]),Null))<=#12/31/2000#));

' q05_Y12 Males wVital Stats between 1980-2000
' Example 5 - Y12 males ever hired between 1980-2000 and their vital status
'

SELECT [q03_CoD for Y12 males].*
FROM [q04_Y12 Males worked between 1980-2000] INNER JOIN [q03_CoD for Y12 males] ON [q04_Y12 Males worked between 1980-2000].id = [q03_CoD for Y12 males].id;

Below is the list of all queries used in the MS Access database:

Access queries

Next: See Tableau Examples

Share This Page:
To Top
ORISE

Contact Us
Oak Ridge Institute for Science and Education (ORISE) is managed for the U.S. Department of Energy (DOE) by Oak Ridge Associated Universities (ORAU). Files are built in .CSV and .XLS formats for use in Excel. Bibliographies are in Adobe Reader .PDF format - Privacy and Security Statement