Ad hoc queries on two or more tables (Filemaker Pro 7)
I can't seem to find a way to do what ought to be simple queries and table-matches.
I have 6 tables in one database. These are pretty big tables, ranging from 10 to 50 million records in each, for a total of 180 million records. The tables represent active accounts at year end 2006 through year end 2011, and most records appear in more than one table. The tables are easily linked together by the unique ID code for each account.
What I'd like to do is count the accounts that continue to exist from each year to each following year. It would be great to have one master table like this:
ID Descriptor1 Descriptor2 Descriptor3 EOY2006 EOY2007 EOY2008 EOY2008
where the EOYnnnn fields are just 0 or 1, True or False, etc. to indicate if the ID is found in the original EOYnnnn table.
Or, I could live with just having a file of all IDs in 2006 but not 2007, another of IDs in 2007 but not 2006, another of IDs in both 2006 and 2007, and likewise for other pairs of years.
With either of those results I could continue my analysis. The analysis is a one-shot result, and doesn't need to be anything live that auto-updates or anything. Just a few specific ad-hoc numbers need to be pulled out and then I can forget the whole wretched mess.
Either type of solution would have been simple in old neanderthal Paradox via its "query by example" functionality, but Paradox won't handle these big tables, and I can't seem to find a way to do the same in FMP. The only time the Help function mentions table joins and such is in reference to importing, which I can't use.
Thanks for any suggestions!