Table concatenation in Relationships Screen

[Flick Olmsford]

I set a field in a table to lookup values in another. I set the lookup
table, the display widths, number of columns to show, etc. I tested the
lookup by going into datasheet view and it works fine.

When I drag to connect the fields in the relationship window, I get a
message about "not a valid name" with the lookup table name listed
concatenated with the table storing the values - i.e.
lookup_TableOriginalTable is not valid

I have tried deleting the field as it is new and defaults to the same value
for every record

 

[Flick Olmsford]

My last sentence should be "I tried deleting the field and re-adding it but
the problem still occurs."

 

[Pat Hartman]

This may be among the reasons why lookup fields at the table level are not
used by experts. If you remove the lookup from the table, you will have no
problem with the join. Leave the lookups for forms where they belong. In a
table, they are merely a crutch for those who don't know how to create
queries or are too lazy to.

I don't use lookups at the table level because I discovered 15 years ago
that they were more trouble then they were worth (same thing for captions)
so I don't have a lot of experience with them. The only time you can get
away unscathed when using them is when the bound field is the visible text
value. If your lookup table's PK is numeric and hidden (as it should be),
you will always end up with issues of whether you are referencing the real
field value or the visible field value. This issue extends to VBA as well
SQL.

 

[Jason Lepack]

I would like to point out that the datatype for the lookup field is
probably not Text. It's probably a number. You're probably trying to
join the employee_id to the employee_name or some such thing.

 

[Flick Olmsford]

I did not know all this. Thanks for the info.

Re: the problem, since it was a new version of a currently running database,
I was able to delete the table entirely and re-add it under another name.
That worked, but is hardly something I want to do in the future.

Thanks again for the developers tip.

 

[Jason Lepack]

Essential Reading for Beginners:

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

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

 

[Flick Olmsford]

I got the articles. Very helpful. I had encountered some of hte problems
you guys mentioned. Your advice is great.