5 Replies Latest reply on Aug 13, 2014 10:27 AM by philmodjunk

    Multiplying Numbers From different tables

    ClaudeTessier

      Title

      Multiplying Numbers From different tables

      Post

           I am new to Filemaker Pro, but have done a few databases using Access. The problem I am having is trying to summarize numbers from 3 different tables. I have 3 tables that I am working with.

           The first table contains 700 records, fields are  "Unit" (unique text), "Description", "Winter Rate" (per cent), "Unit of Measure", and "Group"

           The second table contains 3000 records fields are "Area", "Unit", "Description", and "Price". I have different 4 different prices for the same unit in 4 different areas.

           The third table contains 1200 records "Project", "Unit", and "Quantity". Different projects would require different quantities of unit to construct.

           The view I want would have the "Project Name", "Area", "Unit", "Description", "Quantity", "Price", "Price X Quantity", "Total Cost of Project for each area"

           Tried a few different methods but none have worked so far. When it has given me the "Price X Quantity" figure, it is just for the first "area" records in the second table.

        • 1. Re: Multiplying Numbers From different tables
          philmodjunk

               I don't see how the first table is involved in producing the results that you want. It would seem that the data you need for your result is all specified in tables 2 and 3.

               It looks like you need to match records between table 2 and 3 like this:

               Table 3::Area = Table 2::Area AND
               Table 3::Unit = Table 2::Unit

               This assumes that combining the values of Area and Unit in Table 2 produces a value that is unique to only one record in Table 2.

               Then you can reference or copy a price from Table 2 in order to calculate a cost in Table 3:

               Quantity * Table 2::Price

               The relationship controls which record in Table 2 provides the needed price for this calculation.

          • 2. Re: Multiplying Numbers From different tables
            ClaudeTessier

                 Hi Phil

                 My table 3 currently has no "Area" field in it, not sure where to put what you sent I have include a screen shot of all 3 tables. Now that I look at, would a "-" in some of the units cause problems

            • 3. Re: Multiplying Numbers From different tables
              dbail22@comcast.net

                   Since it looks like "Unit" is the key field between the files you need to create relationships between all of the files using that field.  Once done your calculations should be easy and displaying them in a portal or report also easy. 

              • 4. Re: Multiplying Numbers From different tables
                ClaudeTessier

                     Some times you have to stop overthinking the problem. The table which had 4 different prices and units repeating was the problem. I changed the table by adding columns for the different prices. In the old table a record had a "Price", "Unit", and an "Area". The new table has "Unit", "Area 1 Price", "Area 2 Price", "Area 3 Price", and "Area 4 Price". Works perfect now.

                     Thanks for everyone's help.

                      

                • 5. Re: Multiplying Numbers From different tables
                  philmodjunk
                       

                            The new table has "Unit", "Area 1 Price", "Area 2 Price", "Area 3 Price", and "Area 4 Price". Works perfect now.

                       The draw back to that approach is that adding a 5th Area requires a design change to your database. With other methods, the same change is a data entry task--but it would require you to specify the area or area's for which you want prices.