Crosstab query and Lookup field problem

[Andy C Matthews]

Hi there,
I'm trying to create a Crosstab query that shows a Class of Business on the
rows, Status along the columns and a total number of Projects as the values.
The trouble is, the Project Status field is a lookup from another table, so
though the Crosstab query works, along the top it shows 1, 2, 3, 4 etc as the
column headings instead of Investigating, Estimating, On Hold...etc.
I can't seem to find a way around this, can anyone help?
Many thanks

 

[Duane Hookom]

That's just one of the many reasons why many of us "seasoned" developers shun
lookup fields. If you want to display the actual status, include the table
that stores the actual status. This would be the table with records like:
StatusID StatusTitle
1 Investigating
2 Estimating
3 On Hold
4 FUBAR ;-)

Join the appropriate numeric fields so you can include StatusTitle as the
Column Heading.

 

[Andy C Matthews]

Thanks Duane, that worked a charm!
I'm beginning to see, like you say, that lookups are a pain and should be
avoided...

Perhaps you can assist with a similar problem. In another query, I have two
fields (Project Manager A and Project Manager B) pulled through from a table,
where each of those fields is a lookup (with a predefined list of 'A' project
managers and 'B' project managers). This works fine, with the Project
Managers names, not ID numbers, showing up. However, when I create a new
field that concatenates these together (PMs:[ProjectManagerA] &
[ProjectManagerB]), I end up with the ID numbers...any ideas?

 

[Duane Hookom]

Again, include the table(s) that store the actual values in your query.

--
Duane Hookom
Microsoft Access MVP

Thanks Duane, that worked a charm!
I'm beginning to see, like you say, that lookups are a pain and should be
avoided...

Perhaps you can assist with a similar problem. In another query, I have two
fields (Project Manager A and Project Manager B) pulled through from a table,
where each of those fields is a lookup (with a predefined list of 'A' project
managers and 'B' project managers). This works fine, with the Project
Managers names, not ID numbers, showing up. However, when I create a new
field that concatenates these together (PMs:[ProjectManagerA] &
[ProjectManagerB]), I end up with the ID numbers...any ideas?

That's just one of the many reasons why many of us "seasoned" developers shun
lookup fields. If you want to display the actual status, include the table
that stores the actual status. This would be the table with records like:
StatusID StatusTitle
1 Investigating
2 Estimating
3 On Hold
4 FUBAR ;-)

Join the appropriate numeric fields so you can include StatusTitle as the
Column Heading.