In a database for a lending institution, there are Deals and there are Pools. Deals are made up of Pools. Deals go on for a long time while the Pools within them mature and are either paid off or rolled over into another Pool (with a different Pool Number.
I have a union query that stacks the maturing pools above the new pools. An agregate query then summs the total in the union query, grouping on deal, maturing or new, date (maturity date for maturing pools and book entry date for new pools).
A second agregate query repeats the process of the first agregate query but only for the maturing pools.
Another query joins the two agregate queries on deal, date and sum of face value. If all the pools maturing on that date are being paid off, there will be no new pools being added to the sum of the face amounts and the two will be the same, so this query shows all where the pools are being paid off. (I need to work with the sums because it is very common for three pools to be rolled into two or vice versa).
This whole thing works very nicely except for one problem. We have one deal in which there are three pools maturing, two are being rolled into new pools and one is being paid off (this is a rare occurrance). This, of course, doesn't show up because the sums aren't the same. Does anyone have any idea of how I might pick up this type of occurrance?
I have a union query that stacks the maturing pools above the new pools. An agregate query then summs the total in the union query, grouping on deal, maturing or new, date (maturity date for maturing pools and book entry date for new pools).
A second agregate query repeats the process of the first agregate query but only for the maturing pools.
Another query joins the two agregate queries on deal, date and sum of face value. If all the pools maturing on that date are being paid off, there will be no new pools being added to the sum of the face amounts and the two will be the same, so this query shows all where the pools are being paid off. (I need to work with the sums because it is very common for three pools to be rolled into two or vice versa).
This whole thing works very nicely except for one problem. We have one deal in which there are three pools maturing, two are being rolled into new pools and one is being paid off (this is a rare occurrance). This, of course, doesn't show up because the sums aren't the same. Does anyone have any idea of how I might pick up this type of occurrance?
