Displaying the name instead of the key.

[tim.breeding]

I've got a PO form where I'm selecting names from combo boxes populated by other tables. When the selection is made, the ID# associated with that name is stored in the form's table source.

The problem is, when I run a report(using the report wizard) the ID# is displayed on the report. How would I go about writing a query or whatever else I need to get it to display the name instead of the ID#? What(if any) relationships do I need to build between the tables?

 

[Big Pat]

Hi,

Not familiar with the term PO form, but I think I understand what you're trying to do. The way I've got round this is by basing the report on a query rather than on the table.

In the query, you have your main data table, where the ID# is stored. But you need to add the other table (where it will look up the matching name or description) to the query design too. (In query design, click the add tables icon.)

Next, link the ID# in your main data to the code or primary key in your lookup table. If your relationship is right, Acccess should do this for you, but if not just drag the main data ID# across and drop it onto the relevant field in the other table. This will create the relationship for you.

Choose join type 1 if you want records returned ONLY if there ARE matching values in the other table (this is the default)
Choose join type 2 if you want records returned EVEN IF they contain values that don't match anything in the lookup table (but maybe your design prevents this in the first place)

When you've linked them, add the name or whatever from your lookup table to the query. This should give you the actual text of the name and not just the number.

Finally, bring this new field into your report.

See attchment for an example of a VERY simple query design.