Related table picking up 0 values

[enquirerfm]

I have a table which is related to another one. Trying to keep it simple lets say one is a table of cats (first table) which has fields for cat species, colour, weight etc. I can also record the details of when I bought the cat, price paid etc. It is linked to another table where I record all the prices of cats I can find (second table) and they're matched according to species and colour.

I create a report based upon a time period using the first table and by doing a find of records which match certain criteria I wish to includewhich shows certain data for example: min price, max price, av. opening price, av. opening price. The report doesn't need a script it is simply another layout which pulls the data from certain calculations fields some of which are based upon the other table (eg a summary field). The problem is the table (first table) picks up references to any cats given away (eg $0) as part of its minimum search and I only want to include cats whose price is at least $50. Also, I wish to exclude the $0 values in the summaries of average prices in the second table.

I suppose what it comes down to is how can I ensure 'summaries' never include 0 values without just excluding them from the DB altogether (I could do a find on the price field for $0 and then delete all these records).

[comment]

The proper (and easy) way would be to produce the report from the Prices table, where you can perform a find as part of the script that produces the report.

Otherwise you would have to make sure the unwanted records are not related for the purposes of the aggregating calculations. Since clearly you want them to be related for all other purposes, you would have to define another relationship between the two tables. And add a calculation field to the Prices table to return the CatID only if your criteria are met, so that you have a matchfield for the additional relationship. Did I mention the easy way?

[enquirerfm]

Yes, I thought so. Thank you. Actually, I don't need these records so easiest for me is to export them to a file in case I do and then delete them !