need help with validation of imported items

[pduncan]

I have an inventory application where I import an excel spreadsheet into a (temp) table (using TransferSpreadsheet). Before I bring any of this data further into the application for "processing" I want to be sure that it is correct - it often isn't.

I want to be sure that the part number and price are correct. To do this, I have a fixed (tblPartList) table that contains only correct information. Note: this table is intended to be read only.

My goal is to compare the values in the temp table (tblImport) to the known correct values in tblPartList and then, as the comparisons pass or fail, move the associated file from tblImport to one of two tables – tblInProcess for those that pass, and tblNonProcess for those that did not match (i.e. part number was not found in tblPartList or part number was found, but the price for the part was wrong.) I would also like (if possible) to insert a “code” in tblNonProcess along with the bad data that would signify what went wrong. (ex – 1 for bad part, 2 for wrong price)

When all is said and done, tblImport should have no records in it, tblNonProcess should have only records that are “bad” along with a reason why, and tblInProcess has ONLY records that are correct and ready to be “processed” - does this sound like an unreasonable request for Access 2002? I would think a function (or series of functions) would do this, but I am completely in the dark about how I would do that.

Any help, suggestions or guidance would be greatly appreciated. Also – given what I would like for the final result, if you know of a better way, I am open to new ideas.

Thanks in advance!

PDuncan – Memphis, TN

 

[BNPMike]

The general idea is you join from the input table to to tblPartList. Any records you select now are valid. Convert an appropriate select statement into an append query to tblInProcess. This loads your good records. Repeat the process only with a left join. This time select all those records without a match (ie tblPartList value is null) and load these into tblNonProcess.