I am fairly new to FileMaker, although not new to Relational Databases. I have most recently worked with SQL Server and am fairly conversant in scripting and in creating complex queries with SQL. I have to say, adapting to the paradigm shift that is FMP is an ongoing challenge.
I am currently contracted with a company to clean up their FMP data as well as improve on their database structure and bring additional elements into the database. One of my primary objectives is as follows:
I have created a table and am populating it via ODBC, which is comprised of Client IDs, Encounter Dates and multiple fields that contain numeric survey scores. Each client will have multiple encounter dates in the table, but the data set needs to contain ONLY the oldest and the most recent encounter dates, as well as the scores for each of those dates.
The first report required has to include Encounter dates and the scores for the current quarter, but also the baselines (oldest Encounter date) for those same clients. These baseline dates can be as close as a year and as far out as 3 years ago, it's very random.
The second report is to include a single line for each client, but with the baseline and the most current data side by side, with calculations that show improvement or decline.
I have created a table that currently includes the client id and the minimum encounter date, and one with the client id and the maximum encounter date. The idea was to create a join table of some sort that would hold the data set. But apparently I am unable to join them with the table containing my scores by using the client id and encounter date because it won't allow me to join using multiple fields. It keeps wanting to throw up a copy of my data table and what is that about??? Don't know why I am having so much trouble seeing my way through to a solution, but there it is.
I hope someone can point me in the right direction.