Data Validation

[Tom]

I am performing data validation on a table. More
specifically, I need to validate dependencies between
certain fields.

For instance, I might have a table that contains the
following fields/records:

ID MILCIV STATUS RANK
== ====== ====== ====
1 Military Officer SK1
2 Military Enlisted BM2
3 Civilian A-Level A5
4 Military Enlisted OF3
5 Civilian B-Level A5

Based on the sample data, the dependencies for the
following records are violated:

ID 1:
STATUS = Officer, but RANK is equivalent to Enlisted

ID 4:
STATUS = Enlisted, but RANK is equivalent to Officer

ID 5:
STATUS = B-Level, but RANK is equivalent to A-Level Civ.


I have developed queries that show all 3 records with the
incorrect data.... so, identifying the records is not the
problem.

I also have placed the queries into a module and execute
them one after another via a command button.

HERE'S THE PROBLEM NOW!
Executing all queries and reviewing them one after
another (and closing 20+ open query windows) does not
seem very efficient.

Besides, most of the time, I don't have an error in the
table so I'm going through this exercise "just to make
sure"...

Setting up criteria in the table which will allow to
enter only certain values in a field based on other
values in another field will not work either since I have
too many possibilities (hundreds of military ranks, etc.).

My goal would be to still execute all queries via the
command button on a form, but ONLY SHOW THOSE WHERE THERE
IS TRULY AN ERROR (instead of opening all queries).

Is that possible???


Thanks! Any help is appreciated!!!

Tom

 

[Tom]

John:

Thanks for your prompt feedback!

In respect to your follow-up questions...

1. Although this is "real" data, it only examplifies the
challenge I need to overcome. I have multiple
dependencies that need to be validated. Again, finding
the errors is not the problem... it's all about "how
efficiently I find them"...

2. None of the data can be dropped. In respect to
the "driving value"... in the example data, there's no
straight-forward way. As a matter of fact, other fields
will have to be analyzed to either change Mil to Civ or
A/B levels to any Mil rank.

I understand that I provided lots of info in my previous
posting... and in this one, too. So, let me reiterate
the task at hand:

I'd like to create some form of message boxes that will
pop up ONLY if there is an error in the dependency.
Then I only have to analyze e.g. 5 out of 20 errors
(instead of analyzing and closing 20 open query windows).

Make sense? Hopefully, you or anyone else has an idea as
to how I should attack this!

Thanks again!

Tom

 

[John Vinson]

I'd like to create some form of message boxes that will
pop up ONLY if there is an error in the dependency.

Unless you have either a Table or a VBA array which contains all the
errors - or a Table which contains all the valid values, which you can
check by searching it and finding nothing - I can't think of any way
to do this.

 

[Tim Ferguson]

For instance, I might have a table that contains the
following fields/records:

ID MILCIV STATUS RANK
== ====== ====== ====
1 Military Officer SK1
2 Military Enlisted BM2
3 Civilian A-Level A5
4 Military Enlisted OF3
5 Civilian B-Level A5

Making some guesses about the dependencies here, I guess you have some
design work still to do on getting this thing normalised.

If Status depends on Rank, then it should be in its own table.

If MilCiv depends on Status, then it should be in its own table.

So a suggested design would be (*=primary key, +=foreign key)

Something(*ID, Rank+)

Ranks(*RankCode, Status+)

Statuses(*StatusCode, MilCiv)

The whole point of the last forty years of DB research into the relational
model is to make obsolete the kind of update anomaly that you describe. You
have a relational database (well, very nearly) so use it relationally!

Hope that helps



Tim F