cannot join on memo field

[jacobk]

Hi all,

I have a problem, which I'm stuck with.
If somebody has a suggestion, please help.

The situation:
I have a database for intranet logging where I import data in tblURLs from a
..txt file with about 300,000 records a time (3 months). The URL-field must be
a Memo field (more than 255 characters sometimes). This field is not unique,
it repeats many times in the table.
I've added an ID (autonumber to the table).

The second tabel, tblStructure has also the URL-memo field with the same
data from the first, but filtered to be unique.
This table has also other fields, say level0, level1, level2 and URL.
I use this table to get the structure of the intranetsite translated to URL's.

The problem:
I like to see the intranet structure in my crosstab query in stead of the
URL's.
P.e. The structure "support" is covered by the urls:
/intranet/boston/support, /intranet/texas/support and
/intranet/houston/support.
I want to convert the urls back to the structure.

I tried with different joins: no success: cannot join on memo field.
I tried with a DLookup in the query: no success, gives errors.

Anyone any suggestions ? I'm stuck a little bit now...

Thanks in advance,

Jacob.

 

[KARL DEWEY]

Fields have to be the same type to join.
Try using a hyperlink field in both tables.

 

[Michel Walsh]

Memo and OLE fields cannot directly participate to a join, neither to a
GROUP, neither to an ORDER BY. At most, you can, in some instances, use the
first 255 characters of a memo field, for such operations.



Vanderghast, Access MVP

 

[John W. Vinson]

I have a database for intranet logging where I import data in tblURLs from a
.txt file with about 300,000 records a time (3 months). The URL-field must be
a Memo field (more than 255 characters sometimes). This field is not unique,
it repeats many times in the table.
I've added an ID (autonumber to the table).

The second tabel, tblStructure has also the URL-memo field with the same
data from the first, but filtered to be unique.
This table has also other fields, say level0, level1, level2 and URL.
I use this table to get the structure of the intranetsite translated to URL's.

You cannot join on memo fields... period. The rationale is that a Join
requires an Index, and that you can't store a two gigabyte field (which a Memo
field could legally contain!) in an index.

I suspect the closest you'll be able to come is to create a separate Text 255
field, and run an Update query updating it to Left([memofield], 255); you will
be able to index this field and join on it. If you have two URL's that differ
only past the 255th character you will also need a WHERE criterion to exclude
them.

 

[jacobk]

Hi,

Thanks for all your reply!
Karl: You also cannot join on Hyperlink and OLE objects. That did not work.

Michel and John: This could be a good workaround. I'll keep this in mind!

I found the solution as followes:

I created 2 queries; 1st matches the breadcrumb from tblURLs and tblStructure.
This query I used as input for the 2nd query, which retrieves the structures
from tblStructure. This works well.

Jacob.