Data Validation

[James Wilson]

Hi All,

I'm trying to write an application where I'm importing a large quantity of
account codes into a table. I have already placed a chart of account codes
in another table. I want the import table to validate that all the imported
codes are in the chart of account codes.

Can anyone suggest what validation rule I need?

 

[Brian]

What is the source of your import and/or your import method? Another table
via a query? A spreadsheet or CSV file via TransferText?

If you import them all, you can easily use an unequal join between the new
data table & the account codes table to identify & delete ones that do not
match.

Create a new query.
Add both tables, NewData & Accounts
Add the NewData * to the grid
Join the NewData table to the Accounts table on the account #.
Right-click the join line.
Click Join Properties.
Click Include ALL records from NewData and only those records from Accounts
where the joined fields are equal.
Add the AccountNumber field from Accounts to the grid.
Enter Null in its criteria.

This query will show you all NewData account numbers not appearing in
Accounts.

 

[James Wilson]

Thanks, Brian, wherever you are...!

It worked really well.

James