Report Showing ID number and not the name

[tipopilotas]

I am creating a report and it is showing the number associated with that particular line and not the name itself. I know this is probably really simple but just trying to figure out how to fix this.



Thank you

[pbaldy]

Presumably you have a data table that this report is based on, and a lookup table with the numbers and names. You would base your report on a query that joined your data table with this "lookup" table. That enables you to include fields from both tables in the report.

[tipopilotas]

Presumably you have a data table that this report is based on, and a lookup table with the numbers and names. You would base your report on a query that joined your data table with this "lookup" table. That enables you to include fields from both tables in the report.

I tried doing that and the same result. For the company field it shows the ID number associated with that company and not the name, however on the query it shows the company name in that field.

[pbaldy]

Are you joining the tables? It sounds like you have a lookup field:

http://www.mvps.org/access/lookupfields.htm

[tipopilotas]

Are you joining the tables? It sounds like you have a lookup field:

http://www.mvps.org/access/lookupfields.htm

What I am doing is creating a query off of a table that I have created for a list of items. Yes within that table it does have lookup fields. Is there another way to create these tables and do the same thing for looking up info? An example would be I have a table with employee names, however I want another table to tap into that table to let me pick an employee to add into it. And I want to run a report that shows all this info and not just numbers.

[pbaldy]

Typically you use a combo box on a form. The combo gets its values from the lookup table. Often the numeric key value is hidden, so the user only sees the text value, but the numeric value is the bound column, so it is what's saved in related tables. For a report, you use a query that includes both tables, joined on the numeric field. That enables you to include the text field in the report.

[tipopilotas]

Ok, so I must be lost somewhere. So here is how I have it setup.

1 Table Listed as Order Transactions
1 Table Listed as Company
1 Table Listed as Product Types

In that order transaction table it is grabbing information from the company table and the product types table using lookup wizard.

I then have a query called Order Transaction Query that basically is just a duplicate of the Order transaction table but it is a query.

I then create the report to come from the Order Transaction Query.

When in the design view for the report I have to change the company and product type ones to combo box and do a select statement to grab from the table for the respective area. However I still get the ID and not the actual name.

What am I missing?

[pbaldy]

Create a new query that has all 3 tables in it. If join lines don't appear between the key fields, add them by clicking and dragging. Double click on the fields in the orders table that you want in the report, causing them to appear in the field grid below. Also add the descriptive text fields from the other two tables. Base a report on this query.

[kayakismet]

Hi! Kaya, a longtime lurker here for the same issue. So I found this solution for it on another forum:

"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.

And it almost works. Problem now is that it's just displaying the wrong name, not the one that's linked. Is this a bad track to go down? It seems to be SO CLOSE to working!

[kayakismet]

This solution DOES work...I had to strip the "ORDER BY" clause, and change the column count to 2. Hallelujah! Try it and see if it works!

[jmitchelldueck]

Hi, I followed the process you described to build a query and it seems to work for me (although bugs usually abound for me - only time will tell!). Can you explain why? I think I have a general understanding, the lookup field looks at the reference table and picks corresponding values to the reference table's primary key, which is why it displays the number in the report. How can the query select the correct text values?

Thanks.

[June7]

That's the whole idea behind relational database - join tables on primary and foreign key fields so all related data will be available.

If you have a specific issue, start your own thread.