Lookup data from one table to another that share a common third table.
I have three tables, Coins, Type and Mintage. My relationships are as follows: Coins::TypeIDLink >--- Type::TypeID. Through this relationship, individual coins of various and duplicate years (multiple coins with the same years for a particular Type are possible due to other specific coin attributes other than year) can be linked to a particular type.
I also have the relationship of Mintage:: TypeID >--- Type::TypeID. Through this relationship, various unique mintage years and their unique mintages (duplicate years are not possible for a particular type - the mintage for a particular type and year is only one amount) for a particular type are maintained.
Lets say I have four coins with unique attributes (other than year) linked to TypeA, dated 1922, 1923, 1923 and 1925. In a similar manner, lets say I have five coins with uniques attributes (other than year) linked to TypeB, dated 1922, 1926, 1926, 1926 and 1928.
Lets say I have in the mintage file a series of unique years and mintages for those years (and many others) linked to TypeA and also for TypeB types.
When I am in the Coin record for TypeA with a year of 1923, how can I have my layout automatically lookup what the mintage was for TypeA, year 1923 from the Mintage table? The same for any other year and type for any other active coin record?
I thought a join table might be what I need but I couldn't figure that out. Any advice or even better, a demo file, would be appreciated!
I am using FM13 Pro Advanced on a Mac.