A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old August 13th, 2007, 06:02 PM posted to microsoft.public.access.reports
Rob T[_2_]
external usenet poster
 
Posts: 13
Default On a report how do I display the text not the lookup value?

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.
  #2  
Old August 13th, 2007, 06:23 PM posted to microsoft.public.access.reports
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default On a report how do I display the text not the lookup value?

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


"Rob T" wrote in message
...
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.



  #3  
Old August 14th, 2007, 09:21 AM posted to microsoft.public.access.reports
PBsoft[_2_]
external usenet poster
 
Posts: 1
Default On a report how do I display the text not the lookup value?

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.

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skypebsoftsolution


  #4  
Old August 15th, 2007, 05:24 PM posted to microsoft.public.access.reports
Rob T[_2_]
external usenet poster
 
Posts: 13
Default On a report how do I display the text not the lookup value?

I buit a query and it worked. Thanks.

"Ken Snell (MVP)" wrote:

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


"Rob T" wrote in message
...
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.




  #5  
Old August 15th, 2007, 05:24 PM posted to microsoft.public.access.reports
Rob T[_2_]
external usenet poster
 
Posts: 13
Default On a report how do I display the text not the lookup value?

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

"PBsoft" wrote:

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.

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skypebsoftsolution



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.