I got 9 tables in MS Access 2003 and each table have more then 2000 entries. My task is find out matched records in two columns of 9 tables. for example in each table column 1 DOB (Date of Birth) has 6 zero(mm.dd.yyyy) and column 2 ID has 5 zero (12345). Now my task is find out that Table A has matched record with Table B, Table C, Table D ..... individually and collective in both columns (DOB & ID). I find out solution for individually but need help for collective. Report will be show in this formate or ....?
RNo Table A Table B Table C Table C Table D
1 DOB ID x x
2 DOB ID x x
3 DOB ID x x x x
4 DOB ID x x
wher RNo=Record No, x=shows double record with Table A.
I hope that i will get answer from you. Best regards and thanks for your help.
The easiest, although admittedly messy, way to do it is to make a table with all the info in two columns and nine other text columns representing the nine possible tables. Make nine separate SQL statements updating one of the nine columns (with say an "X") in the column representing that table, if the info is found in that table.
If you wanted to make it one query I guess its possible, providing that you have one table which is the parent / known "good" source. The problem that is going to arise is if that table doesn't have records in that other table(s) have.
So you could do it as a series of left joins, using an iif to test if the other dob & id exists / not null and if so set a flag as "X" or " "
But its going to be messy!
Sams solution may be more appropriate - bearing in mind that you can refer to other queries within a JET query.
say if your first level of queries comapre table A to table B,C....N
then your next level of query referrs to table A and the results of the other 8 table match queries.
If you really wanted to compolicat matters you could add another layer of preprocessing which found distinct DOB's and ID's in all 9 tables (probably taking all form table 1, adding any differences from table2..tablen) and then comapre to all 9 tables to see if they exiusted in that table. But that way you have a pig to design, maintain and support. Unless you need to I'd try to the task another way using a VB procedure.
Out of curiostiy why would you need a DOB and ID - if the data is normlaised the ID should be sufficient, the DOB could be duplicated.