Pivot Table: Lookup Field Problem

[Kristen Ziliak]

I inherited an Access Database and am trying to create a pivot table from a
query. In the Pivot Table, a Lookup field (The lookup table itself has 3
fields, ID, Gender and Long Description) will only display the numberic
primary key (ID) instead of the useful text description (Gender). When I
run the query itself in query mode, the text description Gender displays
but when I pivot the query, the primary key value, ID for the text
description displays... Why?

 

[John Vinson]

I inherited an Access Database and am trying to create a pivot table from a
query. In the Pivot Table, a Lookup field (The lookup table itself has 3
fields, ID, Gender and Long Description) will only display the numberic
primary key (ID) instead of the useful text description (Gender). When I
run the query itself in query mode, the text description Gender displays
but when I pivot the query, the primary key value, ID for the text
description displays... Why?

Why?

Because Microsoft's misleading, misdesigned, thoroughly obnoxious
Lookup misfeature is concealing the actual contents of your table (the
numeric ID) behind a "lookup". The text description DOES NOT EXIST IN
YOUR TABLE. It appears to, but *it's in a different table*.

Whan you do the pivot, you see what is actually in your table - the
ID.

Base the Pivot on a query joining your table to the lookup table.
Select the description field *from the lookup table*, rather than
relying upon the weak reed of the "lookup".