Tables disregarding limit to list (1 Viewer)

I am trying to make a table that validates data as it is imported into access from excel.

To do this I have
1. Created a link table to bring the data into access from excel
2. Using a macro I am copying and pasting the data from the linked table into a full access table.

However the full access table has a number of fields that are limited to lists in order to validate the data as it goes into the access table yet if any data in the linked table is not in a list it is entered into the new table regardless, is there anyway of ensuring that when the data is copied into the new table the lists are enforced and any data that contradicts these lists is split out into an errors table?

well runnning an update/append query will bypass any validation checks you make at the from level

if you set relational integrity between the base tables, and the lmiited lookups, this will prevent the deficient records being added - but does this all imply you are storing "text" values in the value limited fields? - so how do you limit the values - do you have a lookup table or just a value list?

secondly it still might be hard finding the rejected items - it would be better to validate the new data BEFORE running the append query

So who owns the most updated version of the lists? Access or Excel?

If the items in Excel are not in Access does this imply that Access is out of date or some has entered incorrect list items in Excel.

I think you need to answer this question first before proceeding any further. It could be a combination of both. You may have a list in Access say Red, Green & Blue. Your Excel Sheet has the Colours Brown, Red and Yellow. Now We know that Red is Ok but Brown and Yellow do not appear in Access. But we decided to add the colour Yellow to the Access list but have yet got around to adding it physically. So the colour Yellow is a valid colour but Brown is not.

So what do we do? This is for you to decide I can only offer you the possible senerios that can happen, as they always do. So you need to consider them in the first instance.

David

The most up to date lists are located in Access. This database is supposed to take data that has been manually entered into excel and transform it into a validated database. We gather data using excel but then we need the data in access in order for different analysis programs to work. If the data does not agree with the access lists then it is wrong and it needs to be split into an errors table so that it can be fixed.

I have lookup tables for all the information that needs to be checked in access do you know of an easier way to validate this excel data as it comes into access?

In that case you will need to import the spreadsheet(s) into an empty dump table that is a clone of the real data table. Then validate the data in the dump table. Append the good records to the live table and the bad records to an error table.