On a report, how to print the value of a field found in another table

[sms28]

I have table containing a foreign key, contacttypeid. The table store the value 1, for example. The translation for 1 is found in Contact types table in the contactname field. How do I retrieve the value from the other table and place it on my report?

 

[Wocca]

In the underlying query to your report link in the table & include the field.

From the sounds of your question you may not be aware that any report you have has either a table or query for the data source. If you open the report in design mode & open the properties box for the report you will see an item record source, which in your case will probably be the name of a table. You can either change this to the name of a query you have previously created & saved or you can create a query from within the report.

I hope this is of help. Regards
Warwick

 

[randysmid]

Hi,
You can also use a DLookup function without changing your query. Here is an example of the "control source" for the information you want to add to the report:

This example presumes the table is called tblContactTypes,
- the field is called Contacttypeid
- and the field on the report to be used to link is txtContacttypeid
=DLookUp([Contacttypeid]","tblContactTypes","[Contacttypeid]='" & [txtContacttypeid] & "'&quot

In case you need it, here is an example of a DCount function, which is placed in the control source for a text box:
=DCount("[SSN]","tblPersonnel&quot 'counts the number of SSN's found inside the tblPersonnel table
HTH,
Randy Smith
California Teachers Association