comparing memofields

[Francis]

I have 2 memo fields in table1.field1 and table2.field2. I need to
compare them, and get the rows where the fields arent equal. How can
one accomplish this?
Thanks advance
of
Francis

 

[John Vinson]

I have 2 memo fields in table1.field1 and table2.field2. I need to
compare them, and get the rows where the fields arent equal. How can
one accomplish this?
Thanks advance
of
Francis

I'm not sure I understand. If you have 1000 records in each table, do
you want all million possible pairings, returning all pairs where the
memo fields are unequal? Or is there some other joining field in the
table?

John W. Vinson[MVP]

 

[Francis]

I have a Companies DB, that i use for CRM (Customer Relationship
Management) Pourposes, i also use an Accountant/Finantial DB, where i
create the receipts and invoices. The thing is i have in my CRM DB the
following table structure (only will say the structure elements
required for this case):

Companies_Table:
ID : Autonumber
Name: text
Email: hyperlink
Observations: Memo

Clients_LinkedTable: (External Data From Finantial DB)
ClientID: Number
ClientName:Text
ClientEmail: text
ClientObservations: Memo

Objective: Compare the 2 tables in the different databases, and make a
multiple field unmatched query, retrieving the full row, in the cases
there is at least one field that doesn't match.

Suggestion:
SELECT Clients_LinkedTable.*
FROM Clients_LinkedTable LEFT JOIN Companies_Table ON
(Clientes_LinkedTable.ClientID=Companies_Table.ID) AND
(Clientes_LinkedTable.ClientName=Companies_Table.Name) AND
(Clientes_LinkedTable.ClientEmail=HyperlinkPart(Companies_Table.Email,2))
AND
(Clientes_LinkedTable.ClientObservations=Companies_Table.Observations)
WHERE Empresas_Table.codigoEmpresa IS NULL;

This would work, the problem is the memo field that can't be used in
Joins, and i need it also to be compared.


John Vinson escreveu:

 

[Francis]

I'm using field names in Portuguese and forgot to translate for better
understanding the full query, here it is:
SELECT Clients_LinkedTable.*
FROM Clients_LinkedTable LEFT JOIN Companies_Table ON
(Clientes_LinkedTable.ClientID=Companies_Table.ID) AND
(Clientes_LinkedTable.ClientName=Companies_Table.Name) AND
(Clientes_LinkedTable.ClientEmail=HyperlinkPart(Companies_Table.Email,2))
AND
(Clientes_LinkedTable.ClientObservations=Companies_Table.Observations)
WHERE Companies_Table.ID IS NULL;

Nicely

 

[Jamie Collins]

Observations: Memo

(Clientes_LinkedTable.ClientObservations=Companies_Table.Observations)

This would work, the problem is the memo field that can't be used in
Joins, and i need it also to be compared.

Would a match between hash values be reliable enough, given the nature
of the data?

Jamie.

--

 

[Rick Brandt]

I have a Companies DB, that i use for CRM (Customer Relationship
Management) Pourposes, i also use an Accountant/Finantial DB, where i
create the receipts and invoices. The thing is i have in my CRM DB the
following table structure (only will say the structure elements
required for this case):

Companies_Table:
ID : Autonumber
Name: text
Email: hyperlink
Observations: Memo

Clients_LinkedTable: (External Data From Finantial DB)
ClientID: Number
ClientName:Text
ClientEmail: text
ClientObservations: Memo

Objective: Compare the 2 tables in the different databases, and make a
multiple field unmatched query, retrieving the full row, in the cases
there is at least one field that doesn't match.

Suggestion:
SELECT Clients_LinkedTable.*
FROM Clients_LinkedTable LEFT JOIN Companies_Table ON
(Clientes_LinkedTable.ClientID=Companies_Table.ID) AND
(Clientes_LinkedTable.ClientName=Companies_Table.Name) AND
(Clientes_LinkedTable.ClientEmail=HyperlinkPart(Companies_Table.Email,2))
AND
(Clientes_LinkedTable.ClientObservations=Companies_Table.Observations)
WHERE Empresas_Table.codigoEmpresa IS NULL;

This would work, the problem is the memo field that can't be used in
Joins, and i need it also to be compared.

If you go into SQL view you can change from an Equi-Join to one that uses an
expression. The following worked for me in a simple test...

SELECT Table1.*
FROM Table1 LEFT JOIN Table2
ON CStr(Nz(Table1.mmo,"")) <> CStr(Nz(Table2.mmo,""));

I assume it would an fairly inefficient query to run though so I would be
careful if the table is large.

 

[Francis]

Nicely done and sorted now, quewl Rick, your suggestion was obvious and
i didnt remember to go for it and one other thing i found, is that
when you are using ponctuation in field names, you have to Use in SQL,
the delimiter brackets in the field name. Having everything implemented
and working So Thanks

Is very good your assistance.
Francis


Rick Brandt escreveu:

 

[Jamie Collins]

The following worked for me in a simple test...

SELECT Table1.*
FROM Table1 LEFT JOIN Table2
ON CStr(Nz(Table1.mmo,"")) <> CStr(Nz(Table2.mmo,""));

I assume it would an fairly inefficient query to run though so I would be
careful if the table is large.

Agreed. in my test (omitting NZ): self-joined a table of 1K rows in 12
seconds, 2K rows in 45 seconds, 3K rows in 100 seconds, 4K rows in 300
seconds.

I also agree it's a good discovery and a nice workaround when the row
count is very small.

Jamie.

--