Identify and report missing fields

[mvreeswijk]

Dear all,

I have a question regarding the following: i have two tables (one called TARA including 4700 records and one called GDP containing 120.000 records) with the following fields:

Tara:
1. CustomerNumber
2. Name_Full
3. CPTY_CIS_CODE
4. Mat_Date

GDP:
1. Local sys ID
2. Local CPTY ID
3. GRD ID
4. GRD sh_name
5. CIS_CODE

The CustomerNumber and Local CPTY ID are unique identifiers for customers. They just have different fieldnames in the two tables.
The CPTY_CIS_CODE and the CIS_CODE are unique identifiers for internal codes and also just have different fieldnames in the two tables.

I need to extract the following 3 tables:
1. A table showing those CustomerNumbers that exist in TARA but not in GDP
2. A table showing those CPTY_CIS_CODEs that exist in TARA but not in GRD CIS_CODE
3. Thos records in TARA with no CPTY_CIS_CODE (thus blank fields)

Does anyone have any idea how to do this? Sorry i am quite a beginner.

Cheers, Mark

 

[Norie]

The easiesy way for your first 2 questions is to use the Access Unmatched Query wizard.

Just goto the query window and hit new.

A dialog should pop up with various types of query. Chose Unmatched and Access will guide you through the process.

The 3 part is quite easy.

Again goto Query window and press New.

This time chose Design View.

The Query Designer will open and you will have a list of tables/queries to add.

Double click on the TARA table and then close the list.

Now drag the CPTY_CIS_CODE into the field grid.

Then put Is Null as the criteria, add any other fields you want and goto datasheet view.

Hope that at least gets you started