Dexter Query Detailed Description
Query ID: acsbtable1
SummaryThe 2005 American Community Survey tables are stored in data directory ("filetype") acs2005. The tables are grouped by certain categories ("B" vs "C", with imputation, PR (Puetro Rico only) and other special tables grouped into separate data sets), as well as by "wave". See the acs2005 Readme file for details. In this example we assume that we know what table we want. It is table B17002, containing data on poverty ratios of persons. We want to retrieve this data in a csv file (so that we can load it into Excel and do postprocessing) and we want it for all states (without any geographic components).
Filetype accessed: acs2005 : American Community Survey data for 2005.
Data Set accessed: btables2 (because this is where we keep all the "B" tables released in Wave 2.) See detailed metadata for this data set.
Dexter features used: Nothing beyond the basics. But we do explain how to get around a glitch (bug?) in the IE browser (Version 6) so that we are able to scroll through the Tables select list.
Can be readily modified to:
Degree of Difficulty: 3 - moderate. (the Dexter part is not very hard, but it takes some familiarity with this complex data collection to know where to find the available tables and determine which of the data sets in this directory contains the table desired.
Annotated Dexter Query Form (As filled out to define this query)
Section I: We accept the default. Output is to a delimited file in csv format. We want our data for postprocessing using Excel.
Section II: The filter specifies two conditions:
We select the single identifier AreaName from the Identifiers list on the left. You might also want to select State or Geocode if you wanted a FIPS code to go with the name of the state. You would definitely want to choose more and/or different identifiers if you were selecting more summary levels. You would want to keep either Name or Geocomp_name if you were not filtering out the geographic components.
This screen snapshot shows the browser window as it was initially displayed and after we chose our identifier(s). Note that the Tables select list on the right is not completely displayed; it is too wide to fit on our screen. We shall need to use the horizontal scroll bar in our Internet Explorer browser window to make the Tables select list fully visible, including its vertical scroll bar. We shall need to use the latter to scroll down to the table we wish to select.
Here is what the screen looks like after we scroll right to see the full Tables select window with its vertical scroll bar. What you cannot see from the screen shot is what happens when you try to make use of that vertical scroll bar. If you are using Internet Explorer, Version 6 it does not work; somehow IE gets disoriented and loses track of where the scroll bar is at this point. (This problem appears to be fixed in IE, Version 7.)
The solution to this glitch is pretty simple but not intuitively obvious. You have to use the vertical scrollbar on the IE window (NOT the one on the TABLES select window) to go down the page so that the Tables window is no longer visible, and then scroll back up so that it is once again visible. At this point the scroll bar seems to always work. (At least this is the way it was in the spring of 2007; this problem may have disappeared by the time you read this, or maybe it is just my browser that behaves this way.) At any rate here is what the window looks like once you have a working scrollbar and can select the table you want:
Section IV and Section V: These sections are not used for this query. We use the Extract Data button at the bottom of Section III to submit the query.
The query takes about 20 seconds to run because the data set being accessed is quite large. When the Output menu screen displays a message telling me that the extraction has completed I then click on the link to the Delimted File. Since my browser (IE) has been configured to automatically invoke Excel when I click on a file with a csv extension the next thing I see is this (not all rows shown):
Sharing these results with a client or colleague is rather easy. You can always save your work as an xls or csv file on your PC and then send it as an attachment to the person(s) with whom you want to share. The file is rather large and it will waste some resources doing it this way but it's familiar and easy. Another option you have is to take advantage of the fact that any output file you generate with Dexter remains on the mcdc server for 2 days and is publicly accessible. To get the URL for your output file right click on the Delimited File link on the Output Menu page and choose "Copy Shortcut" (stores the URL in the clipboard). Then go to your e-mail client and send a message to the person(s) with whom you want to share your results. As part of the message you can just paste the URL of the file that you just copied to the clipboard. For example, when we ran this xsample to create this page our output was saved at /tmpscratch/14MAY07_00007.dexter/xtract.csv . You can do the same thing with the Summary log file. These are temporary files and will remain on the server for approximately 48 hours from the time they are created (barring an emergency).