Hi All
I have the following two tables:
Reservations ( ResNo C(6),ClientCode C(5),Amount N(9,2), Date D )
Clients ( Code C(5), ClientName C(30) )
I want to retrieve in one SELECT the number of reservations and the total amount grouped by client, for two time periods ( current and last year ). The current time period will be BETWEEN ( date, ReportFrom, ReportTo ), and the old time period will be BETWEEN( date, OldFrom, OldTo ).
I am trying to run the following select command:

SELECT Clients.ClientName, Count(Reservations.ResNo) as CurRes,;
 sum(Reservations.Amount) as CurAmount,0000 as OldRes,00000.00 as Oldamount ;
 from Reservations, Clients ;
 where Reservations.ClientCode=Clients.Code and ;
   between( Reservations.date, ReportFrom, ReportTo ) ;
 group by 1 ;
union ( ;
SELECT Clients.ClientName, 0000 as CurRes,00000.00 as CurAmount,;
 count(Reservations.ResNo) as OldRes,sum(reservations.amount) as Oldamount ;
 from Reservations, Clients ;
 where Reservations.ClientCode=Clients.Code and ;
   between( Reservations.date, OldFrom, OldTo ) ;
 group by 1 )

I have two problems: first, when running this, some clients come once, and some twice. Also, i tried to put an order by clause, but wherever i put it ( i.e in the end of the fist select ) i get a "Missig ) parentheses" error.

TIA
Jaime