Jump to content

Related table picking up 0 values

This topic is 2967 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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).

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This topic is 2967 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.