>Dear Cetin,
>
>Thank you very much for the advice. I have included a snippet of code, so that you can actually see what I have done. This code DOES work, of which I am very happy with.
>
>SELECT product.productname, country.countryname, company.compname, ;
> city.cityname, Capacity.year, Capacity.npcapacity, ;
> Capacity.tempplant ;
> FROM dcidatabase!capacity ;
> INNER JOIN dcidatabase!product;
> ON capacity.productcode = product.productcode ;
> INNER JOIN dcidatabase!country ;
> ON Country.countrycode = val(left(capacity.tempplant,2)) ;
> INNER JOIN dcidatabase!company ;
> ON company.compcode = val(substr(capacity.tempplant,3,3)) ;
> INNER JOIN dcidatabase!city ;
> ON city.citycode=val(right(capacity.tempplant,3)) ;
> WHERE country.countrycode=80 AND;
> product.productcode=3
>
>However, there is a slight problem - I have two more tables that must be included. These tables (region & state) need to be joined with country. Is this possible? Thank you again for your help.
>
>Jeff
Jeff,
As I could see you're currently using indirect relations so with this approach you could include region and state too. If gets too complex you could first create a view (or plain SQL with nofilter) for country,region,state and use that view as if it was country table. You strategy would depend if you're going to create updatable view(s) or just for viewing data purposes.
Cetin