Replace value of a foreign key on a report with corresponding value

[Khalil Handal]

Hi,


A query has two tables: table1 , table2
Table2 has a field fkYears (foreign key). A report is based on this query and the report shows the fkYears (a number is shown at the report).
The fkYears is primary key in Table3. Table3 has two fields: pkYears and ScYear.
How can I show the ScYear value on the report instead of the fkYears?

I know I can do this by adding Table3 to the query. Is there a way doing it without adding Table3 to the query?

Khalil

[Bob Fitz]

Is there a way doing it without adding Table3 to the query?

What is your objection to using the query?

[Khalil Handal]

Hi,
I thinks less tables in the query is better performance. Am I wrong ?

[mike60smart]

It is the only way you get the required value by adding the additional table.

The query allows you to limit the number of fields produced.

[Bob Fitz]

Hi,
I thinks less tables in the query is better performance. Am I wrong ?

To be perfectly honest I don't actually know, but I believe that two properly indexed tables in a query would be much faster than using a single table and something like DLookup().

Have you noticed a discernible drop in performance with two tables in the query?

[Gicu]

You don't need a query; open the report in design view, right click on the textbox bound to fkYears and select Changet To and select combo box. Now click the Data tab on the Properties window and click on the builder (...) on the right of the Row Source property. Add your Table 3 in there and set any desired sorting. Finally back in the Properties window set the Columns number to 2, Column Width to 0";2" and you should be done.

Cheers,

[Khalil Handal]

Hi,
Thank you all for sharing your solutions.
So, It is by adding the third table to the query or using a combo box on the report.
Khalil

[June7]

Either way, the table included in report design. Does one perform better? I don't know - you can test it.