Thread: sql help

Hi people, Im struggling with this quite hard to explain database query.

How would I count only once the (many) occurance of a record within a one to many relationship.

I have the following tables:

tblCheck:

lTableID
StaffNumber
CheckCompletedDate


tblError:

lCheckID
StaffNumber
ErrorType


The red ID's show the link between the tables. I have a sql string that will count the number of times checks have been carried out between current date and CheckCompletedDate-30days for a staffnumber.

I then however, want to count whether this check resulted in an error. At present I can count the number of times an error occured but sometimes this can be more than once for a single Check. I want a simple yes/no count was there an error or not.

I will then be grouping this by StaffNumber.

So my output would be:
From current date to date-30days:

StaffNumber // ChecksCarriedOut
100010134 // 4
234234212 / / 1

---------

StaffNumber // Errorfoundincheck?
100010134 // 2
234234212 // 0


This will show how many checks had been carried out on different pieces of a staffs work and how many times a piece of work was wrong (not how many errors were within this as a result of the check, just simply if the work was right or wrong).

Using the above examples 4 seperate pieces of work were checked and 2 of these contained errors (the number of errors i dont care about, but my tblErrors shows all of these errors, i just want to know if a check resulted in an error).

I will then be able to calculate a percentage for the number of pieces of work checked against the number of these that were incorrect (even if they contained 20 errors or 1 error, it would still be classed as incorrect).


The query which counts the # of checks correctly:

Code:

 Set Rs = Db.OpenRecordset("SELECT sStaffNumber, count(*) AS checkcount FROM tblcheck WHERE dteCheckCompletedDate BETWEEN date() AND date()-30  Group By sStaffNumber;")

The query which counts the # of errors within these checks (but this is currently the sum of every error within that check so if a case contained 10 errors it counts all 10, i want to just see it say 1 error against this check):

Code:

   Set Rs = Db.OpenRecordset("Select tblError.sStaffNumber, Count(*) As ErrorCount FROM TblError LEFT JOIN tblCheck ON (tblError.lCheckID = tblCheck.lTableID) Where tblCheck.dteCheckCompletedDate BETWEEN date() AND date()-30 GROUP BY tblError.sStaffNumber")

Thanks for any help sorry to go on but its hard to explain. Posted this on another forum also but no answer found.