Getting a different description for 2 fields

[Cris]

I have a table named FINCAS that contains 2 fields named:
CODPOS and CODPOSP that store the zip-code of a house and the zip-code of the owner of the house.
This values are often distinct.
In my report, I show the information of FINCAS and I have to put the name of the country associated to CODPOS and CODPOSP.
The names of the countries are in a table named PROVIN whith two fields: CEPROVIN and DEPROVIN
CEPROVIN contains the two first digits of the zip-code.
So I have to query PROVIN table 2 times, the first with the two first digits of CODPOS and the second with the two first digits of CODPOSP.

What is the way to do this?
Thank you.

 

[MalcolmW]

Some assumptions:
country actually means state or province
zip code actually means zip/postal code
I'm guessing that you used some translation software on your question.
Because your table is named PROVIN (not STATES), I guess you are using Postal CODes (characters, not digits) and provinces.
You will have to add another copy of the PROVIN table to your report, so that you have one copy to use to look up the postal code for the house, and another copy to look up the postal code for the house owner. When you add the second PROVIN table, you will be prompted to provide an alias, such as PROVIN2.
The next problem is how to link these tables. Because you are linking on a substring of a field, it will not be efficient.
In Database|Show SQL Query, you can go in and edit the SQL statement to link FINCAS to PROVIN, and FINCAS to PROVIN2.
...
WHERE SUBSTR(FINCAS.codpos, 1, 2) = PROVIN.CEPROVIN
AND SUBSTR(FINCAS.codposp, 1, 2) = PROVIN2.CEPROVIN
... Malcolm
wynden@telus.net

 

[kenhamady]

Add the CEProvin table twice, the second time it will ask you to give it an alias to distinguish them. Then link one field to one instance, and the other field to the other instance.

The partial link, however, is not supported in many environments. Crystal does not allow you to link on an expression. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

Thanks Ken - I should make it clear that the Visual Linker is not of any use for this, and should be ignored.
The Visual Linker is very limited (isn't that a polite way to put it? ) - we have a bit more flex when we edit the SQL statement directly. So even if a partial link is not supported in the Visual Linker, it can be done in the SQL statement.
This assumes that you are using either ODBC or a client server database. If you are using a local database, you will need to use ODBC for this trick. Otherwise, you will not have a SQL statement to edit.
Malcolm
wynden@telus.net