5 Replies Latest reply on Mar 9, 2012 10:34 AM by philmodjunk

    perform a find on multiple tables

    leighmorganhomes

      Title

      perform a find on multiple tables

      Post

       i need to display fields from multiple tables that have similar data. can that be done

        • 1. Re: perform a find on multiple tables
          philmodjunk

          Yes it can be done.

          HOW you do it depends on the structure of your database and what you are ultimately trying to accomplish.

          It could be that you have separate tables that should really be one combined table.

          It's also possible to use filtered search portals to display records matching the search text (and updating keystroke by keystroke). A series of such portals would each display matching data from a different table.

          • 2. Re: perform a find on multiple tables
            leighmorganhomes

            the tables have similar data. i am a cabinet maker. i construct 6 types of cabinets, usually some of each in each kitchen. the calculations of each type are different. the input data into each table is the same e.g. height, width, depth. the types are "upper double door" "upper single door" "lower stack of drawers" "lower with double doors and drawer on top" "lower with single door and drawer on top" the calculations for the single door are different from the calculations for double door.  that is why im using multiple tables. id like to input data on one form with job name, width, height, depth and type. then i would like to extract for example a list of door and drawer face sizes for the entire job. any help would be appreciated.   Leigh 

            • 3. Re: perform a find on multiple tables
              philmodjunk

              Sounds like you need one table instead of 6. There are a number of ways to handle the differences in calculations. Here's one approach:

              Case ( CabinetType = "upper double door" ; //put calculation for cabinets of this type here ;
                          CabinetType =  "upper single door" ; //put calculation for cabinets of this type here ;
                          CabinetType = "lower stack of drawers" ; //put calculation for cabinets of this type here ;
                          CabinetType = "lower with double doors and drawer on top" ; //put calculation for cabinets of this type here ;
                          CabinetType = "lower with single door and drawer on top" ; //put calculation for cabinets of this type here)

              This is something of a "brute force" solution--there are other more sophisticated approaches that provide greater flexibility, but is an approach that's fairly simple to set up. Other approaches allow you to link to related tables where the differences in type are handled--which could make adding a 7th type of cabinet a simple data entry task instead of having to modify your database design.

              Production databases often set up a table where one record represents one product and a related Bill of Materials table (BOM) where each record lists one material and quantity used to construct one product. Then a production or Jobs table is used to document the actual production of product. A record in the Jobs table might specify 3 "upper double door" cabinets and then the data in the Products and BOM tables is used to produce a list of materials and quantities needed to produce the specified product.

              You can also have two related tables, a BOM that lists raw materials (X board feet of Walnut, 25 join biscuits, etc.) and a components table listing the components to be produced (3 drawers, 2 doors, 5 shelves, etc.)

              • 4. Re: perform a find on multiple tables
                leighmorganhomes

                i think you are right. sorry to show my ignorance but where do i put the "put calculations of this type here" statements. does this then perform different calculations for each type of cabinet in the same table.

                • 5. Re: perform a find on multiple tables
                  philmodjunk

                  Yes, put the calculations you have already defined for the different tables in place of that place holder. (This assumes that you then use a single table in place of the 6 tables.) The case function then controls which calculation is performed.