> Steve
>
> Hmmm. This is getting complicated. I think your second select requires a join
>
>

> SELECT A.ddate,;
> B.Block,;
> C.cname as name,;
> C.narea as area,;
> ROUND(iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,C.narea*(43560/(C.nspx*C.nspy)),C.nrdmPlants),2) as Plants,; 
> ROUND(D.plants,2) as TotPlants,;
> ROUND((A.nmen/D.plants)* iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,ROUND(C.narea*(43560/(C.nspx*C.nspy)),0),C.nrdmPlants),2) as Spread, ;
> C.nspx as spx,C.nspy as spy,A.nmen as Men;
> FROM Dailywork2 A ;
> Inner Join Block2 B ;
> On A.cblock=B.block ;
> Left Outer JOIN ;
> BlockProfile C ON A.cblock = C.cparent;
> Left OUTER Join ;
> (select C.cparent,sum(iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,C.narea*(43560/(C.nspx*C.nspy)),0))+(SUM(C.nrdmPlants))as Plants ;
> FROM BlockProfile C  ;
> LEFT OUTER JOIN ;
> Dailywork2 A ;
> on C.cparent=A.cblock ;
> group by cparent WHERE BETWEEN(A.ddate ,C.dstart,C.dend)) D ;
> on D.cparent=A.cblock ;
> WHERE BETWEEN(A.ddate ,C.dstart,C.dend)

>
> Regards
> Tushar
> >
> > Hi Ken,
> >
> > Sorry about the rating, I have always been doing this. It's just that I needed to add 1 additional bit of code myself, so I was wondering whether it was appropriate to rate the final outcome, or the route to the end. Although in all fairness, I have to conclude that without the route there is no end. Tushar has helped me a great deal, and I am immensely grateful.
> >
> > Meanwhile, I've come across one more hitch I'm trying to resolve, which I oversaw earlier. I'll get back on this case in a short while.
> >
> > Steve
> >
> >
> > Well, really sorry, I had not realized this earlier, but the result is showing a continuous flow of the same data.Although the date field is changing, the block field remains the same throughout.All the other fields are linking to the block field in pairs (that is the 2 record relationship with block), but the results are continuous, and the same.
> >
> > Please excuse me for this! But I yet have to resolve the problem.
> >
> > Steve


No Luck, Tushar, Sorry.

I've remodelled the query again into two distinct selects, one with Plants and another with sum(plants). The second select works fine when the 'between' clause is not incorporated, but is obviously giving me a sum of plants of the entire table, and not of the period specified. The moment I incorporate the 'between', I get irregulated results of the sum. Basically, the between function should fire by linking on to the dailywork table immediate record and doing a sum of plants there.

My Code:

SELECT Temp1.Ddate,Temp1.cblock,Temp1.cName,temp1.plants,Temp1.Men,Temp2.Plants as totPlants;
from;
(SELECT A.ddate,A.cblock,C.cname,;
ROUND(iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,C.narea*(43560/(C.nspx*C.nspy)),C.nrdmPlants),2)  as Plants,; 
C.nspx as spx,C.nspy as spy,A.nmen as Men;
FROM Dailywork2 A;
Left Outer JOIN BlockProfile C;
ON A.cblock = C.cparent;
and BETWEEN(A.ddate,C.dstart,C.dend)) temp1;
LEFT outer Join;
(select cparent, sum(iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,C.narea*(43560/(C.nspx*C.nspy)),0))+(SUM(C.nrdmPlants))as Plants ;
FROM BlockProfile C group by cparent ) Temp2;
ON temp1.cBlock=temp2.cparent


* where BETWEEN(A.ddate,C.dstart,C.dend)

Thanks a lot, Tushar,

Regards,

Steve.