If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append Query Help Needed
My CSRs are each responsible for reviewing their section of the alphabet
looking for similarities. To assist them, I need an Append Query that looks something like this: TBL1, COL1 If TBL2, COL1 has a partial match to TBL1, COL1 Then Move TBL1, Entire Record to TBL3 All Three TBLs already exist, as do the TBL Structures and there is no relationship between the TBLs. Can someone please assist me with the correct SQL syntax? TBL2, COL1 has precise partial information, such as Johnson. Thanks, Hank |
#2
|
|||
|
|||
Append Query Help Needed
More details. I do not think you are going to get a lot of help here
without more detail. Give some examples: What would be in Tbl1 col1 and tbl2 col1 that would be a "partial match?" You could match "Fred" to "Freddy", "Fredrick" and "Fredrica", but you would not be able to match "Fredrick" to "Fredrica" for instance. You could match "ed" to Fred,Freddy, Fredrica, as well. If you were trying to match addresses, like 101 Johnson St. #4 101 Johnson Street apt. 4 Good luck. For such a thing I would pull the data into other tables so you can mess with it, then run a series of queries: Replace ST. with Street. delete apt., #, Delete Ave with Avenue etc. Finally, I would delete all of the spaces, at which point, the two examples would read the same. How you want to do it really depends on what you want to do it to. wrote: My CSRs are each responsible for reviewing their section of the alphabet looking for similarities. To assist them, I need an Append Query that looks something like this: TBL1, COL1 If TBL2, COL1 has a partial match to TBL1, COL1 Then Move TBL1, Entire Record to TBL3 All Three TBLs already exist, as do the TBL Structures and there is no relationship between the TBLs. Can someone please assist me with the correct SQL syntax? TBL2, COL1 has precise partial information, such as Johnson. Thanks, Hank |
#3
|
|||
|
|||
Append Query Help Needed
The partial information match would be "*TBL2, COL1"
Please do not over think this query. All I want is the SQL code to get me pointed in the right direction. Thanks, Hank "Phil" wrote in message ... More details. I do not think you are going to get a lot of help here without more detail. Give some examples: What would be in Tbl1 col1 and tbl2 col1 that would be a "partial match?" You could match "Fred" to "Freddy", "Fredrick" and "Fredrica", but you would not be able to match "Fredrick" to "Fredrica" for instance. You could match "ed" to Fred,Freddy, Fredrica, as well. If you were trying to match addresses, like 101 Johnson St. #4 101 Johnson Street apt. 4 Good luck. For such a thing I would pull the data into other tables so you can mess with it, then run a series of queries: Replace ST. with Street. delete apt., #, Delete Ave with Avenue etc. Finally, I would delete all of the spaces, at which point, the two examples would read the same. How you want to do it really depends on what you want to do it to. wrote: My CSRs are each responsible for reviewing their section of the alphabet looking for similarities. To assist them, I need an Append Query that looks something like this: TBL1, COL1 If TBL2, COL1 has a partial match to TBL1, COL1 Then Move TBL1, Entire Record to TBL3 All Three TBLs already exist, as do the TBL Structures and there is no relationship between the TBLs. Can someone please assist me with the correct SQL syntax? TBL2, COL1 has precise partial information, such as Johnson. Thanks, Hank |
#4
|
|||
|
|||
Append Query Help Needed
On Thu, 16 Mar 2006 02:19:28 GMT, wrote:
The partial information match would be "*TBL2, COL1" This means that whatever you're matching to contains any arbitrary number of characters followed by the ten exact characters TBL2, COL1. Somehow I don't think this is what you mean. As requested, and it's a pretty simple request - could you give one or two examples of the ACTUAL CONTENTS of your tables, and what constitutes a match? John W. Vinson[MVP] |
#6
|
|||
|
|||
Append Query Help Needed
Correct, I miss-stated this ("*TBL2, COL1").
What I meant was: If TBL2, COL1 contains the following: Johnson Johansen Gorginson *TBL2,COL1 would find: Mary Johnson Bob Johnson Jack Johansen Amy Johansen Kerry Gorginson Jeorge Gorginson "John Vinson" wrote in message ... On Thu, 16 Mar 2006 02:19:28 GMT, wrote: The partial information match would be "*TBL2, COL1" This means that whatever you're matching to contains any arbitrary number of characters followed by the ten exact characters TBL2, COL1. Somehow I don't think this is what you mean. As requested, and it's a pretty simple request - could you give one or two examples of the ACTUAL CONTENTS of your tables, and what constitutes a match? John W. Vinson[MVP] |
#7
|
|||
|
|||
Append Query Help Needed
Let me re-state this in far simpler terms.
TBL1 is where the data needs to go. TBL2 is where the data currently exists. TBL3 is where the the criteria for what I do not want exists. So my SQL Statement would look somthing like this INSERT INTO TBL1 ( COL1, COL2, COL3 ) SELECT TBL2.COL1, TEBL2.COL2, TBL2.COL3 FROM TBL2 WHERE ((TBL3.COL1) NOT LIKE "??Here is what I don't know??") ORDER BY TBL2.col1; For simplification, lets say I have a special charecter "^" which represents the criteria in TBL3, Col1. So I want to insert into TBL1 all records that do not meet the following criteria "*^". How do I write this part of the statement? Hank "Phil" wrote in message ... So your data might be: "fredTBL1, COL1" "GoergeTBL2, COL1" Trying to partial match to "FordTBL1, COL1" "ChevroletTBL2, COL1" I don't think that is what you mean, but if it is, use you could use WHERE Rights$([FieldName],10) = Rights$([OtherFieldName],10) If not, give me three examples from each side of your match so I have a better idea. Not trying to overthink it, just do not have enough information to understand the nature of the problem. wrote: The partial information match would be "*TBL2, COL1" Please do not over think this query. All I want is the SQL code to get me pointed in the right direction. Thanks, Hank "Phil" wrote in message ... More details. I do not think you are going to get a lot of help here without more detail. Give some examples: What would be in Tbl1 col1 and tbl2 col1 that would be a "partial match?" You could match "Fred" to "Freddy", "Fredrick" and "Fredrica", but you would not be able to match "Fredrick" to "Fredrica" for instance. You could match "ed" to Fred,Freddy, Fredrica, as well. If you were trying to match addresses, like 101 Johnson St. #4 101 Johnson Street apt. 4 Good luck. For such a thing I would pull the data into other tables so you can mess with it, then run a series of queries: Replace ST. with Street. delete apt., #, Delete Ave with Avenue etc. Finally, I would delete all of the spaces, at which point, the two examples would read the same. How you want to do it really depends on what you want to do it to. wrote: My CSRs are each responsible for reviewing their section of the alphabet looking for similarities. To assist them, I need an Append Query that looks something like this: TBL1, COL1 If TBL2, COL1 has a partial match to TBL1, COL1 Then Move TBL1, Entire Record to TBL3 All Three TBLs already exist, as do the TBL Structures and there is no relationship between the TBLs. Can someone please assist me with the correct SQL syntax? TBL2, COL1 has precise partial information, such as Johnson. Thanks, Hank |
#8
|
|||
|
|||
Append Query Help Needed
To identify the records in Tbl1 that you want you could use a non-equi join.
Note that this type of join cannot be built or shown in the query grid. SELECT tbl1.Col1, Tbl1.Col2, Tbl1.Col3 FROM Tbl1 INNER JOIN tbl2 ON Tbl1.Col1 Like "*" & tbl2.Col1 & "*" WHERE Tbl2.Col1 IS NOT NULL Once this is working to identify the records you can turn it into an append query INSERT Into Tbl3 (ColA, ColB, ColC) SELECT tbl1.Col1, Tbl1.Col2, Tbl1.Col3 FROM Tbl1 INNER JOIN tbl2 ON Tbl1.Col1 Like "*" & tbl2.Col1 & "*" WHERE Tbl2.Col1 IS NOT NULL I really don't understand by you need an append query to add records to third table when you should be able to just use the Select query and have it always returning the desired matches when it is called. wrote: My CSRs are each responsible for reviewing their section of the alphabet looking for similarities. To assist them, I need an Append Query that looks something like this: TBL1, COL1 If TBL2, COL1 has a partial match to TBL1, COL1 Then Move TBL1, Entire Record to TBL3 All Three TBLs already exist, as do the TBL Structures and there is no relationship between the TBLs. Can someone please assist me with the correct SQL syntax? TBL2, COL1 has precise partial information, such as Johnson. Thanks, Hank |
#9
|
|||
|
|||
Append Query Help Needed
Getting Closer. Thanks for your persistence in assisting me.
Using the following query, I get the records that meet the criteria in TBL3.COLa SELECT TBL2.COLa, TBL2.COLb, TBL2.COLc FROM TBL2 INNER JOIN TBL3 ON TBL2.COLa Like "*" & TBL3.COLa & "*" WHERE TBL3.COLa IS NOT NULL; How would I go about doing the exact opposite. Looking for the records that do not meet the criteria in TBL3,COLa? TBL1 is where the data needs to go. TBL2 is where the data currently exists. TBL3 is where the the criteria for what I do not want exists. Hank "John Spencer" wrote in message ... To identify the records in Tbl1 that you want you could use a non-equi join. Note that this type of join cannot be built or shown in the query grid. SELECT tbl1.Col1, Tbl1.Col2, Tbl1.Col3 FROM Tbl1 INNER JOIN tbl2 ON Tbl1.Col1 Like "*" & tbl2.Col1 & "*" WHERE Tbl2.Col1 IS NOT NULL Once this is working to identify the records you can turn it into an append query INSERT Into Tbl3 (ColA, ColB, ColC) SELECT tbl1.Col1, Tbl1.Col2, Tbl1.Col3 FROM Tbl1 INNER JOIN tbl2 ON Tbl1.Col1 Like "*" & tbl2.Col1 & "*" WHERE Tbl2.Col1 IS NOT NULL I really don't understand by you need an append query to add records to third table when you should be able to just use the Select query and have it always returning the desired matches when it is called. wrote: My CSRs are each responsible for reviewing their section of the alphabet looking for similarities. To assist them, I need an Append Query that looks something like this: TBL1, COL1 If TBL2, COL1 has a partial match to TBL1, COL1 Then Move TBL1, Entire Record to TBL3 All Three TBLs already exist, as do the TBL Structures and there is no relationship between the TBLs. Can someone please assist me with the correct SQL syntax? TBL2, COL1 has precise partial information, such as Johnson. Thanks, Hank |
#10
|
|||
|
|||
Append Query Help Needed
Assumption: Tbl3.ColA is never null.
Try the following to identify the records in tbl2 SELECT TBL2.COLa, TBL2.COLb, TBL2.COLc FROM TBL2 LEFT JOIN TBL3 ON TBL2.COLa Like "*" & TBL3.COLa & "*" WHERE TBL3.COLa IS NULL; IF the above works then try INSERT INTO TBL1 (ColA, ColB, ColC) SELECT TBL2.COLa, TBL2.COLb, TBL2.COLc FROM TBL2 LEFT JOIN TBL3 ON TBL2.COLa Like "*" & TBL3.COLa & "*" WHERE TBL3.COLa IS NULL; wrote: Getting Closer. Thanks for your persistence in assisting me. Using the following query, I get the records that meet the criteria in TBL3.COLa SELECT TBL2.COLa, TBL2.COLb, TBL2.COLc FROM TBL2 INNER JOIN TBL3 ON TBL2.COLa Like "*" & TBL3.COLa & "*" WHERE TBL3.COLa IS NOT NULL; How would I go about doing the exact opposite. Looking for the records that do not meet the criteria in TBL3,COLa? TBL1 is where the data needs to go. TBL2 is where the data currently exists. TBL3 is where the the criteria for what I do not want exists. Hank "John Spencer" wrote in message ... To identify the records in Tbl1 that you want you could use a non-equi join. Note that this type of join cannot be built or shown in the query grid. SELECT tbl1.Col1, Tbl1.Col2, Tbl1.Col3 FROM Tbl1 INNER JOIN tbl2 ON Tbl1.Col1 Like "*" & tbl2.Col1 & "*" WHERE Tbl2.Col1 IS NOT NULL Once this is working to identify the records you can turn it into an append query INSERT Into Tbl3 (ColA, ColB, ColC) SELECT tbl1.Col1, Tbl1.Col2, Tbl1.Col3 FROM Tbl1 INNER JOIN tbl2 ON Tbl1.Col1 Like "*" & tbl2.Col1 & "*" WHERE Tbl2.Col1 IS NOT NULL I really don't understand by you need an append query to add records to third table when you should be able to just use the Select query and have it always returning the desired matches when it is called. wrote: My CSRs are each responsible for reviewing their section of the alphabet looking for similarities. To assist them, I need an Append Query that looks something like this: TBL1, COL1 If TBL2, COL1 has a partial match to TBL1, COL1 Then Move TBL1, Entire Record to TBL3 All Three TBLs already exist, as do the TBL Structures and there is no relationship between the TBLs. Can someone please assist me with the correct SQL syntax? TBL2, COL1 has precise partial information, such as Johnson. Thanks, Hank |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Link CSV file created query append query to append data | esparzaone | Running & Setting Up Queries | 2 | July 5th, 2005 04:49 PM |
Append Query Problem | Santara | General Discussion | 3 | July 5th, 2005 04:32 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
append Query duplicating records | Alex | Running & Setting Up Queries | 1 | July 8th, 2004 01:31 PM |