### Title

Help with finding highest value with exceptions

### Post

I have a field "Pile Number" that get assigned to Photo Orders. Once we have ten or eleven orders we assign the next higher pile number. The problem I have is knowing how many orders are in the current pile and when to start with the next number. To complicate the problem we have special orders that always get put in pile 99 so I can't just look up the highest value in the field. Any thoughts on how I can let the user know how many orders are in the last assigned pile excluding pile 99?

Thanks,

John

The simplest solution I can think of is to set up a Pile table with one record for each pile and an auto-entered serial number used to produce the Pile number. Each time that you need a new pile number, you just create a new record in this table and the serial number field provides the needed number. I'd make your pile #99 -99 in this system so that it is smaller than all other values.

You can then set up a relationship that matches records by pile number and then you can count related numbers to determine how many photo records are in a given "pile". There are numerous other ways to count this data as well--such as using a summary report or ExecuteSQL.