On a report how do I display the text not the lookup value?

[Guest]

I am trying to generate a report from information stored in table#1 that
looks up organization names from table#2. When I include the organization
field from table#1 in my report, the report displays the numerical value
stored in table #1 not the actual text of the organization name stored in
table#2. How do I get the
report to display the text value from table #2? I'm just getting started
with access and tried finding this in a book I have and on the help topics
but to no avail. Any help would be greatly appreciated. Thanks.

 

[Ken Snell \(MVP\)]

Use a query as the report's RecordSource. In that query, join the table#1
with table#2 via an INNER JOIN, linking the primary key field from table#1
as the join to the foreign key field in table#2. Then include the
"organization name" field in the query's field list.

For example (not including other fields that you may want to show on the
report in this example):

SELECT [table#1].OrganizationID,
[table#2].OrganizationName
FROM [table#1] INNER JOIN
[table#2] ON [table#1].OrganizationID =
[table#2].OrganizationID;


You can do this in the query design view (the grid view) if you add both
tables to the query, and be sure that there is a join line between the two
tables' OrganizationID field (if there is not, click on OrganizationID field
in table#1 and drag to OrganizationID field in table#2).

 

[PBsoft]

I am trying to generate a report from information stored in table#1

that looks up organization names from table#2. When I include the
organization field from table#1 in my report, the report displays the
numerical value stored in table #1 not the actual text of the
organization name stored in table#2. How do I get the report to
display the text value from table #2? I'm just getting started with
access and tried finding this in a book I have and on the help topics
but to no avail. Any help would be greatly appreciated. Thanks.

You can use a combobox with the following rowsource:

SELECT OrgID, OrgName FROM table2, ORDER BY OrgName

Then set the ColumnWidths property of that combo to the following value:
0 cm, 3 cm

Comboboxes are displayed as textboxes onto reports, and with the "0 cm" column
you are hiding the value and showing the second column, the name.

 

[Guest]

I buit a query and it worked. Thanks.

Use a query as the report's RecordSource. In that query, join the table#1
with table#2 via an INNER JOIN, linking the primary key field from table#1
as the join to the foreign key field in table#2. Then include the
"organization name" field in the query's field list.

For example (not including other fields that you may want to show on the
report in this example):

SELECT [table#1].OrganizationID,
[table#2].OrganizationName
FROM [table#1] INNER JOIN
[table#2] ON [table#1].OrganizationID =
[table#2].OrganizationID;


You can do this in the query design view (the grid view) if you add both
tables to the query, and be sure that there is a join line between the two
tables' OrganizationID field (if there is not, click on OrganizationID field
in table#1 and drag to OrganizationID field in table#2).
--

Ken Snell
<MS ACCESS MVP>

I am trying to generate a report from information stored in table#1 that
looks up organization names from table#2. When I include the organization
field from table#1 in my report, the report displays the numerical value
stored in table #1 not the actual text of the organization name stored in
table#2. How do I get the
report to display the text value from table #2? I'm just getting started
with access and tried finding this in a book I have and on the help topics
but to no avail. Any help would be greatly appreciated. Thanks.

 

[Guest]

I wish I saw this before I built the query (which worked) but this seems to
be a simpler solution. Thanks for posting.