Report shows the uniqueID value instead of the text

[InDFW]

I have my table set up to lookup value from another table and have created the relationships. Both tables have unique IDs (autonumber) as the primary key.

When i add the field to the table, the unique ID appears, and not the text value.

Is there a way to fix this, or did I set up my database incorrectly?

 

[PNGBill]

Are you using the correct terms here?

Tables don't normally look up. Queries and forms do.

Do you have a form that is linked to a table and you want to select from a list to get data for a text box on your form?

 

[InDFW]

In my tables, I created a combo box where it looks up the value from another table.
"Look up" may not be the correct term, but it uses the values in the other table as the drop down box choices.

 

[InDFW]

oh wait - dang - i'm so sorry! i have a typo - here's what my question should have been:

I have my table set up to lookup value from another table and have created the relationships. Both tables have unique IDs (autonumber) as the primary key.

When i add the field to the report, the unique ID appears, and not the text value.

Is there a way to fix this, or did I set up my database incorrectly?

 

[PNGBill]

Did you use the lookup wizard to create the lookup field ?

When you do it with the wizard it asks you which column you want to get your data from and which column or colums to show.

Sorry, I read as table looking up. It is a table with a lookup field. Just semantics I think.

With the lookup data you have choices as to how many columns to show and how wide they are and which column is the data to use.

Have a look at your fields properties box in Table design view.

Sometimes, better to make a query from your 'lookup" table with just the data you want to see and use and then make this the data the lookup wizard goes to when creating the field in your table.

 

[InDFW]

Sometimes, better to make a query from your 'lookup" table with just the data you want to see and use and then make this the data the lookup wizard goes to when creating the field in your table.

this is a good idea - I'll do it this way and it should eliminate my problem. Thanks~

 

[PNGBill]

Ok, now report.

A report should be from a single source. If you have data coming from more then one source ie a2 tables or a table and a query, consider making a query that will collect all the data you need and when this works, ie you can see all the data displayed - but not as you wish it to be. Then you make a report.

Generally, if the query works, so should the report.

Reports are good for grouping and totaling columns and groups but I prefer to do as much of the other "fancy" stuff first in a query and then make the report. Easier to see where you have gone wrong as you construct the query and make it more intricate as you go.

 

[boblarson]

Lookups should NOT be used at table level. You can use lookup tables with combos/list boxes on FORMS but you should not use them in tables. See here for why.

Remove the lookups at table level and use a query for the report to tie the values to their lookups and get the descriptions. See here for more about how to do that.

 

[PNGBill]

Bob, What about value lists for table data ie Where marital status you chose single, married, divorced, tired etc - this is ok?

 

[InDFW]

Lookups should NOT be used at table level. You can use lookup tables with combos/list boxes on FORMS but you should not use them in tables. See here for why.

Remove the lookups at table level and use a query for the report to tie the values to their lookups and get the descriptions. See here for more about how to do that.

The first article describes exactly how i have always created my combo boxes and explains exactly why i have always had problems with them.

2nd article is same thing as PNGBill just suggested, and seems to be a good solution - can't believe i never thought of it.
thanks, y'all!!!

 

[boblarson]

Bob, What about value lists for table data ie Where marital status you chose single, married, divorced, tired etc - this is ok?

Better to use value lists on the FORM's combo box instead.

 

[PNGBill]

Thanks Bob, not sure if I have any table lookup fields, they are quite likely all on the form.