Recommendation for query structure

  • Thread starter Thread starter Christian
  • Start date Start date
C

Christian

Hi NG,

I'm trying to create a query that will return rows from a table when the
content is not in line with a set of rules, e.g. fieldA has to be 4
characters long, field B can only be 4 or 9, field can not be zero etc.

The purpose of this is to validate an external file and locate possible
errors.

The external file is loaded to a table and the query is run.

Question:
Should I create one query that will contain all possible checks and assign
the checks a criteria in each row
or should I create a query for each check and them combine all the queries
afterwards?

I would like that each error adds a error message to the row selected, e.g
"Field A: not 4 characters long" or "Field B: Value can only be 4 or 9" etc.

if this is truly advance then one row might fail on several checks and the
error message for the row should then be a combination of these error
messages.


Any suggestions are warmly welcome
- Chr
 
Christian,

You could use a union query to return the problematic records with the
appropriate diagnostic in one step. Under this approach, if there are two
problems within the same record, the record will be returned twice, with two
different diagnostics. The SQL for the query would be something like:

SELECT TableName.*, "Field A: not 4 characters long" AS ErrMsg
FROM TableName
WHERE Len([fieldA])<>4

UNION SELECT TableName.*, "Field B: Value can only be 4 or 9" AS ErrMsg
FROM TableName
WHERE (fieldB <> 4 AND fieldB <> 9)

ORDER BY ErrMsg

You can add more UNION SELECT statements if required, justy make sure you
use the correct table and field names.

Note: ORDER BY ErrMsg can be changed to ORDER BY (yourPKfield) if this makes
manual correction easier (you get all errors for the same table record in
subsequent query records).

HTH,
Nikos
 
If this were a one-time validation, I'd do each separately. If this were a
routine to be applied regularly, I'd first do each separately (to "proof"
the queries), then combine them together in a procedure (or a macro).

Just one person's opinion

Jeff Boyce
<Access MVP>
 
Back
Top