Restricting Fields on Import?

[eyal8r]

Hey guys-
New here, and fairly new to Access as well (although I can stumble my way around). I am building a database in which I have to import the data on a daily basis (there won't be much, if any, data entry- just manipulating/Queries of the imported data). One of the fields I am importing is a 'category' type field. There's no way to restrict the data entry aspect of it before I import it into my DB- so I have to work with what I got.

The users type in the categories themselves- many all match the same name/spellings, some do not. So I assume I'm going to have a Category table with all the acceptable categories already entered into it. What I need is some way for the ones that don't fit the acceptable Category names to be flagged- once flagged I need to be prompted for each record if I want to ADD the category to the Category Table, or, if I want to match it to an existing category in the category table. If I choose to match it to an existing category, it will always keep that new category name so that queries and whatnot are consistent.

An Example-
The acceptable Category name might be 'Fruits'.
Upon importing the file- there might be entries that match that exactly, or- some might be entered as-

The Fruits
Fruits II
The Fruity Ones

If these three were to be in the import file- I want to be prompted to change the field entry to 'Fruits' or create a new category name, and associate that with the new name.

How would I go about doing this?
Thanks!

 

[Moniker]

Make a query using the imported data as your driver. In that query, add the imported data table and then the category table. LEFT JOIN the Category field from the imported data table to the Category field on the category table. Set the criteria on the Category field in the category table to Is Null.

This will return the categories that were imported that were not found in the "expected" or "acceptable" category list.

What you do with that list is up to you. If you want it to prompt you, there will be some coding involved. You can go about it a lot of ways once you have the "unacceptable" category list, so I'll leave that up to you.