6 Replies Latest reply on Aug 13, 2013 5:47 AM by MichaelChenetz

    How to go about querying multiple tables for info

    MichaelChenetz

      Title

      How to go about querying multiple tables for info

      Post

           Table1:

           --------------------------

           product                      subproduct

           -------------------------------------------------------------------------------------

           prod 1                         subproduct1                10

           prod1                          subproduct2                10

           prod2                          subproduct1                 10

            

           table2

           ------------------------------------

           product                 quantity

           ------------------------------------------------------

           prod1                        100

           prod2                           50

            

           What i want to figure out is if i get 100 of prod1 specified in table2, how would i calculate all of the subproducts that would equate to from table1?

            

           Thanks in advance for your help.

        • 1. Re: How to go about querying multiple tables for info
          philmodjunk

               Not sure if this is what you want, but if you set up this relationship:

               Table2-----<Table1

               Table2::product = Table1::product

               Then, from a layout based on table 2, sum( Table1::NumberFIeld ) --> you didn't show a field name for the third column

               A summary field defined in table1 that computes the total fo this same field can also be placed on the Table2 layout to show the same total.

               There are also ways to use ExecuteSQL to produce the same total, but without the need to define a relationship in Manage | Database | Relationships.

          • 2. Re: How to go about querying multiple tables for info
            MichaelChenetz

                 Thanks for your help... I am not sure if that is what i want to do.

                 basically if it was product 1 then i would calculate:

                 prod1 = 100

                 totalproduct 1(That is what i will call that third column) = prod1 * subproduct1

                 totalproduct2 = prod1 * subproduct2

                  

                 I want it to calculate this for every subproduct baased on the parent product.

                  

                 Thanks again for your help.

                  

                  

            • 3. Re: How to go about querying multiple tables for info
              philmodjunk

                   I don't see how you are getting a result of 100 for product 1. You have two records in table 1 with prod1, each with a value of 10 in an unamed 3rd column. Then you show a value of 100 in the Quantity field for Prod1 in table2.

                   In table 1, 3rdColumn * Table2::Quantity ---> 1000 or 100 * 10.

              • 4. Re: How to go about querying multiple tables for info
                MichaelChenetz

                     Sorry... i meant to say prod1=10

                      

                     The result should be 1000

                • 5. Re: How to go about querying multiple tables for info
                  philmodjunk

                       Then my last post describes what you want here.

                       given this relationship:

                       Table2-----<Table1

                       Table2::product = Table1::product

                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                       Table 1, with added calculation field, would look like this:

                       product                      subproduct                Field3                  CalculationField: Field3 * Table2::Quantity
                       ---------------------------------------------------------------------------------------------------------------------------------------------------------
                       prod1                         subproduct1                10                      1000
                       prod1                         subproduct2                10                      1000
                       prod2                         subproduct1                10                       500

                       And I would define Table2::Product as an auto-entered serial number.

                  • 6. Re: How to go about querying multiple tables for info
                    MichaelChenetz

                         Awesome! Thanks for your help.