Pivot Table: Lookup Field Problem

  • Thread starter Thread starter Kristen Ziliak
  • Start date Start date
K

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?
 
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?
Click to expand...

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".
 
Back
Top