field names show up in query but not in the report

[quonzilla]

i am using linked tables from my companies db to compilee info on a specific
project. i import a table from Excel and run a query using one of the linked
tables. the info that i am looking for is all there.

when i create the report, the field (manager's name) is there but the names
of the employees are numeric values (auto number value), not the actual names
that are displayed in the query.

the table that i am linked to populates the field (manager's name) with a
look up value from another table called "Merchandisers". the look up value
is:
SELECT Merchandisers.EmployeeID, [First Name] & " " & [Last Name] AS Expr1,
Merchandisers.[Last Name] FROM Merchandisers ORDER BY Merchandisers.[Last
Name];

when i run the report, the value in the managers' field is their auto
number, not their name. since the managers do not know their Access created
ID's, this does me no good, and it looks ugly on the report.

i have tried all i know and i can't seem to figure it out.

 

[Jeff Boyce]

If you are seeing one thing (IDs) in one situation and another (managers'
names) in another, it sounds to me like your underlying table uses a
"lookup" datatype field. This stores one value (the ID), but displays
another (e.g., manager name).

This is only one of the ways that using a lookup data field can come back to
bite you!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[quonzilla]

thanks Jeff. i am pretty sure that is the issue. is there anything i can do
to fix the problem? there are other reports that are generated and they
display the name not the id number.

rob

If you are seeing one thing (IDs) in one situation and another (managers'
names) in another, it sounds to me like your underlying table uses a
"lookup" datatype field. This stores one value (the ID), but displays
another (e.g., manager name).

This is only one of the ways that using a lookup data field can come back to
bite you!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

i am using linked tables from my companies db to compilee info on a
specific
project. i import a table from Excel and run a query using one of the
linked
tables. the info that i am looking for is all there.

when i create the report, the field (manager's name) is there but the
names
of the employees are numeric values (auto number value), not the actual
names
that are displayed in the query.

the table that i am linked to populates the field (manager's name) with a
look up value from another table called "Merchandisers". the look up
value
is:
SELECT Merchandisers.EmployeeID, [First Name] & " " & [Last Name] AS
Expr1,
Merchandisers.[Last Name] FROM Merchandisers ORDER BY Merchandisers.[Last
Name];

when i run the report, the value in the managers' field is their auto
number, not their name. since the managers do not know their Access
created
ID's, this does me no good, and it looks ugly on the report.

i have tried all i know and i can't seem to figure it out.

 

[Jeff Boyce]

The general consensus in the tablesdbdesign newsgroup is to not use lookup
fields. That way, there's no confusion about what it is you are getting
when you do a query.

You'll need to add the table that holds the looked-up values to your query,
joined on the lookup field. Then you'd use the field in that other table
(lookup tables, good; lookup fields, bad).

Regards

Jeff Boyce
Microsoft Office/Access MVP

thanks Jeff. i am pretty sure that is the issue. is there anything i can
do
to fix the problem? there are other reports that are generated and they
display the name not the id number.

rob

If you are seeing one thing (IDs) in one situation and another (managers'
names) in another, it sounds to me like your underlying table uses a
"lookup" datatype field. This stores one value (the ID), but displays
another (e.g., manager name).

This is only one of the ways that using a lookup data field can come back
to
bite you!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

i am using linked tables from my companies db to compilee info on a
specific
project. i import a table from Excel and run a query using one of the
linked
tables. the info that i am looking for is all there.

when i create the report, the field (manager's name) is there but the
names
of the employees are numeric values (auto number value), not the actual
names
that are displayed in the query.

the table that i am linked to populates the field (manager's name) with
a
look up value from another table called "Merchandisers". the look up
value
is:
SELECT Merchandisers.EmployeeID, [First Name] & " " & [Last Name] AS
Expr1,
Merchandisers.[Last Name] FROM Merchandisers ORDER BY
Merchandisers.[Last
Name];

when i run the report, the value in the managers' field is their auto
number, not their name. since the managers do not know their Access
created
ID's, this does me no good, and it looks ugly on the report.

i have tried all i know and i can't seem to figure it out.

 

[quonzilla]

thanks again Jeff. i will wait until the IT guy who helped build this
monster comes back from jury duty.

ttyl.

The general consensus in the tablesdbdesign newsgroup is to not use lookup
fields. That way, there's no confusion about what it is you are getting
when you do a query.

You'll need to add the table that holds the looked-up values to your query,
joined on the lookup field. Then you'd use the field in that other table
(lookup tables, good; lookup fields, bad).

Regards

Jeff Boyce
Microsoft Office/Access MVP

thanks Jeff. i am pretty sure that is the issue. is there anything i can
do
to fix the problem? there are other reports that are generated and they
display the name not the id number.

rob

If you are seeing one thing (IDs) in one situation and another (managers'
names) in another, it sounds to me like your underlying table uses a
"lookup" datatype field. This stores one value (the ID), but displays
another (e.g., manager name).

This is only one of the ways that using a lookup data field can come back
to
bite you!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

i am using linked tables from my companies db to compilee info on a
specific
project. i import a table from Excel and run a query using one of the
linked
tables. the info that i am looking for is all there.

when i create the report, the field (manager's name) is there but the
names
of the employees are numeric values (auto number value), not the actual
names
that are displayed in the query.

the table that i am linked to populates the field (manager's name) with
a
look up value from another table called "Merchandisers". the look up
value
is:
SELECT Merchandisers.EmployeeID, [First Name] & " " & [Last Name] AS
Expr1,
Merchandisers.[Last Name] FROM Merchandisers ORDER BY
Merchandisers.[Last
Name];

when i run the report, the value in the managers' field is their auto
number, not their name. since the managers do not know their Access
created
ID's, this does me no good, and it looks ugly on the report.

i have tried all i know and i can't seem to figure it out.