Text not appearing when group by lookup

  • Thread starter Thread starter GregB
  • Start date Start date
G

GregB

Created a customer table which includes a lookup to
another table which consisting of vendors each customer
might use for a particular product. The vendor table
includes about 20 names.

This works great until I create a report that is grouped
by vendor. When I do that, the report, instead of
displaying the vendor name, displays the corresponding ID
number for each vendor from the vendor table.

Doesn't seem to matter which field in the vendor table is
the primary key, or whether I have no primary key, or if I
remove that ID autonumber field altogether.

Any help?
 
GregB said:
Created a customer table which includes a lookup to
another table which consisting of vendors each customer
might use for a particular product. The vendor table
includes about 20 names.

This works great until I create a report that is grouped
by vendor. When I do that, the report, instead of
displaying the vendor name, displays the corresponding ID
number for each vendor from the vendor table.

Doesn't seem to matter which field in the vendor table is
the primary key, or whether I have no primary key, or if I
remove that ID autonumber field altogether.
Click to expand...

You need the Vendor table in your query and then you can pull the vendor name
field from there. The lookup field is hiding from you the fact that the
Customer table DOES NOT contain the vendor name. It contains the VendorID.
This is one of the main reasons why the use of Lookup fields is discouraged.
 
That's right, a lookup field stores the foreign key to another table, but
displays the value from that other table. But, for a report, you have to
join the other table on that field, and pull down the value into the Query
grid.

Lookup fields are a violation of relational design, obscrure what is
actually stored, and are useful [primarily | only] for a casual end-user who
spends a lot of time looking at tables in datasheet view. My suggestion is
to replace the Lookup field with a simple foreign key field and use Queries,
unless you mostly look at tables in datasheet view.

Larry Linson
Microsoft Access MVP
 
Back
Top