Help with Union Query

[jvicchio]

I have a database built that stores medical office location. I have some new data about 60,000 lines on people who have Violations with Medicare and are no longer allowed to bill medicare.
I was trying to match up the different people that match in my database and the violations. The data is either by doctors name or Address and the name of the clinic itself. I was able to easily run a query with the doctors names matching.

I am having problems with the Address matching because the addresses aren't going to match 100%. Here is what I have so far but I am getting syntax errors Any help will be greatly appreciated.

SELECT tblZZMedicareViolation7-9-15.ADDRESS , tblService_Provider.Service_Address
FROM tblZZMedicareViolation7-9-15, tblService_Provider
WHERE (((tblZZMedicareViolation7-9-15.ADDRESS) Like "*" & tblService_Provider.Service_Address & "*"));

 

[plog]

Those syntax errors have to do with your table names. You should only use alphanumeric characters and underscores in table names. Anything else and you have to use brackets:

[tblZZMEdicareViolation7-9-15].ADDRESS

All instances of that table need to be bracketed.

 

[jvicchio]

It was that easy!

Goodness gracious I have been sitting here getting angry. Now I have to fix the query itself because it gave a (Not Responding).

 

[plog]

Also, you are going to get false positives (data showing that really shouldn't) and false negatives (data not showing that should) with this method.

Example, Table1Address, Table2Address
1, 123 Main Street, 123 Main
2, 123 Main, 123 Main Street
3, PO Box 1725, PO Box 17

1 will show up and it should show up
2 will not show up and it should show up
3 will show up and it shouldn't show up

The same might be true of your Name match. Also with that a computer doesn't now Bob==Robert, Terri==Theresa, Teresa==Theresa, etc.

Anytime you have to do something like this, its best to actually verify your data by spot checking a few records.

 

[speakers_86]

This is not a union query, so just use the QBE. It will write the SQL for you. Also, I'm not sure that you want the hyphens in your table names.

 

[plog]

Yeah, that query is a monster. For 1 its a cartesian product (https://en.wikipedia.org/wiki/Cartesian_product) meaning you aren't joining the two tables in any manner which means it has to check every record from Table 1 against every record in Table2 resulting in [Table1 Record Count] * [Table Record Count] matches it has to check. And the check you've established (Address1 like Address2) is a very computer intensive one.

If you have any amount of records in the tables, this will take a while. One way to speed it up is to JOIN those tables somehow. My advice would be to create a query for each table and create a calculated field which takes the first 3 characters of the address field:

MatchField: Mid(Address, 1, 3)

Then to look for matches you use those 2 queries and JOIN them by the MatchField. That will help you cut down on the number of operations you have to perform.

 

[jvicchio]

I took out my hyphens on the violation table. I had them in their because that is when I received the data.

Plog I see what you mean regarding the names not matching properly but because the doctors are licensed with the state they usually have to use their full Names and Medicare only pulls full names. I think it might be better to use partials to ensure there is none slipping through the cracks.

I also see the issue with the amount of data it will take to run through the table. The table with the Violations has 65,000 lines in it because it is all 50 states that has any facility that has a violation with Medicare. I crashed it out when I ran it the first time because it was taking 3 minutes and still didn't pull down the query.

I think I am going to make the 2 queries to pull in the first 9 digits of the address fields (have some POBOX) and then run the third query I was attempting to make (w/o all that nonsense in my previous example) .