VB, SQL, or Both? Matching = Records between 2 tables 1

[LowBrow]

I have two tables:
TableA is a master list of names, addresses, phone numbers, etc. TableB is a volunteer list. I need to write a query such that:
From TableA select specific row where lastname, firstname, mailadd = specific row from TableB.
TableB contains inaccurate information and I need to compare parts of each record to the records in TableA to pull out possible corrected information. I hope this makes sense. Here is an example:
In TableA there would be a perfect record that had
LstName FstName StAdd City State
Doe John 1234 Eastwood St New York NY

In TableB I may have a record that has:
LstName FstName StAdd City State
Doe John New York NY

In this case, I would have my query search for a distinct record that had From Table:LstName, FrstName, City, State
= TableB LstName, FstName, City, State

I'm sure there has to be a way to write this query. If anyone has any ideas, please let me know.
Thanks!

 

[SBendBuckeye]

Hello,

Assuming that your master list in TableA will only have one record per person, try the following:

Select A.LstName, A.FstName, A.StAdd, B.StAdd, A.City, _
A.State, A.ZipCode, B.ZipCode
From TableA A, TableB B
Where A.LstName = B.LstName
And A.FstName = B.FstName
And A.City = B.City
And A.State = A.State

The above code will return data from both tables where FstName, LstName, City and State are equal. It returns columns from both tables for comparison purposes. If you have more columns that you want to verify just include them in a similar manner.

If you only want to return the ones with discrepancies do as below:

Select A.LstName, A.FstName, A.StAdd, B.StAdd, A.City, _
A.State, A.ZipCode, B.ZipCode
From TableA A, TableB B
Where A.LstName = B.LstName
And A.FstName = B.FstName
And A.City = B.City
And A.State = A.State
And (A.StAdd <> B.StAdd Or A.ZipCode <> B.ZipCode)

NOTE: Because the joins were done with a Where clause, this query is a read only query.

Hopefully, this will at least get you started. I'll try to get back later today and show you how to update out of the query.

Are you at all concerned that there may be more than one corresponding row in TableB or are you just worried about inconsistent data in a one-to-one relationship? Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[LowBrow]

Thanks Tons, SBB,
I have not incorporated this yet, but it seems it will work (a response in another forum gave a similar statement). I am more concerned that I identify members in TableB that exist in TableA (validating the information in our volunteer list). In total, TableA has up 38 fields of information that can be validated against, however, only about 8 of these fields correspond to fields in TableB. My new question is this, can I use this query in a form that would allow me to select records in TableB (like a drop down list for the 8 corresponding fields), and run that against TableA? I have written parameter queries used in forms to accomplish things such as this, but I think this may get a little too complex for me.
I have been making the alterations in the query directly for each record to this point, but it would go much quicker to use a form.
Thanks again for your help!

 

[SBendBuckeye]

Hey LowBrow,

The short answer is yes, you can use a query as a rowsource into a form. Sorry I can't answer now, but I'll try to check back later today (EST) or tomorrow. Good Luck!

MedBrow ;-)

Have a great day!

j2consulting@yahoo.com

 

[LowBrow]

Thanks Again, SBB,
I look forward to your advice.

 

[SBendBuckeye]

Hey LowBrow,

Sorry I didn't get back last night until late. Here is the query discussed above rewritten with a For Clause join instead of a Where Clause join. This is important because a Where clause join is a read only query.

A. Where clause join query - Note a.city = b.city, etc

Select A.LstName, A.FstName, A.StAdd, B.StAdd, A.City, _
A.State, A.ZipCode, B.ZipCode
From TableA A, TableB B
Where A.LstName = B.LstName
And A.FstName = B.FstName
And A.City = B.City
And A.State = A.State
And (A.StAdd <> B.StAdd Or A.ZipCode <> B.ZipCode)

B. For clause join query - returns same thing but updateable

Select A.LstName, A.FstName, A.StAdd, B.StAdd, A.City, _
A.State, A.ZipCode, B.ZipCode
From TableB B Inner Join TableA A
On (A.LstName = B.LstName And
A.FstName = B.FstName And
A.City = B.City And
A.State = A.State)
Where (A.StAdd <> B.StAdd Or
A.ZipCode <> B.ZipCode)

Note that the criteria part remains in the Where clause but the parts pertaining to linking the tables is moved up into the From portion

If you will permit me, let me suggest that you do all of this with queries. This is assuming that TableA is always accurate and that any discrepancies found in TableB should be replaced with their corresponding TableA value. You have 3 possible situations pertaining to TableB data as below:

1. The data matches - no need to do anything

2. TableB data does NOT exist in TableA - should be deleted

3. TableB data does not match TableA - should be updated

Presumably, you have been able to identify the discrepancy data using the query code previously discussed. Using that as a basis, you can do an update query similar to this:

Update TableB B Inner Join TableA A
On (A.LstName = B.LstName And
A.FstName = B.FstName And
A.City = B.City And
A.State = A.State)
Set B.Column1 = A.Column1,
B.Column2 = A.Column2, etc
Where (A.StAdd <> B.StAdd Or A.ZipCode <> B.ZipCode)

To convert a select query into an equivalent update query:

1. Discard the entire Select portion

2. Change From Table1, etc to Update Table1, etc - Use the entire From clause including Joins after changing From to Update and make it the first statement

3. Add Set B.Column1 = A.Column1, B.Column2 = A.Column2, etc

4. Append the same Where clause

Note that if you run your update query in the QBE window it will NOT update but will run like a Select query so you can precheck it. Sorry this is so late. I hope it helps!






Have a great day!

j2consulting@yahoo.com

 

[LowBrow]

Thanks a ton! No, it is not too late. I'll be working on this project for the next couple of weeks at least. This will help a lot!

 

[SBendBuckeye]

Forgot to mention that if you build your queries in design view in the QBE editor selecting your tables and establishing relationships by linking it will build Inner Join SQL for you, just look at the SQL when you are finished. I learned a lot about Inner Joins by playing around with queries. Good Luck!

Have a great day!

j2consulting@yahoo.com