AnsweredAssumed Answered

Multiplying Numbers From different tables

Question asked by ClaudeTessier on Aug 12, 2014
Latest reply on Aug 13, 2014 by philmodjunk

Like • Show 0 Likes0
Comment • 5

Title

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.

No one else has this question

Outcomes

philmodjunk Aug 12, 2014 3:04 PM

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.

Actions

ClaudeTessier Aug 12, 2014 3:29 PM

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

Actions

disabled_dbail22@comcast.net Aug 12, 2014 9:46 PM

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.

Actions

ClaudeTessier Aug 12, 2014 10:36 PM

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.

Actions

philmodjunk Aug 13, 2014 10:27 AM

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.

Actions

5 Replies

philmodjunk Aug 12, 2014 3:04 PM
Mark Correct
Correct Answer
     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.
Like • Show 0 Likes0
Actions
ClaudeTessier Aug 12, 2014 3:29 PM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
disabled_dbail22@comcast.net Aug 12, 2014 9:46 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
ClaudeTessier Aug 12, 2014 10:36 PM
Mark Correct
Correct Answer
     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.
Like • Show 0 Likes0
Actions
philmodjunk Aug 13, 2014 10:27 AM
Mark Correct
Correct Answer

          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.
Like • Show 0 Likes0
Actions

Multiplying Numbers From different tables

Title

Post

Outcomes

Related Content

Recommended Content