Complex Query - need help

  • Thread starter Thread starter R Avery
  • Start date Start date
R

R Avery

My problem is allocating $ to general partners that all have shares in
different Pools of money.

Table Name: PctSharing
Fields: Partner ID, Pool ID, Effective Date, Pct Ownership

Table Name: PoolEarnings
Fields: Pool ID, Earning Date, Amount

The Pct Ownership of a particular partner can change at Quarter-Ends
(but usually only occurs once a year), but at any point in time, the
total Pct Ownership across all Partners must be 100% in each Pool.
Every quarter, each Pool has earnings which must be distributed
according to the last Pct Ownership available.

How do I construct a crosstab query that will have Partners as the
ColumnHeading, Earning Date as the RowHeading, and Amount as the value
(where Amount is appropriately scaled by the Partner's Pct Ownership)?
Any help would be most appreciated. Thanks!
 
I think you need to do this with two queries. The first I have called MaxDate which gets the latest PCT Ownership for Pool for each partner. The code needed for this is very simple

SELECT [PCT Sharing].[Partner ID
, [PCT Sharing].[Pool ID
, Max([PCT Sharing].[Effective Date]) AS [Effective Date

FROM [PCT Sharing

GROUP BY [PCT Sharing].[Partner ID], [PCT Sharing].[Pool ID]

The Crosstab query then uses this result and should look something like this

Transform Sum(Split1) as Spli

Select [Earning Date], [Pool ID

fro

(SELECT PoolEarnings.[Earning Date], [PCT Sharing].[Partner ID], [PCT Sharing].[Pool ID
, [PCT Sharing].[PCT Ownership], PoolEarnings.Amoun
, [PCT Sharing].[PCT Ownership]*PoolEarnings.Amount as Split

from [PCT Sharing]
, MaxDat
, PoolEarning

where [PCT Sharing].[Effective Date] = MaxDate.[Effective Date
and [PCT Sharing].[Partner ID] = MaxDate.[Partner ID
and [PCT Sharing].[Pool ID] = MaxDate.[Pool ID
and [PCT Sharing].[Pool ID] = PoolEarnings.[Pool ID
and PoolEarnings.[Earning Date] = #30/04/2004# ) Sub

Group by [Earning Date], [Pool ID

Pivot [Partner ID

I have assumed that there is a single earning date entered at the end of the quarter but you can adjust this as neccessary. Also I have included the Pool ID in the results. If you can don't want to show the Pool ID remove it from the first Select and from the Group by Statements

Hope this Help
Rowan
 
Rowan said:
I think you need to do this with two queries. The first I have called MaxDate which gets the latest PCT Ownership for Pool for each partner. The code needed for this is very simple:

SELECT [PCT Sharing].[Partner ID]
, [PCT Sharing].[Pool ID]
, Max([PCT Sharing].[Effective Date]) AS [Effective Date]

FROM [PCT Sharing]

GROUP BY [PCT Sharing].[Partner ID], [PCT Sharing].[Pool ID];

The Crosstab query then uses this result and should look something like this:

Transform Sum(Split1) as Split

Select [Earning Date], [Pool ID]

from

(SELECT PoolEarnings.[Earning Date], [PCT Sharing].[Partner ID], [PCT Sharing].[Pool ID]
, [PCT Sharing].[PCT Ownership], PoolEarnings.Amount
, [PCT Sharing].[PCT Ownership]*PoolEarnings.Amount as Split1

from [PCT Sharing]
, MaxDate
, PoolEarnings

where [PCT Sharing].[Effective Date] = MaxDate.[Effective Date]
and [PCT Sharing].[Partner ID] = MaxDate.[Partner ID]
and [PCT Sharing].[Pool ID] = MaxDate.[Pool ID]
and [PCT Sharing].[Pool ID] = PoolEarnings.[Pool ID]
and PoolEarnings.[Earning Date] = #30/04/2004# ) Subq

Group by [Earning Date], [Pool ID]

Pivot [Partner ID]

I have assumed that there is a single earning date entered at the end of the quarter but you can adjust this as neccessary. Also I have included the Pool ID in the results. If you can don't want to show the Pool ID remove it from the first Select and from the Group by Statements.

Hope this Helps
Rowan
Click to expand...
 
Excellent! Thanks, Rowan.

Now suppose I want to do something a even more complex. What if I want
to get a list of the partner's share in not just the latest earnings
date, but across all earnings dates? That would require the calculation
of a the latest PCT Sharing as of each PoolEarnings.[Earning Date]. The
MaxDate query would also have to be modified to accept a parameter
AsOfDate and return the Max([PCT Sharing].[Effective Date]) for all
records before AsOfDate.

The part I am stuck on right now is getting the query to call the
MaxDate query for each [Earning Date], passing the [Earning Date] as the
parameter to MaxDate. Can this be done?
 
You would normally do this by using the same variable (say AsOfDate) in both queries. This could be added to the MaxDate query with the line

WHERE [Effective Date] <= AsOfDat

You would then use the same variable to replace the hadcoded date in the main query and on running this you should be promted for the variable once for both queries

Unfortunately this does not seem to work with CrossTab queries. I am getting an error saying the Micorsoft Jet Engine does not recognise the Variable as a valid field name. I'll have a little play around with it and see what I can do

Regard
Rowan
 
Back
Top