Join a concatenated field

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Hi,

I need your help to figure out how to join a concatenated
field. In a query, I have joined the first name, a space
and the last name into a field (First_Name & ' ' &
Last_Name). Is there a way to convert this concatenated
field into a field that can be joined with another field
in another table? I tried to create a table and pasted
the results into the table but somehow it still seems to
remember that it is a concatenated field. I keep getting
the following message: Cannot join on Memo, OLE, or
Hyperlink Object. Does anyone know how to get around this?

Thank you,
Aaron
 
You shouldn't need to do this if your database is properly designed, for the
simple reason that the name should only be stored in a single location. I'm
not sure whether it can be done or not (though I suspect it can't), but even
if it could be done it would not be a good idea. You could easily have two
identical names referring to two different people and your system would have
no means of distinguishing them.
 
Unfortunately, the database was not that well designed - vendor designed this. I forgot to mention that I also changed the concatenated name to a primary key as there should only be one instance of the name in this table. Although, it could show up multiple times in the table that I am trying to join to

The message seems to indicate that the concatenated field is a Memo, OLE, or Hyperlink Object. When I created the table to paste the query into, it still seems to think that it is a field other than the text that I tried to setup for it

Any other ideas?
----- Andrew Smith wrote: ----

You shouldn't need to do this if your database is properly designed, for th
simple reason that the name should only be stored in a single location. I'
not sure whether it can be done or not (though I suspect it can't), but eve
if it could be done it would not be a good idea. You could easily have tw
identical names referring to two different people and your system would hav
no means of distinguishing them
 
Cannot join on Memo, OLE, or
Hyperlink Object.
Click to expand...

It sounds like you're trying to Join your calculated concateneation to
a Memo field - i.e. it's the OTHER table field that's causing the
error, not the concatenation! Is that the case?
 
Hi John,

Thank you for that info.....I can't believe I didn't look
there first! I just figured that the fields that I
extracted from the application were all in text. I didn't
think that this would be the case. Oh brother! Thank you
for your help!

Aaron
 
John V,

I bow to the guru. I had no idea on what might be causing the problem. Good job.

John S
 
Back
Top