eliminating similar records

[opostal]

I have a database that records 20 fields of information about a record. In two of these fields, it indicates if the record has been tested or not and if it passed or not. These are equated to 1 and 0 for yes and not respectively. In one additional field it indicates what type of test. I then do simple calculations for total tested, total failed and a percentage of the failure.

Here is my problem: You could have several records with identical data less on the field which describes the type of test given. Everything else in that record may be identical to another row, or very similar in that all but the type of test. If any test performed becomes a failure, then everything that applies to that one record or many records is a failure. Is there any way to perform a query or other function that will compare all records except the “test taken” and “test passed/failed” field, use the largest value from each of those columns for otherwise matching records, and then perform final calculations to give me percentages so that item failures equate to one failure for one item, not one failure for three tests thus skewing the percentages of total pass/failed records?

 

[Galaxiom]

I think you need to post more information about the structure of your database.

An image of the relationships window can be useful or a sample of the database itself with just a few dummy records.

BTW I would change the 1/0 fields to a Y/N boolean field. These are more compact and faster to process.

 

[opostal]

Well my apologies for not being clear enough. I am attaching the file here zipped up. I have removed all the bells and whistles from it to ensure that it would be as small as possible. The acutal one I am using will open to the startup form and the user will select the report they wish and provide any applicable information such as date etc.

My source data comes from an excel sheet with macros to separate the columns. That data is then imported into this database for tracking. Let me try to explain my problem better.

If you look at the Main Table items with ID number 37621 and 37620 this should make it easier. These rows are nearly identical until you get to the shot column. One is a T and one a V. This accounts for what angle the test shot was taken at. You should only have values of T, V or T-2, 2-3, 3-4, 4-V in that column. The excel macro goes in and looks for a value in the shot column. If the value does not start with a T, it converts the RTQty value to 0. The next column is RTReject. If the test is acceptable, there is a zero there. If it fails, we assign a 1 value.

If two or more records are identical less the shot column it is the same item being tested from more than one angle. If any shot fails then all of the records pertaining to that item fail as well. Unfortunately with my setup, what can happen is that both a T and V shot will fail and with the reporting you will get a 1 in both the T and V record in the RTReject column. In the RTQty column you will get a 1 and a zero. With the math you wind up with 200% failure which is inaccurate.

What I want the database to do is find the similar records and take the largest RTReject value and assign that for the calculation. That way if they all pass I have a zero percent rejection. If any one of the records that pertain to a specific WeldNumber have a rejection, the calculation will say we have 100% rejection not 200% or larger.

While this is easy with the small demo database I have sent, the real database is tracked with nearly 40000 entries at the moment and data can include literally thousands of records to perform these functions on. My apologies with the authoring of this database as I am in the early stages of learning at this time.

 

[opostal]

Hope that helped to clear it up a bit? Thanks again.