Validation on Import

  • Thread starter Thread starter andy_42
  • Start date Start date
A

andy_42

I am importing large amounts of data into Access but want to validate the
data before inserting it into the main tables. What I would like to have
happen is the data is first inserted into a single table with the validation
rules and the invalid rows are inserted into a secondary table so that I can
keep track of the invalid entries. Can anyone help me understand how to
extract these invalid entires? For example, if the field requires and M or F
for gender and somebody enters an L, it should fail on the entire row and
insert it into a "failed" table so I can edit at very least.

Any suggestions on how to approach this is appreciated.

Thanks!
 
andy_42 said:
I am importing large amounts of data into Access but want to validate the
data before inserting it into the main tables. What I would like to have
happen is the data is first inserted into a single table with the validation
rules and the invalid rows are inserted into a secondary table so that I can
keep track of the invalid entries. Can anyone help me understand how to
extract these invalid entires? For example, if the field requires and M or F
for gender and somebody enters an L, it should fail on the entire row and
insert it into a "failed" table so I can edit at very least.
Click to expand...

INSERT INTO <'Failed' stage table> (<column list>)
SELECT <column list>
FROM <'Imported' stage table>
WHERE IIF(gender IS NULL, 'X', gender) NOT IN ('M', 'F')
AND <other search conditions>;

Jamie.

--
 
Thank you for the reply. Is it possible to simply say If any fields fail a
validation rule on import, place them into the failed table? I have many
potential columns that could fail which will vary depending on what I am
importing. Your statement will surely work but I guess I need to determine if
it is most efficient to have the WHERE clause check every column or if there
is an easier method.

Andy
 
You would need two queries. One to append the good data and another to
append the bad data to the failed table. Just reverse the logic on what is
included.
If the validation is not too complex, using the Where is probably the most
straight foward way to do it.

In a situation where the logic is more complex, an technique I have used is
to add a field to the query that returns a boolean value. Use that field to
call a function and pass all the fields that need to be checked. The
function evaluates the fields and passes a value back to the query. You then
use the Where clause to evaluate the return of the function.
 
Back
Top