Dirty record report

[irethedo]

From time to time I get dirty records added to my databse by certain users and even though the data entry form does have code to keep a user from making dirty records, somehow I am still getting dirty records...

I have put together a query/report which will hunt these down and display a report for these dirty records in my ain table. The query looks for blank fields and I am also trying to get it to match up a part number with an operation.

I secify a criteria in the query to only display records where the partnum (which is matched with the correct operation from a table) to the assembly number within the main table record, but I am getting all records displayed.

I am probably doing something wrong that is too simple for me to figure out and perhaps with the join properties.

Here is what my query looks like:

SELECT [FPY Table].Date, [FPY Table].ID, [FPY Table].Operation, [FPY Table].[Assembly Number], allowable.partnum, [FPY Table].DTS, operator.Name, [FPY Table].TermID
FROM ([FPY Table] INNER JOIN operator ON [FPY Table].Operator = operator.Operator) INNER JOIN allowable ON [FPY Table].Operation = allowable.Operation
WHERE ((([FPY Table].ID) Is Null)) OR ((([FPY Table].Operation) Is Null)) OR ((([FPY Table].[Assembly Number]) Is Null)) OR ((([FPY Table].DTS) Is Null)) OR (((allowable.partnum)<>[Assembly Number]));

Any ideas?

Thanks in advance!

 

[TheAceMan1]

How are ya irethedo . . .

In the use of your db in your report area, [purple]whats the definition of a Dirty Record?[/purple]

See Ya! . . . . . .

 

[irethedo]

Hey Ace Man-

A dirty record as I have been experiencing with this database is one that is either incomplete or incorrect...

Most dirty records that I have seen in this database have blank fields where data should have been entered per the controls on the form.

Also, other dirty records I have come across is where the assembly number does not match the operation (I have a table which contains both of these fields as certain operations are only associated with certain assembly numbers)
When these do not match up I am getting erroneous data in a few of my reports.

The query is trapping all of the null or blank fields but I am not having much luck getting the query to find any records where the operation and assembly number do not match up per the table called allowable.

cheers

 

[TheAceMan1]

[blue]A dirty record as I have been experiencing with this database is one that is either incomplete or incorrect...[/blue]

To be sure . . . this is your main problem, queries not considered!

Whats missing here is an [purple]validation scheme[/purple] to qualify data! . . . necesary before you even think about printing a report. Before you print a report, [purple]validation should've already been performed[/purple] (makes printing reports that much more direct).

As far as validation is concerned, you have two choices, each involving the [blue]BeforeUpdate[/blue] event:
[ol][li]Use the [blue]BeforeUpdate[/blue] event of each control of interest to [purple]pin the user down for proper data[/purple] (can't leave the form!).[/li]
[li]Use the [blue]BeforeUpdate[/blue] event of the form to check each control of interest [blue]all at once[/blue], any failure and the user can't leave the form![/li][/ol]
In all cases above, you can use the [blue]Cancel[/blue] arguement of the [blue]BeforeUpdate[/blue] event to keep the user right where they are (can't save record) until data is validated!. This is a common technique used is Access for validation.

In a nutshell, [blue]your problem is not the query[/blue] . . . [purple]its the validation of data[/purple] (ahead of time), so the query works without a hitch! . . .

[blue]Your thoughts?[/blue]

See Ya! . . . . . .

 

[irethedo]

Thanks Ace- I couldn't agree with you any more...

The form has been set up to not allow the user to leave the form without entering these fields along with a scheme that locks the assembly number to the operation so that these match up and it works as best as it can but there are records in the databse that have already been added before this was all in place that I still need to deal with.

Also, I find that certain fields like a time date stamp is not always getted added as it should and the code is correct

A third anomaly happens from time to time where the pc can lock up and the user reboots it and then not all fields get loaded- this has been happening more than I know about...

So the form has been bullet proofed- I am looking for a way to catch some of these straggler issues afer the fact that you just can't build into code to stop them from happening...

any suggestions?

 

[TheAceMan1]

Roger That irethedo . . .

[blue] . . . there are records in the databse that have already been added before this was all in place that I still need to deal with.[/blue]

For now lets say your query does work. What would be your schema be for filling in the blanks (I know not where the data will come from!)?

Your query looks fine except for one thing (see [purple]purple[/purple]):

[blue]SELECT [FPY Table].Date, [FPY Table].ID, [FPY Table].Operation, [FPY Table].[Assembly Number], allowable.partnum, [FPY Table].DTS, [purple][b]operator[/b][/purple].Name, [FPY Table].TermID[/blue]

Hope this is a typo

See Ya! . . . . . .

 

[irethedo]

Thanks Ace-

My schema for filling in the blanks will be to manually fix these bad records in the table, but this is step 1 where I am looking for a way to identify which records in the table are questionable with this report then I can search on the ID field to fix them one at a time. Don't know if there is an easier way or not.

The operator.Name is not a typo...

operator is a table with the fields Operator and Name
and the operator field from the FPY Table is joined to the the Operator field of the operator table when both fields are equal.

I tried removing this but it doesn't change anything- the query finds nulls in the fields but I am still not able to locate mis-matches between the allowable assembly numbers to their matching operation as these matches are stored in the allowable table.