Microsoft Access - Validating Data

[eric.kung]

Dear All

My name is Eric Kung, I am a new member of the forum.

The reason I joined because I have a question which I would like to ask.





Currently I have 2 files.
Both files have 2 fields which are exactly identical (in terms of field name, field content etc.).
The field is: File_name.

Now I would like to develop a program using MS Access to validate these two files and see whether anything from these files are missing.

Under the field: File_name there are many file names.
The format is similar to this: 20110812_FRI


Is it possible to create a program to allow it to automatically validate the two files (The file is CSV file, and it is being automatically imported to access file.) and create an alert IF found data is missing?

Is it then possible for the ALERT or some kind to display which files are missing?

[NoellaG]

Hi,

it's certainly possible. You could design two queries combining the two linked/imported tables, first with a left outer join to see which records exist in table A and have no matching record in table B, then a right outer join to see which records exist in table B and have no matching record in table A.
You can then show the results in a form or print them using a report.

grNG

[eric.kung]

Hi,

it's certainly possible. You could design two queries combining the two linked/imported tables, first with a left outer join to see which records exist in table A and have no matching record in table B, then a right outer join to see which records exist in table B and have no matching record in table A.
You can then show the results in a form or print them using a report.

grNG

Dear NoellaG

Thank you for the great answer but here is another thing I do not understand...
What is this outer join you speak of?

is this a function of Access? Or is there a code to define it?
Please assist, thank you!

Is it possible for the validation be done automatically?
If yes, how? Is there some examples where I can follow?

[NoellaG]

Hi,

as in most relational databases the data in Access are stored in a set normalized tables. In queries you can join those tables by creating relationships (mostly PK -> FK). The standard join is an inner join showing all matching records from the joined tables:

Select tblTable1.fldA, tblTable1.fldB, ... , tblTable2.fldC from tblTable1 inner join tblTable2 on tblTable1.fldA = tblTable2.fldZ

You can use outer joins to show all records from table 1 and, where a matching record exists from table2, the matching records from table 2

Select tblTable1.fldA, tblTable1.fldB, ... , tblTable2.fldC from tblTable1 left/right outer join tblTable2 on tblTable1.fldA = tblTable2.fldZ

All you have to do then is add an is null selection criterion to the PK field (or any field that's always filled) from table 2 to look for all records in table 1 that haven't got a matching record in table 2.

succes
NG