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.
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.