Tay
likes garlic
- Local time
- Today, 09:20
- Joined
- May 24, 2002
- Messages
- 269
I've created a database to record the outcome of quality checks that are routinely carried out on several workstreams. This db replaces an 'Excel database' (several linked workbooks) and while the structure and data entry side of this seems sorted, the reports I need to replicate are not.
The problem that I'm struggling with comes from the count I need to do. One check can have many errors of either type (C or P). But, for reporting purposes, I need to count the number of erroneous checks whether a C error or P error, and not the number of times each type of error occurs (regardless of the number of checks).
Whatever I have tried to do in my queries, fails. All I seem to be able to manage is to get a count of each error type per month. So, if Check No 100 has 2 x P errors and 1 x C error, my query returns 2 x P error and 1 x C error. But I need it to show 1 x P error and 1 x C error as the quantity or errors per check is immaterial; it is the fact that both types of error have occured that is of importance.
If it helps, this is a synopsis of my table structure: I have.....
tbl_Error_Lookup = Error_ID and Error_Desc fields
tbl_Error_Type_Lookup = Error_Type_ID, Error_Type and Error_SubType fields
tbl_Join_Error_Type = Error_Type_ID and Error_ID fields
tbl_Check_Errors = Error_ID and Check_ID
tbl_Check = Check_ID, date and several other fields.
Any ideas on how to achieve the required count, or workarounds to this problem would be greatly appreciated.
The problem that I'm struggling with comes from the count I need to do. One check can have many errors of either type (C or P). But, for reporting purposes, I need to count the number of erroneous checks whether a C error or P error, and not the number of times each type of error occurs (regardless of the number of checks).
Whatever I have tried to do in my queries, fails. All I seem to be able to manage is to get a count of each error type per month. So, if Check No 100 has 2 x P errors and 1 x C error, my query returns 2 x P error and 1 x C error. But I need it to show 1 x P error and 1 x C error as the quantity or errors per check is immaterial; it is the fact that both types of error have occured that is of importance.
If it helps, this is a synopsis of my table structure: I have.....
tbl_Error_Lookup = Error_ID and Error_Desc fields
tbl_Error_Type_Lookup = Error_Type_ID, Error_Type and Error_SubType fields
tbl_Join_Error_Type = Error_Type_ID and Error_ID fields
tbl_Check_Errors = Error_ID and Check_ID
tbl_Check = Check_ID, date and several other fields.
Any ideas on how to achieve the required count, or workarounds to this problem would be greatly appreciated.
