Seeking Advice on setting ErrorTracking DB

I'd love to have some advice on designing a DB which I intend to import data
from a CSV on a monthly bases. The structure of the CSV will be the same each
month. The purpose is to check for errors in client services data.

A query will compare the records and return potential errors. However, there
will be times when it appears that there is an error - yet it isn't. (This is
determined manually when someone goes back into the original application's
typed "memo NOTES" to read about it and make the judgment call). So if the
record is OK, I'd need a check a box (or someway to indicating it's OK).
Here's how I'm thinking of building it. Please add your wisdom/ advice.

tbl-ClientServiceData is created to hold CSV data
tbl-ErrorOK is created with PK, FkID (related to tbl-ClientServiceData's pk)
and ErrorOK checkbox defined as a YES/NO field.

New monthly data comes in and gets added to existing data
Once query's have identified potential errors and these are manually
verified, the user will check the ErrorOK checkbox.

Another query will remove all the records which have the ErrorOK checkbox so
the records are removed from sight when it's time to check errors again next
month.

Does this at all sound reasonable? I realize this is quite nebulous but I'm
just hoping someone will understand enough to make sure I'm heading in the
right direction as I build from scratch.

--
Rocky''''''''s Curious Coding

I wrote a similar routine for an application a couple of years ago, and here
is how I handled it.

First I imported the CSV file into tblRawData (After deleting any records in
the table)
This was automated. I used a common dialog control to allow the user to
select the file to import, then everything was done with VBA and queries.
tblRawData had one more field than the imported data I used to identify
records with errors.

Then, I wrote a recordset processing routine that read each record, and had
the business rules coded so that it would upate the ErrorFlag to True if an
error was found.

Then I had a subform with a query on tblRawData that filtered out records
where ErrorFlag = False, so the user could see the records and review them.
If the user decided the record was good, she could click the error flag off.
The user could also modify the data. I keeped a form level variable to tell
me if the user made any changes to the data except for clicking the ErrorFlag
off so I would know I had to rurn the Edit routine again to be sure the user
had made valid modifications.

Once the user completed correcting errors and removing the ErrorFlag, she
would click an update button. If the user had made any modifications, it
would run the Edit routine again; otherwise, it would run an append query
that would move valid records to the production data.

The only issue you have is what to do about records that can't be corrected
because you don't have the necessary information at hand. If you need to
update production with good records but leave the bad records in tblRawData,
you might want to have a Suspense table where you append the error records
until you can deal with them. In our case the rule was data could not be
added to production until all records were correct.

And once you are done, you need to be sure tblRawData is empty.
--
Dave Hargis, Microsoft Access MVP


"Rocky" wrote:

I'd love to have some advice on designing a DB which I intend to import data
from a CSV on a monthly bases. The structure of the CSV will be the same each
month. The purpose is to check for errors in client services data.

A query will compare the records and return potential errors. However, there
will be times when it appears that there is an error - yet it isn't. (This is
determined manually when someone goes back into the original application's
typed "memo NOTES" to read about it and make the judgment call). So if the
record is OK, I'd need a check a box (or someway to indicating it's OK).
Here's how I'm thinking of building it. Please add your wisdom/ advice.

tbl-ClientServiceData is created to hold CSV data
tbl-ErrorOK is created with PK, FkID (related to tbl-ClientServiceData's pk)
and ErrorOK checkbox defined as a YES/NO field.

New monthly data comes in and gets added to existing data
Once query's have identified potential errors and these are manually
verified, the user will check the ErrorOK checkbox.

Another query will remove all the records which have the ErrorOK checkbox so
the records are removed from sight when it's time to check errors again next
month.

Does this at all sound reasonable? I realize this is quite nebulous but I'm
just hoping someone will understand enough to make sure I'm heading in the
right direction as I build from scratch.

--
Rocky''''''''s Curious Coding