Hi Fabio

Thanks for the code. Unfortunately, it does not return the correct data. Not sure why. I had similar problems which is why I was built the two view sample I posted. The view you posted returns the same value for all prices and a zero rate which I found somewhat baffling so I tried removing the SUMming and discovered that It was returning the same record a number of times but this still didn't explain why all the prices were the same. I will look at it further and see what I can come up with.

I had discovered some documentation on subqueries and thought I would give it a go but have discovered that apparently this will not work until VFP9. It was this feature that I was trying to get going rather than trying to condense the view by summing/grouping. If you know of any way I subquery a view I would very much appreciate anything you can tell me.

I will have a play with the view again and see if I can work something along the lines you have done.

Thanks once again
Geoff

>if code is a PK try this:
>
>

>lcCustPrsched='CU02'
>lcContPrsched='CO03'
>lcProdserv=INPUTBOX('Enter Product Code','Product Pricing','AGE100')
>CREATE SQL VIEW vu_Pricing as ;
>		SELECT  pricebase.prodserv	;
>			,	prsched.baserate	;
>			,	SUM(IIF(pricebase.code = ?lcCustPrsched ,pricebase.rate 	,0.000) )	rate ;
>			,	SUM(ROUND(IIF(pricesched.type=='%'	;
>						,	rate*IIF(pricebase.code = ?lcCustPrsched ,pricesched.urgent	* 0.01	,1	);
>						,	rate+IIF(pricebase.code = ?lcCustPrsched ,pricesched.urgent			,0.0)),2))	dprice1	;
>			,	SUM(ROUND(IIF(pricesched.type=='%'	;
>						,	rate*IIF(pricebase.code = ?lcCustPrsched ,pricesched.general * 0.01	,1	);
>						,	rate+IIF(pricebase.code = ?lcCustPrsched ,pricesched.general 		,0.0)),2))	dprice2	;
>
>			,	SUM(ROUND(IIF(pricesched.type=='%'	;
>						,	rate*IIF(pricebase.code = ?lcCustPrsched ,pricesched.planned * 0.01	,1	);
>						,	rate+IIF(pricebase.code = ?lcCustPrsched ,pricesched.planned		,0.0)),2))	dprice3	;
>
>			,	SUM(ROUND(IIF(pricesched.type=='%'	;
>						,	rate*IIF(pricebase.code = ?lcContPrsched ,pricesched.urgent	* 0.01	,1	);
>						,	rate+IIF(pricebase.code = ?lcContPrsched ,pricesched.urgent			,0.0)),2))	cprice1	;
>			,	SUM(ROUND(IIF(pricesched.type=='%'	;
>						,	rate*IIF(pricebase.code = ?lcContPrsched ,pricesched.general * 0.01	,1	);
>						,	rate+IIF(pricebase.code = ?lcContPrsched ,pricesched.general 		,0.0)),2))	cprice2	;
>
>			,	SUM(ROUND(IIF(pricesched.type=='%'	;
>						,	rate*IIF(pricebase.code = ?lcCustPrsched ,pricesched.planned * 0.01	,1	);
>						,	rate+IIF(pricebase.code = ?lcCustPrsched ,pricesched.planned		,0.0)),2))	cprice3	;
>			FROM prsched ;
>				LEFT JOIN pricebase ON prsched.baserate == pricebase.basecode ;
>				LEFT JOIN pricesched ON prsched.code+pricebase.prodserv == pricesched.prsched+pricesched.prodserv ;
>			WHERE pricebase.prodserv = ?lcProdserv AND prsched.code IN (?lcCustPrsched,?lcContPrsched)
>			GROUP BY pricebase.prodserv
>USE wots!vu_Pricing
>BROWSE
>