Simple MS Access crosstab query with lookup column

Question

I have a simple MS Access crosstab query question. I have a table with the following fields:

Name, category, number

And want to do a crosstab with name as row header and category with column header. Category is defined as a text field in the database and has a defined lookup value.

If I run a simple select query, everything looks fine, but if I run a crosstab query I get numbers as column headers. Does this have something to do with the lookup definition?

Any ideas? Thanks in advance.

Answer 1

Things are very rarely simple when you use lookups in tables. They should be avoided unless you have to have them for sharepoint. The way around this is to add the lookup table for category to the design grid and link the two tables by the join field.

TRANSFORM Sum(t.Number) AS SumOfNumber
SELECT t.NameX
FROM MyTable t 
INNER JOIN CategoryTable c ON t.Category = c.CatID
GROUP BY t.NameX
PIVOT c.CategoryName;

Note that name is a reserved word and should not be used.