sql query one to many count

[munkee]

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
CheckCompletedD ate


tblError:

lCheckID
StaffNumber
ErrorType


The bold 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 CheckCompletedD ate-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 // ChecksCarriedOu t
100010134 // 4
234234212 / / 1

---------

StaffNumber // Errorfoundinche ck?
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:

 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):

   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.

[aas4mis]

Instead of using "count(*)" try using "1" (with the quotes), this will return the string "1", which could just as easily be "error".

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

This already filters out the "non error" staff members due to your join, but you probably already knew that. :)

Side note: Not sure if access automatically does the sort for you, but you may want to put an ORDER BY clause in there.. else you might get duplicate staff id's..

for example..

100010134 / 1 <-- need to fire that guy. :P
234234212 / 1
211865152 / 1
100010134 / 1 <-- need to fire that guy. :P

Hope that helped!

[munkee]

Thanks for this aas4mis i've been teaching myself over the last week so it really got me stuck easy. I will give your code a go at work tomorrow, i'm in a strange situation where I don't actually have Access available to me. At present i'm importing the data in to excel and running queries from there from an access database file that is used by a different department so solutions aren't as easy to work out as I would have liked.

Just had a good read of the code. Would I then have to do a Count() to get the total number of times that staff member had a case appear between the dates specified?

So I basically can output on a summary sheet using your above example and saying they had 5 checks done:

StaffNumber #ofChecks #ofcheckswitherrors
100010134  5                 2
234234212  3                 1
211865152  10               1

[patjones]

Hi munkee -

Try this...

SELECT tblCheck.sStaffNumber, COUNT(tblError.sStaffNumber) AS ErrorCount 
FROM tblCheck LEFT JOIN tblError ON (tblCheck.lTableID = tblError.lCheckID) 
WHERE tblCheck.dteCheckCompletedDate BETWEEN date() AND date()-30 
GROUP BY tblCheck.sStaffNumber, tblError.sStaffNumber

The key here is to GROUP BY on sStaffNumber in each table. I tried this query in one of my Access databases that has a similar arrangement to yours and it worked nicely.

Pat

[patjones]

munkee -

I hasten to add that for a simple Y/N entry you can modify the SELECT clause of my SQL with an IIf statement as follows...

SELECT tblCheck.sStaffNumber, IIf(COUNT(tblError.sStaffNumber)>0, "Y", "N") AS [Has Error(s)?] 
 

One of the nice things about using SQL in an Excel or Access environment is that you can embed VBA functionality like "IIf" in the query. I hope this helps.

Pat

[patjones]

Hi again -

Sorry to be a pain, but yet another possibility for the SELECT clause is...

SELECT tblCheck.sStaffNumber, IIf(tblError.sStaffNumber IS NULL, "N", "Y") AS [Has Error(s)?]
 

...which is merely a slight modification on the IIf formulation.

Pat

[aas4mis]

zepphead80,
Nice way to eliminate the join! Should this also have a WHERE clause now since there's no join?

munkee,
Keep an eye out for this one, I believe if you're populating a list/combobox with the results you'll have an entry for every staffNumber, even if they don't have an error.

zepphead80,
Please correct me if I'm wrong.

[patjones]

Hi -

The JOIN isn't eliminated; I was just pointing out a couple other ways to rewrite the SELECT line...everythi ng else would stay the same. Sorry for the confusion.

I inferred from munkee's original post that he wanted to show staff even if they had no errors, as in

StaffNumber // Errorfoundinche ck?

100010134 // 2
234234212 // 0

However, to eliminate those staff, the WHERE clause in my query just needs to be modified:

SELECT tblCheck.sStaffNumber, COUNT(tblError.sStaffNumber) AS ErrorCount 
FROM tblCheck LEFT JOIN tblError ON (tblCheck.lTableID = tblError.lCheckID) 
WHERE COUNT(tblError.sStaffNumber) > 0 AND tblCheck.dteCheckCompletedDate BETWEEN date() AND date()-30 
GROUP BY tblCheck.sStaffNumber, tblError.sStaffNumber

Hopefully munkee can let us know whether or not this works for him.

Pat

[aas4mis]

Ah, I see.. this quick reply box works a little to "quick" for me sometimes. :)