 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / February 2008Comma Delimited Query Question
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!! >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).  SignatureJohn W. Vinson [MVP] Hello John, Thank you for your response, very helpful. May I ask what the principle of atomicity is? Thanks! >>Hello, >> [quoted text clipped - 23 lines] >that the string CONFO is *absolutely consistant* in its format (i.e. >143,345,546 will NOT be found). >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!!!! SignatureJohn W. Vinson [MVP] 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. >>Hello John, >> [quoted text clipped - 17 lines] >recommend restructuring your tables so you don't have repeating data in the >field!!!! > 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. SignatureJohn W. Vinson [MVP] Hello John, I actually got this element sorted out on my end. Thank you for your responses! >> I am also having a bit of difficulty adding some additional >>criteria to the query. Specifically where Table A FNAME = Table B FNAME and [quoted text clipped - 4 lines] >query, and what you want to happen with FNAME and ADDR1? Perhaps some examples >of the data in those two fields would help also. 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 - 23 lines] >that the string CONFO is *absolutely consistant* in its format (i.e. >143,345,546 will NOT be found). 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 - 23 lines] >that the string CONFO is *absolutely consistant* in its format (i.e. >143,345,546 will NOT be found). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.