Querying and Reporting multiple tables database

[AnnaSerafini]

I have to create a database with information on Scottish buildings from 17 and 18 centuries. In order for my data to be searchable I need to have a series of tables connected with 1 to many relationships. Each building must be positioned on a map, which will have other layers like geo-referenced historic maps, and other features, like industries that supply materials needed for the building's conservation, etc... The database will be continuously used and updated. I was using Access, but I found that the connection with ArcGIS was not ideal:  either I had to export from Access and Import in ArcGIS a single table or query, or I had to create an OLE DB connection, which isn't able to "read" the relations between the tables. Therefore I thought doing all the database directly in ArcGIS would be better. I created different tables connected with realtionship classes, and one of them has X Y coordinates that give me the position of the buildings. The problem is I can't query more than one table at a time, nor create a report from more than one table at a time. what am I doing wrong? I'm new to databases and to both Access and ArcGIS, so any suggestions are welcome. Thank you.

[WilliamCraft]

Based on the info you provided, it's tough to fully understand your requirements surrounding the maintenance and consumption of the data.  For example, are you planning to make updates in ArcMap only?  Will the data be viewed from an end-user standpoint in ArcMap or will the data need to be published as a map service and visible to others via the web?  For the time being, in reviewing what you wrote I'll keep it simple and assume you're looking for an ArcMap-only solution at the moment.  The project you described could take so many different paths!  It sounds interesting though! 

Are you planning to use ArcSDE with either SQL Server or Oracle, or are you planning to use something like a file geodatabase?  I agree with you the Access is not the best candidate, so I would avoid the personal geodatabase (plus I don't think it's supported at 10.2).  Generally, the file geodatabase will suffice if you or only a small handful of people will be making edits at separate times.  How much data are we talking about (i.e., small like 50 MB, or much larger like 10 GB)?  The biggest thing that I see you needing to think about, at least at the beginning, is how to structure your tables and their various relationship classes.  If you haven't already, you'll have to give some serious thought about how the data model is going to look.  That includes a number of things like primary keys, foreign keys, constraints, field lengths, data types, general cardinality, and unique ID formats among other things. 

If you choose to go with a file geodatabase to store your various tables and "link" them with relationship classes, that should work fine.  I would suggest reading about the various relationship class types to understand your options.  That being said, use your X and Y columns as the source for using the "Add XY Event Layer" tool in ArcMap.  Once the features appear on the map, identify one of them to see how the related information shows up. 

In terms of creating reports, what type of reporting engine are you thinking of using?  There are several open-source options and there are several off-the-shelf options too.  Tableau and Crystal Reports are two semi-popular reporting engines to investigate.

[AnnaSerafini]

Thank you for your reply. I'm sorry I wasn't very precise in my explanations, but that's because I'm still a little bit confused about all this. I'm using ArcGIS 10.1. The data will be used by two people for the moment, and the idea is to publish it in a map service at some point, but not for now. I was using a personal geodatabase, because I was still uncertain if to use Access or not, but if you say a file geodatabase is better, I'll try that. The amount of data is small for now, but it will get bigger and bigger, because eventually a lot of pictures and drawings will be added as well. In any case, I have a structure for the tables which should work, since I was using it in Access, the only difference is in the type of relationship classes, but I've already looked at that (hopefully). My main problem is creating a report from multiple tables. Unfortunately, if I understood right, Tableau and Crystal Reports are no longer included in Arcmap in the version 10.1...

[WilliamCraft]

Your pictures and related documents can't be stored in the file geodatabase, but they can be cataloged in the file geodatabase and used as attributes (hyperlinks or direct file paths, perhaps, depending on how you plan to make calls to them).  If you end up using an RDBMS, then it's possible to store the related files within the Oracle or SQL Server database but there are many reasons why that would still not be ideal.  In either case, if you have a data model and your relationship class structure is sound then it sounds like you're halfway there already.  The Crystal Report reporting engine and authoring tools used to be provided with ArcGIS (I think at the 9.3 and earlier releases), but I think they are no longer part of the application installation files.  Unfortunately, Esri does not provide a reporting engine that really suits your needs I don't think.  However, from a web application standpoint, you may be able to leverage one of the web APIs (Silverlight, Flex, Javascript) to generate a report but that would require custom coding.  Otherwise, you may require the use of an off-the-shelf solution as mentioned earlier but you'd still need custom code to integrate the reports with your web application I believe.  I don't know if there is an all-inclusive solution out there that contains a web GIS viewer and reporting interface but you might try some online searches to see if any vendors pop up.

[StewartGalloway]

Did you ever get a solution to creating a report from multiple tables?
Thanks.

[MichaelVolz]

I think you might want to look into attachments for the storage of associated images in the file geodatabase, as I think this will work well in a mapservice when serving it over the web.