How best to do this.. 1 main table, search and match 4 other tables and sort

[The Stigenator]

Let me explain what i am trying to do:

Table 1: All sites that our dept has.

Table 2: Federal Sites

Table 3: Another State agency sites.

Table 4: Another State agency sites.

What I need to do is this:

1. Take any site from table 1 that matches table 2, 3, 4 (for name or address) and drop it into table 5 and 6. Table 5 and 6 will contain name, address, county, zip, tracking ID # taken from the the comparing table. Table 1 does not change.

I was thinking of doing this in access, but i havent done access in ages. Any quick tutorial guide. Can access do SQL, i figure a few lines of SQL code will do this easily rather than a whole program.


If I go teh access route, i will have to make sure that tables 2, 3, 4 are in teh same format but table 1 really doesnt need to be in teh same format.

The first criteria for search would be by name (if it doesnt hit) and if its true, then output the whole record, if its not true, then search by address and output the whole record. If multiple records are found then put all records.

Here is the catch, any records not matched, it should drop into table 7 for manual checking.

So how should i go about this?

 

[KLin]

Doing this in straight SQL can get complicated. I'd use VBA code in MS Access to accomplish this:

1. Create union query of tables 2, 3, and 4.
2. create recordset in vba of table1
3. run routine that loops through table1 records looking for matches in union query for name, then address.
4. insert matches of any records from union query into table6.
6. insert unmatches into table7.

 

[The Stigenator]

i must say i havent done this something complex (rather i havent programmed in VBA). But yeah i am going to ask you for help if i need any .. I am sure i will need some major help on this. but I am sure i can figure it out too

 

[Evadman]

What database are the tables in? Access, mySQL, MS SQL?

Here is some SQL that does everything you ask.



/*
check each table for a match from table 1. insert any match into table_5.
from what I can see, table 5 and 6 will be exact copies of each other, and we will make that last.
Some of this sql assumes that tracking_id is unique across all sites.
Table 5 and 6 have these fields:
name, address, county, zip, tracking_ID - from source table
Found_type - the way the record was matched. Name and addy or just addy
Found_Table - table the record was found in
*/

/* begin with table_1 to table_2. */
/* name and addy match */
insert into table_5 (name, address, county, zip, tracking_ID, Found_type, Found_Table)
select
name, address, county, zip, tracking_ID, 'Match on Name & Addy' as Found_type, 'Table_2' as Found_Table
from
table_1 inner join table_2 on table_1.name = table_2.name and table1.address = table_2.address

/* addy match */
insert into table_5 (name, address, county, zip, tracking_ID, Found_type, Found_Table)
select
name, address, county, zip, tracking_ID, 'Match on Addy' as Found_type, 'Table_2' as Found_Table
from
table_1 inner join table_2 on table_1.name = table_2.name and table1.address = table_2.address
WHERE NOT tracking_ID in (select tracking_ID from table_5) /* can also be done with an outer join if you want instead of a where clause*/

/* table_1 to table_3. */
/* name and addy match */
insert into table_5 (name, address, county, zip, tracking_ID, Found_type, Found_Table)
select
name, address, county, zip, tracking_ID, 'Match on Name & Addy' as Found_type, 'Table_3' as Found_Table
from
table_1 inner join table_3 on table_1.name = table_3.name and table1.address = table_3.address
WHERE NOT tracking_ID in (select tracking_ID from table_5) /* this will make it so records already found in table 2 are not entered again*/

/* addy match */
insert into table_5 (name, address, county, zip, tracking_ID, Found_type, Found_Table)
select
name, address, county, zip, tracking_ID, 'Match on Addy' as Found_type, 'Table_3' as Found_Table
from
table_1 inner join table_3 on table_1.name = table_3.name and table1.address = table_3.address
WHERE NOT tracking_ID in (select tracking_ID from table_5) /* this will make it so records already found in table 2 are not entered again*/


/* table_1 to table_4. */
/* name and addy match */
insert into table_5 (name, address, county, zip, tracking_ID, Found_type, Found_Table)
select
name, address, county, zip, tracking_ID, 'Match on Name & Addy' as Found_type, 'Table_4' as Found_Table
from
table_1 inner join table_4 on table_1.name = table_4.name and table1.address = table_4.address
WHERE NOT tracking_ID in (select tracking_ID from table_5) /* this will make it so records already found in table 2 & 3 are not entered again*/

/* addy match */
insert into table_5 (name, address, county, zip, tracking_ID, Found_type, Found_Table)
select
name, address, county, zip, tracking_ID, 'Match on Addy' as Found_type, 'Table_4' as Found_Table
from
table_1 inner join table_4 on table_1.name = table_4.name and table1.address = table_4.address
WHERE NOT tracking_ID in (select tracking_ID from table_5) /* this will make it so records already found in table 2 & 3 are not entered again*/


/* table 5 now has all name and addy matches.
now we need to look for records from table 2,3,4 that were not found. The simpilist SQL statement would be to use the tracking_ID
and check tables 2,3,4 for an ID that does not exist.
*/

/* Compare all tracking ID's to those in table 5. any that are not found need to be put into table_7 */
/* table 2 */
insert into table_7 (name, address, county, zip, tracking_ID, Found_type, Found_Table)
SELECT
name, address, county, zip, tracking_ID, 'Not in table_5' as Found_type, 'Table_2' as Found_Table
FRom
table_2
WHERE NOT tracking_ID IN (SELECT tracking_ID FROM table_5) /* any record not found should be inserted */

/* table 3 */
insert into table_7 (name, address, county, zip, tracking_ID, Found_type, Found_Table)
SELECT
name, address, county, zip, tracking_ID, 'Not in table_5' as Found_type, 'Table_3' as Found_Table
FRom
table_3
WHERE NOT tracking_ID IN (SELECT tracking_ID FROM table_5) /* any record not found should be inserted */

/* table 4 */
insert into table_7 (name, address, county, zip, tracking_ID, Found_type, Found_Table)
SELECT
name, address, county, zip, tracking_ID, 'Not in table_1' as Found_type, 'Table_4' as Found_Table
FRom
table_4
WHERE NOT tracking_ID IN (SELECT tracking_ID FROM table_5) /* any record not found should be inserted */



/* create a duplicate of table_5 for table_6 */
insert into table_6 (name, address, county, zip, tracking_ID, Found_type, Found_Table)
SELECT
name, address, county, zip, tracking_ID, Found_type, Found_Table
FRom
table_5