sql server 2005 error

Question

I am new to t-sql in the last few months. I am getting the following error in sql server 2005 and would like to know how to get around the problem:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Z.MaxReceiveDate" could not be bound.
The sql that I am running is the following:
select distinct Z.gnumber, R.Requests, Z.MaxReceiveDate

   from

(select distinct gnumber,
            count(*) as Requests
    from dbo.table1
            where receive_date > Z.MaxReceiveDate
   group by gnumber
        ) R

left join
(select distinct P.gnumber,
             MaxReceiveDate = Max(Received_Date),
             from dbo.table2
    group by P.gnumber
) Z
On Z.HNumber = R.Hnumber
I am bascially trying to use the maxreceivedate value from a subquery.
Can you suggest ways to solve this problem?
Thanks!

Andrew Fenster · Answer

The error is telling you that Z doesn't have a column named MaxReceiveDate. So try this instead: select distinct Z.gnumber R.Requests Z.MaxReceiveDate from (select distinct gnumber count(*) as Requests from dbo.table1 where receive_date > Z.MaxReceiveDate group by gnumber ) R left join (select distinct P.gnumber Max(Received_Date) AS MaxReceiveDate from dbo.table2 group by P.gnumber ) Z On Z.HNumber = R.Hnumber I haven't tried it but I'll bet it works.