Append Query Help Needed

[Guest]

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

 

[Phil]

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.

 

[Guest]

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

 

[John Vinson]

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]

 

[Phil]

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.

 

[Guest]

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

 

[Guest]

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

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.

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

 

[John Spencer]

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.

 

[Guest]

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]

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;

 

[Guest]

That worked Perfect!

Thanks,
Hank

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;