6 Replies Latest reply on Mar 25, 2009 6:24 PM by CBeranek

    Report from multiple tables?



      Report from multiple tables?




      I have a database for artifacts based on the place that they are from, a site and context field.  Each type of object has its own table.  The tables are all related by the site and context information.  Each site has multiple contexts, and each context can have records in each of 5 different object-based tables, or no records in some tables and some in others.  Each context can have between 0 and an arbitrarily large number of records in any of its tables. 
      I’d like to create a report showing data from multiple tables for a list of locations.  Is this actually possible – to create a report proper with grouped and summarized data, not just a layout using portals?  I’ve seen Fenton Jones’ reply to a similar question on March 2 which suggests just using multiple portals and sliding/printing, but I can’t make this work for me, primarily because I have to set the number of portal lines to a higher value than I may ever have records (not impossible, just cumbersome since this can be arbitrarily large). 
      I also like the way the report from a single table can look, while the portals always seem to show the boxes around the portals.
      So, can I produce a report (with or without sub-summaries) with data from multiple tables any other way than using portals/sliding/printing?  If I have to use the portal option, what should I do about setting the number of portal rows, and is there a way I can get the result to print without showing the boxes around the portals?

        • 1. Re: Report from multiple tables?

          Can you explain your relationships in more detail? I understand that one site has many contexts, but I lost you after that.


          Also: why does each type of object have its own table? It seems this could be your greatest obstacle.

          • 2. Re: Report from multiple tables?

            Sorry for not being clear --


            Basically, we use the database to catalog objects -- there are multiple tables becase we need to record different sorts of info about the different classes of objects  (ie the fields/value lists useful for describing metal hardware are not useful for describing ceramic dishes, so there are separate tables for metal and for ceramics). We are so far committed to this set up that I would like to try to find a way to work with it (it existed before I started working on it).


            Site and context are the fields that relate the different tables -- every object is from some site and context.  Site A might have 10 contexts (locations within the site).  Site A, context 1 might have 100 objects -- some from the ceramics tables, some from the glass table, some from the metal table, etc.  Site A, context 2 has its own list of object, etc.  In terms of tables, there is a central table with site and context information that all of the other object-type tables are linked to.


            I've been able to make reports from a single table (ceramics) with sub-summaries sorted by context (and some of the attributes of the ceramics).  What I really need to find some way to do is to generate a printable report that will show me all of the objects (from all the tables) from Site A, sorted by context - so all the objects from Site A, context 1, then site A, context 2, etc.


            Thanks for any assistance - sorry if I'm unclear - I'm an object specialist, conversant but not expert with databases.

            • 3. Re: Report from multiple tables?

              Do I understand this correctly:


              A site has many contexts, but each context belongs to one site only;

              A context has many objects, but each object belongs to one context only (and consequently, to one site):




              • 4. Re: Report from multiple tables?

                The second part is correct - each object belongs to only one context and to one site.  The first part is more complicated.  Conceptually, each context belongs to only one site, but each site can have a context 1, just because of how things are numbered.  Site A, context 1 and site B, context 1 refer to different places, though they both have the  context number 1.  That's why all of our tables are related by site AND context - the Site/context combination is unique.


                A site has many contexts, but each context belongs to one site only;

                A context has many objects, but each object belongs to one context only (and consequently, to one site):


                • 5. Re: Report from multiple tables?
                     OK, then. Let me outline some options for you - in order of decreasing "corectness":

                  1. Strictly speaking, all your objects should be in the same Objects table, with additional tables for the 5 object types (a supertype/subtype data model), e.g.:

                  Sites -< Contexts -< Objects - CeramicObjects

                  Note that the relationship between the 'supertype' Objects and the 'subtype CewramicObjects is one-to-one.

                  However, this data model is not easy to implement, and I wouldn't suggest it to someone who isn't at least on an intermediate level.

                  2. You could put all your objects in a single Objects table, with all the fields from your current 5 tables combined. This is very easy to implement in version 10 with script triggers - in fact, it can be completely transparent to the user (you select a type, a find is peformed and the correct layout is loaded).

                  3. You can import the objects you want to report on into a temp "union" table. Presumably, for the report, only common fields will be used.

                  4. You can define a calculation field in the Contexts table, along the lines of =

                  List (
                  "Ceramic Objects" ;
                  List ( CeramicObjects:: ObjectName ) ;
                  "Metal Objects" ;
                  List ( MetalObjects:: ObjectName ) ;

                  then produce the report from the Contexts table, using this field as the "portal".

                  You should definitely have a Contexts table, with a unique ContextID field (the names can be duplicates), and link to Objects using the unique ContextID only.

                  • 6. Re: Report from multiple tables?

                    thanks very much - will talk these options over with people at work and see what we should do.