Hi Steve,

You could open tables LOOKLID & LOOKRYHM twice each with different ALIASES.

Try something like:

SELECT a.*, c1.lookdesc1, c2.lookdesc1 ;
FROM a, looklid c1, looklid c2 ;
WHERE c1.lookcode1 = a.lid_etdose ;
AND c2.lookcode1 = a.lid_ivdose ;
INTO CURSOR rpt

Hope this helps.

Regards,
Gavin...


>My only question is - look at the first two JOIN commands and the last two JOIN commands. I am trying to lookup up a value in a table and translate a code in the table to a string of text. How do I use the same table to lookup a code for each field that needs translating from a code? Thanks ahead of time for the help.
>
>Here is my code:
>
>

>	SELECT a.edate, a.incident, a.name_last, ;
>		a.name_first, a.ptwt, a.un_circum, ;
>		a.epi_etdose, a.epi_ivdose, a.epi_seq, a.epi_seqb, ;
>		a.atr_etdose, a.atr_ivdose, a.atr_seq, a.atr_seqb, ;
>		a.lid_etdose, a.lid_ivdose, a.lid_seq, a.lid_seqb, ;
>		a.flu_ivdose, a.flu_seq, a.flu_seqb, ;
>		a.cor_ivdose, a.cor_seq, a.cor_seqb, ;
>		a.nar_etdose, a.nar_ivdose, a.nar_seq, a.nar_seqb, ;
>		a.dop_ivdose, a.dop_seq, a.dop_seqb, ;
>		a.bic_ivdose, a.bic_seq, a.bic_seqb, ;
>		a.cal_ivdose, a.cal_seq, a.cal_seqb, ;
>		a.md_initial, a.md_final1, a.ems_outcom, a.amivl_time, ;
>		a.ammed_time, a.amaps_time, a.fdaps_time, a.hosed_time, ;
>		a.roscstart1, a.roscend1, a.roscstart2, a.roscend2, ;
>		b.incident, b.shocks, ;
>		c.lookdesc1, d.lookdesc2, e.lookdesc3, f.lookdesc4, g.lookdesc5, ;
>		h.lookdesc6, i.lookdesc7;
>		FROM FORCE arrest a, shocked b ;
>		LEFT JOIN \data\sca\looklid c ON (a.lid_etdose) = (c.lookcode1) ;
>		LEFT JOIN \data\sca\looklid c ON (a.lid_ivdose) = (c.lookcode1) ;
>		LEFT JOIN \data\sca\lookflu d ON (a.flu_ivdose) = (d.lookcode2) ;
>		LEFT JOIN \data\sca\lookcor e ON (a.cor_ivdose) = (e.lookcode3) ;	
>		LEFT JOIN \data\sca\lookdop f ON (a.dop_ivdose) = (f.lookcode4) ;
>		LEFT JOIN \data\sca\lookbic g ON (a.bic_ivdose) = (g.lookcode5) ;
>		LEFT JOIN \data\sca\lookcal h ON (a.cal_ivdose) = (h.lookcode6) ;
>		LEFT JOIN \data\sca\lookrhym i ON (a.md_initial) = (i.lookcode7) ;
>		LEFT JOIN \data\sca\lookrhym i ON (a.md_final1) = (i.lookcode7) ;
>		WHERE a.edate >= lcStartDate AND a.edate <= lcEndDate AND ;
>			a.incident = b.incident ;
>		ORDER BY a.edate, a.incident ;
>		INTO CURSOR rpt
>		SET ANSI OFF
>	IF THISFORM.cboOutputTo.Value = "S"
>		REPORT FORM \arrest\reports\medsgivn.frx PREVIEW IN SCREEN
>	ELSE
>		REPORT FORM \arrest\reports\medsgivn.frx TO PRINT
>	ENDIF
>