Function comparing rows

[Dharmesh]

Hi I Have a nested query which runs fine in access. However when ever I try to save it my Access crashes.

SELECT j.CentreNumber
,j.NumObsGroup
,j.Tours
,j.Deals
,j.AvgConversion
,j.PctOccupancy
,j.AvgDiscount
,j.[Weighted Tours]
,j.[Weighted T-D]
,j.[Enough Deals?]
,IIf([MinOfNumObsGroup] IsNullAnd [MaxLastTours] IsNull,0,1)AS [Use Flag]
FROM (SELECT d.*
,c.MinOfNumObsGroup
FROM ((SELECT a.CentreNumber
,Min(a.NumObsGroup)AS MinOfNumObsGroup
FROM (
SELECT b.CentreNumber
,b.NumObsGroup
,First(b.[Enough Deals?])AS [FirstOfEnough Deals?]
FROM
(SELECT Dev_GPP_tourchunks.CentreNumber
,Dev_GPP_tourchunks.NumObsGroup
,Dev_GPP_tourchunks.Tours
,Dev_GPP_tourchunks.Deals
,Dev_GPP_tourchunks.AvgConversion
,Dev_GPP_tourchunks.PctOccupancy
,Dev_GPP_tourchunks.AvgDiscount
,[Dev_GPP_tourchunks]![Tours]*(1-IIf(0>(IIf([Dev_GPP_tourchunks]![PctOccupancy]<1,[Dev_GPP_tourchunks]![PctOccupancy],1)-[Dev_GPP_Assumptions]![Occupacy at which T:D starts to drop]),0,(IIf([Dev_GPP_tourchunks]![PctOccupancy]<1,[Dev_GPP_tourchunks]![PctOccupancy],1)-[Dev_GPP_Assumptions]![Occupacy at which T:D starts to drop]))*[Dev_GPP_Assumptions]![Tour defferential for occupancy]*100)AS [Weighted Tours]
,[Dev_GPP_tourchunks]![Deals]/[Weighted Tours] AS [Weighted T-D]
,IIf([Dev_GPP_tourchunks]![Deals]>=[Dev_GPP_Assumptions]![Required Deals],1,0)AS [Enough Deals?]
FROM Dev_GPP_tourchunks, Dev_GPP_Assumptions ORDERBY Dev_GPP_tourchunks.CentreNumber, Dev_GPP_tourchunks.NumObsGroup)AS b GROUPBY b.CentreNumber, b.NumObsGroup HAVING (((First(b.[Enough Deals?]))=1)))AS a
GROUPBY a.CentreNumber)c
)
RIGHTJOIN(SELECT Dev_GPP_tourchunks.CentreNumber, Dev_GPP_tourchunks.NumObsGroup, Dev_GPP_tourchunks.Tours, Dev_GPP_tourchunks.Deals, Dev_GPP_tourchunks.AvgConversion, Dev_GPP_tourchunks.PctOccupancy, Dev_GPP_tourchunks.AvgDiscount, [Dev_GPP_tourchunks]![Tours]*(1-IIf(0>(IIf([Dev_GPP_tourchunks]![PctOccupancy]<1,[Dev_GPP_tourchunks]![PctOccupancy],1)-[Dev_GPP_Assumptions]![Occupacy at which T:D starts to drop]),0,(IIf([Dev_GPP_tourchunks]![PctOccupancy]<1,[Dev_GPP_tourchunks]![PctOccupancy],1)-[Dev_GPP_Assumptions]![Occupacy at which T:D starts to drop]))*[Dev_GPP_Assumptions]![Tour defferential for occupancy]*100)AS [Weighted Tours], [Dev_GPP_tourchunks]![Deals]/[Weighted Tours] AS [Weighted T-D], IIf([Dev_GPP_tourchunks]![Deals]>=[Dev_GPP_Assumptions]![Required Deals],1,0)AS [Enough Deals?]
FROM Dev_GPP_tourchunks, Dev_GPP_Assumptions
ORDERBY Dev_GPP_tourchunks.CentreNumber, Dev_GPP_tourchunks.NumObsGroup
)d 
ON (c.CentreNumber=d.CentreNumber)
AND(c.MinOfNumObsGroup=d.NumObsGroup)
)j 
LEFTJOIN(SELECT g.CentreNumber
,IIf([MinOfNumObsGroup] IsNull,40,0)AS MaxLastTours
FROM (SELECT f.CentreNumber
,Min(f.NumObsGroup)AS MinOfNumObsGroup
FROM
(SELECT e.CentreNumber
,e.NumObsGroup
,First(e.[Enough Deals?])AS [FirstOfEnough Deals?] 
FROM
(SELECT Dev_GPP_tourchunks.CentreNumber
,Dev_GPP_tourchunks.NumObsGroup
,Dev_GPP_tourchunks.Tours
,Dev_GPP_tourchunks.Deals
,Dev_GPP_tourchunks.AvgConversion
,Dev_GPP_tourchunks.PctOccupancy
,Dev_GPP_tourchunks.AvgDiscount
,[Dev_GPP_tourchunks]![Tours]*(1-IIf(0>(IIf([Dev_GPP_tourchunks]![PctOccupancy]<1,[Dev_GPP_tourchunks]![PctOccupancy],1)-[Dev_GPP_Assumptions]![Occupacy at which T:D starts to drop]),0,(IIf([Dev_GPP_tourchunks]![PctOccupancy]<1,[Dev_GPP_tourchunks]![PctOccupancy],1)-[Dev_GPP_Assumptions]![Occupacy at which T:D starts to drop]))*[Dev_GPP_Assumptions]![Tour defferential for occupancy]*100)AS [Weighted Tours]
,[Dev_GPP_tourchunks]![Deals]/[Weighted Tours] AS [Weighted T-D]
,IIf([Dev_GPP_tourchunks]![Deals]>=[Dev_GPP_Assumptions]![Required Deals],1,0)AS [Enough Deals?]
FROM Dev_GPP_tourchunks, Dev_GPP_Assumptions 
ORDERBY Dev_GPP_tourchunks.CentreNumber, Dev_GPP_tourchunks.NumObsGroup)AS e GROUPBY e.CentreNumber, e.NumObsGroup HAVING (((First(e.[Enough Deals?]))=1)))AS f
GROUPBY f.CentreNumber
)h
RIGHTJOIN(SELECT Dev_GPP_tourchunks.CentreNumber
,Dev_GPP_tourchunks.NumObsGroup
,Dev_GPP_tourchunks.Tours
,Dev_GPP_tourchunks.Deals
,Dev_GPP_tourchunks.AvgConversion
,Dev_GPP_tourchunks.PctOccupancy
,Dev_GPP_tourchunks.AvgDiscount
,[Dev_GPP_tourchunks]![Tours]*(1-IIf(0>(IIf([Dev_GPP_tourchunks]![PctOccupancy]<1,[Dev_GPP_tourchunks]![PctOccupancy],1)-[Dev_GPP_Assumptions]![Occupacy at which T:D starts to drop]),0,(IIf([Dev_GPP_tourchunks]![PctOccupancy]<1,[Dev_GPP_tourchunks]![PctOccupancy],1)-[Dev_GPP_Assumptions]![Occupacy at which T:D starts to drop]))*[Dev_GPP_Assumptions]![Tour defferential for occupancy]*100)AS [Weighted Tours]
,[Dev_GPP_tourchunks]![Deals]/[Weighted Tours] AS [Weighted T-D], IIf([Dev_GPP_tourchunks]![Deals]>=[Dev_GPP_Assumptions]![Required Deals],1,0)AS [Enough Deals?]
FROM Dev_GPP_tourchunks, Dev_GPP_Assumptions
ORDERBY Dev_GPP_tourchunks.CentreNumber, Dev_GPP_tourchunks.NumObsGroup
)g
ON h.CentreNumber = g.CentreNumber
GROUPBY g.CentreNumber, IIf([MinOfNumObsGroup] IsNull,40,0)
)i
ON (j.NumObsGroup = i.MaxLastTours)
AND(j.CentreNumber = i.CentreNumber)
ORDERBY j.CentreNumber
,j.NumObsGroup;

All tables are linked tables to sql server.
Any Ideas why?
Kind Regards
D

[Stingaway]

What kind of error do you get when it crashes?