For some reason I am struggling with a multi-criteria relationship and it is driving nuts! I am sure someone can put me out of my misery.

I have an order database, and an items database with a sub-table consisting of multiple items each with a quantity and price for discounts.

I have created a multi-criteria relationship between the order item and the discount table consisting of the Item Code (=) and the Qty (>=) in order to lookup the price based on the quantity.

As hard as I try I cannot get a match beyond two values. In other words in my discounts table I have 4 value quantites, 1, 12, 28, and 35. The relationship will bring back the first two but if the quantity is >= 28 it will not return it.

I think I am having a mad moment so can someone point out where my madness lies?

Not sure... but check that the quantity fields are number fields, and that they don't contain white space (spaces, returns, tabs etc).

Thanks for the reply. I have now solved the problem myself. Just for the record just in case it foxes anyone else it was related to the way the data in the sub-table was sorted in the relationship.

Thanks anyway