Clarification: Personal vs file geodatabases?

Question

Hi All,

I'm pretty confused as to the limitations of the various formats of geodatabases - personal vs file.

I'm much more familiar with MS access databases, and have a few built that run through a series of queries to produce an x,y table with calculated fields. One of the fields is calculated using a Row Statistics module. Others are the result of a crosstab query.

Unfortunately, Arc doesn't like the calculated fields much, and refuses to show the query in question from Catalog.

I would really like to embrace the new geodatabase format, but my understanding is that this level of querying and data structure is not available? Is there a happy solution to get my data into Arc, without exporting it to excel first? Thanks for the insights,

Matt

CAEPMAN · Answer

I was able to resolve the problem I was having with a joined date-based crosstab query (created through MS Access) not functioning properly in ArcGIS (described in previous post, above). Modifying the short date format specified in my Windows Regional Settings was the key to resolving this problem.

The short date format used by Access is that which is specified by the Short Date format in your Windows Regional Settings. Typically, the default short date format is setup to display dates as dd/mm/yyyy or dd-mm-yyyy. With these formats 29 August 2005 will be displayed as 29/08/2005 or 8-29-2006. With the first character in each of these short date formats being numeric ArcGIS has trouble with the data held in these fields (info on this available here: http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?id=2760&pid=2745&topicname=Working_with_Microsoft_Access_files_in_ArcGIS ).

I changed my default short date format to mmm_dd_yyyy and now my MS Access date-based crosstab query joins to my feature class attribute table in ArcGIS without any problem.

To change the system short date format...

Go into  "Control Panel" => "Regional and Language Options" (RLO) then click the "Customize" button on the "Regional Options" tab (this will reveal the "Customize Regional Options" form). Next, click on the "Date" tab. Edit the "Date separator:" field to "_" (underscore, no quotes) and then edit the "Short date format:" field to read "MMM_dd_yyyy" (no quotes). Finally hit the "Apply" button and then close the RLO windows. You should now be able to join and use you Access date-based crosstab queries in ArcGIS without any trouble.

ODBC_CrosstabQuery_StepsToFix.gif

test2.html.zip

CAEPMAN · Answer

Tables and queries (both selection and crosstab) within a Personal Geodatabase/Access Database are visible through ArcMap via a "Database Connection" defined in ArcCatalog using the "ODBC" OLE provider. Info on this is described in the old archived ArcGIS forum here: http://forums.esri.com/Thread.asp?c=93&f=988&t=107692&mc=7#msgid305726, on the U of Waterloo website here: http://env.uwaterloo.ca/computing/services/gis/gis_help/arcgis/desktop/faq1.html, and through the newer ESRI support pages here: http://support.esri.com/en/knowledgebase/techarticles/detail/20828

However, I am having some problems with a date-based crosstab query I created in Access and have joined to the attribute table of a feature class in ArcMap:* Data in the date-based crosstab are displaying correctly when previewed through ArcCatalog, but the field headings are displaying incorrectly (e.g. 06/07/2010 is displayed as _07_2010)
* In ArcMap, the field headings for the date-based crosstab query joined to a feature class attribute table are displayed correctly (e.g. 06/07/2010 is displayed as 06/07/2010), but the tabular data is wrong. The numeric value in the first record of each field is repeated for each record in that field. Each of the repeated values under each field from the crosstab is not a reasonable value - they are outside the range (much smaller) than values in the crosstab as viewed through Access.
* Oddly, the statistics (generated in ArcMap by right-clicking field and selecting statistics) for the data in each crosstab field are correct, despite the values from the crosstab being displayed incorrectly.
* I tried generating a raster based on one of the fields in the crosstab - the raster was incorrect - the repeated value displayed in the joined attribute table was used to derive the raster, not the true values in the crosstab as viewed through Access.

I have attached a graphic containing a series of annotated screen caps that illustrate the errors described above.

Has anyone else encountered the same problem and figured out a workaround?

-Darren

ODBC_CrosstabQuery.gif

6-AGOL_OpenInArcGISPro.png

DerekLaw · Answer

Hi Matt, ... In the mean time, support for complex queries is limited with an access-based database?

For executing queries outside of ArcGIS with respect to File geodatabases, the answer currently is yes. You can certainly perform complex queries using ArcGIS - I guess it depends on how you define "complex query".

Hope this helps,