Simple report showing summaries does not work
I am evaulating Filemaker pro and cannot get the following simpe report to work:
I have 2 tables: Table 1 Assets - fields asset code and market
Table2 Valuations - fields asset code, date and value (ie multiple valuations for same asset code for different dates)
Table 1 and 2 are related by asset code
I would like to produce a report showing the asset code, latest value for the asset and market. To be sorted by market showing subtotal by market.
If I do a layout based on Table 2 - i correctly get subtotals but all values are shown (ie for previous dates)
If I do a layout based on Table 1 - i get only the latest value of the asset but the subtotal (in the subsummary part) shows only the value of the last record reported ie not all total of the values of the all the assets in that market.
I would also like to produce this report as at a date in the past, showing the most recent values related to the report date bt cannot even get the basic report right after many hours of struggling.
Pease help - this would be a basic SQL join between the two tables in any relational database reporting tool.