Error Report

[Charles Tippie]

I am trying to generate an error report for inconsistencies in our database.
I want to identify students who have incomplete data or data errors and have
a column that lists the error message in a new table. It would look something
like this:

A B C D
Name EngProf RFEP Date Error Message
JonnyA 2 1/1/2008 EngProf and RFEP Date Inconsistent

I am able to query the lists of students who meet the criteria, the problem
I am having is that I dont know how to generate the Error message in a new
column (column D in the Example). There are about 10 or 15 different
possibilities for error messages and when the students name comes it is
difficult to see why they are on the list.

 

[Allen Browne]

1. Create a table, with the fields you need for your error report.

2. In the query that selects those with the inconsistent date, type that
text into a new column in query design, e.g.:
'EngProf and RFEP Date Inconsistent'
Change the query to an Append query (Append on Query menu.)
Access will ask what table to append to: answer your error report table.

3. Repeat step 3 for the other queries you need that identify other
problems, and append then to your error report table as well.

4. Create a report based on the error report table.

5. Create a query to delete everything from the error report table.

6. Create a macro that uses RunSQL to run the delete query, followed by the
append queries, followed by OpenReport.