Comma Delimited Query Question

[NeonSky via AccessMonster.com]

Hello,

I am not sure how to design the following query. Please reference the two
sample tables...

TABLE A

FNAME LNAME ADDR1 CONFO
Anthony Smith House12 143
Anthony Smith House12 345
Anthony Smith House12 659

TABLE B

FNAME LNAME ADDR1 CONFO
Anthony Smith House12 143, 345, 565


What I would like my query to accomplish is to refence TABLE A's CONFO field
in relation to TABLE B, and delete those records from TABLE A where Confo
exists in TABLE B. Thus after running this delete query we are left with
TABLE A in the below state.

TABLE A

FNAME LNAME ADDR1 CONFO
Anthony Smith House12 659

Thank you for your time and consideration!!

 

[John W. Vinson]

Hello,

I am not sure how to design the following query. Please reference the two
sample tables...

TABLE A

FNAME LNAME ADDR1 CONFO
Anthony Smith House12 143
Anthony Smith House12 345
Anthony Smith House12 659

TABLE B

FNAME LNAME ADDR1 CONFO
Anthony Smith House12 143, 345, 565


What I would like my query to accomplish is to refence TABLE A's CONFO field
in relation to TABLE B, and delete those records from TABLE A where Confo
exists in TABLE B. Thus after running this delete query we are left with
TABLE A in the below state.

TABLE A

FNAME LNAME ADDR1 CONFO
Anthony Smith House12 659

Thank you for your time and consideration!!

Well... this is risky and difficult. If TABLE A had a record with CONFO equal
to 43, you wouldn't want it to be deleted, even though there is a substring 43
in the string "143, 345, 565"!

The problem is that you've violated the principle of atomicity in TableB. Is
there ANY chance that you could clean this up?

If not... *BACK UP YOUR DATABASE*, check a large subset of values, and try

DELETE [Table A].*
FROM [Table A]
WHERE EXISTS
(SELECT [Table B].CONFO FROM [Table B]
WHERE ", " & [Table B].CONFO & ", "
LIKE "*, " & [TABLE A].CONFO & ", *")

This will convert the TableB.CONFO field to (frex) ", 143, 345, 565, " and
then search that field for a substring " ,345, ". The appended commas and
blanks will find the substring even if it's initial or final. This does assume
that the string CONFO is *absolutely consistant* in its format (i.e.
143,345,546 will NOT be found).

 

[NeonSky via AccessMonster.com]

Hello John,

Thank you for your response, very helpful. May I ask what the principle of
atomicity is?

Thanks!

Hello,

[quoted text clipped - 24 lines]

Thank you for your time and consideration!!

Well... this is risky and difficult. If TABLE A had a record with CONFO equal
to 43, you wouldn't want it to be deleted, even though there is a substring 43
in the string "143, 345, 565"!

The problem is that you've violated the principle of atomicity in TableB. Is
there ANY chance that you could clean this up?

If not... *BACK UP YOUR DATABASE*, check a large subset of values, and try

DELETE [Table A].*
FROM [Table A]
WHERE EXISTS
(SELECT [Table B].CONFO FROM [Table B]
WHERE ", " & [Table B].CONFO & ", "
LIKE "*, " & [TABLE A].CONFO & ", *")

This will convert the TableB.CONFO field to (frex) ", 143, 345, 565, " and
then search that field for a substring " ,345, ". The appended commas and
blanks will find the substring even if it's initial or final. This does assume
that the string CONFO is *absolutely consistant* in its format (i.e.
143,345,546 will NOT be found).

 

[NeonSky via AccessMonster.com]

Hello John, Furtrhermore the query works perfectly (thank you!), though you
mention that this query is risky, would you care to elaborate? To answer your
question, the string lengths, and comma delimited spacing will always be
consiststent in my data sources. Thank you!

Hello,

[quoted text clipped - 24 lines]

Thank you for your time and consideration!!

Well... this is risky and difficult. If TABLE A had a record with CONFO equal
to 43, you wouldn't want it to be deleted, even though there is a substring 43
in the string "143, 345, 565"!

The problem is that you've violated the principle of atomicity in TableB. Is
there ANY chance that you could clean this up?

If not... *BACK UP YOUR DATABASE*, check a large subset of values, and try

DELETE [Table A].*
FROM [Table A]
WHERE EXISTS
(SELECT [Table B].CONFO FROM [Table B]
WHERE ", " & [Table B].CONFO & ", "
LIKE "*, " & [TABLE A].CONFO & ", *")

This will convert the TableB.CONFO field to (frex) ", 143, 345, 565, " and
then search that field for a substring " ,345, ". The appended commas and
blanks will find the substring even if it's initial or final. This does assume
that the string CONFO is *absolutely consistant* in its format (i.e.
143,345,546 will NOT be found).

 

[NeonSky via AccessMonster.com]

Hello John, I am also having a bit of difficulty adding some additional
criteria to the query. Specifically where Table A FNAME = Table B FNAME and
Table A ADDR1 = Table B ADDR1. Thank you for your help.

Hello,

[quoted text clipped - 24 lines]

Thank you for your time and consideration!!

Well... this is risky and difficult. If TABLE A had a record with CONFO equal
to 43, you wouldn't want it to be deleted, even though there is a substring 43
in the string "143, 345, 565"!

The problem is that you've violated the principle of atomicity in TableB. Is
there ANY chance that you could clean this up?

If not... *BACK UP YOUR DATABASE*, check a large subset of values, and try

DELETE [Table A].*
FROM [Table A]
WHERE EXISTS
(SELECT [Table B].CONFO FROM [Table B]
WHERE ", " & [Table B].CONFO & ", "
LIKE "*, " & [TABLE A].CONFO & ", *")

This will convert the TableB.CONFO field to (frex) ", 143, 345, 565, " and
then search that field for a substring " ,345, ". The appended commas and
blanks will find the substring even if it's initial or final. This does assume
that the string CONFO is *absolutely consistant* in its format (i.e.
143,345,546 will NOT be found).

 

[John W. Vinson]

Hello John,

Thank you for your response, very helpful. May I ask what the principle of
atomicity is?

Fields should be atomic: having one and only one value. Rather than storing
multiple values in one field, you should really use TWO TABLES in a one to
many relationship; your TableB should be related one to many to a TableBConfos
with one record per CONF value.

The "riskiness" I mention is that any time you're looking for a substring, you
have to be very, very confident that you don't get a false match on the
substring being too big or too little and matching something you don't expect.

In addition, this kind of join will be VERY slow if your tables get large
because Access cannot use indexes when you're searching within a field.

You'll have to post more information about the nature of the problem with the
other fields. Before you get too deep with this query, though, I'd REALLY
recommend restructuring your tables so you don't have repeating data in the
field!!!!

 

[NeonSky via AccessMonster.com]

Hello John, thank you for explaining "atomic". I understand your concern with
non atmoic field values, I will keep that in mind for future projects. Any
chance you might be able to answer the following question? Thanks!

I am also having a bit of difficulty adding some additional
criteria to the query. Specifically where Table A FNAME = Table B FNAME and
Table A ADDR1 = Table B ADDR1. Thank you for your help.

 

[John W. Vinson]

I am also having a bit of difficulty adding some additional
criteria to the query. Specifically where Table A FNAME = Table B FNAME and
Table A ADDR1 = Table B ADDR1. Thank you for your help.

Not without knowing anything about the nature of these fields in the table, or
what problem you're having with them! Could you post the current SQL of the
query, and what you want to happen with FNAME and ADDR1? Perhaps some examples
of the data in those two fields would help also.

 

[NeonSky via AccessMonster.com]

Hello John, I actually got this element sorted out on my end. Thank you for
your responses!