By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,779 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,779 IT Pros & Developers. It's quick & easy.

sql query one to many count

100+
P: 374
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 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 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:
Expand|Select|Wrap|Line Numbers
  1.  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):

Expand|Select|Wrap|Line Numbers
  1.    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.
Feb 23 '10 #1
Share this Question
Share on Google+
8 Replies


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

Expand|Select|Wrap|Line Numbers
  1. 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!
Feb 23 '10 #2

100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. StaffNumber #ofChecks #ofcheckswitherrors
  2. 100010134  5                 2
  3. 234234212  3                 1
  4. 211865152  10               1
Feb 23 '10 #3

patjones
Expert 100+
P: 931
Hi munkee -

Try this...
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCheck.sStaffNumber, COUNT(tblError.sStaffNumber) AS ErrorCount 
  2. FROM tblCheck LEFT JOIN tblError ON (tblCheck.lTableID = tblError.lCheckID) 
  3. WHERE tblCheck.dteCheckCompletedDate BETWEEN date() AND date()-30 
  4. 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
Feb 25 '10 #4

patjones
Expert 100+
P: 931
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...
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCheck.sStaffNumber, IIf(COUNT(tblError.sStaffNumber)>0, "Y", "N") AS [Has Error(s)?] 
  2.  
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
Feb 25 '10 #5

patjones
Expert 100+
P: 931
Hi again -

Sorry to be a pain, but yet another possibility for the SELECT clause is...
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCheck.sStaffNumber, IIf(tblError.sStaffNumber IS NULL, "N", "Y") AS [Has Error(s)?]
  2.  
...which is merely a slight modification on the IIf formulation.

Pat
Feb 25 '10 #6

aas4mis
P: 97
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.
Feb 26 '10 #7

patjones
Expert 100+
P: 931
Hi -

The JOIN isn't eliminated; I was just pointing out a couple other ways to rewrite the SELECT line...everything 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 // Errorfoundincheck?

100010134 // 2
234234212 // 0

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

Expand|Select|Wrap|Line Numbers
  1. SELECT tblCheck.sStaffNumber, COUNT(tblError.sStaffNumber) AS ErrorCount 
  2. FROM tblCheck LEFT JOIN tblError ON (tblCheck.lTableID = tblError.lCheckID) 
  3. WHERE COUNT(tblError.sStaffNumber) > 0 AND tblCheck.dteCheckCompletedDate BETWEEN date() AND date()-30 
  4. GROUP BY tblCheck.sStaffNumber, tblError.sStaffNumber

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

Pat
Feb 26 '10 #8

aas4mis
P: 97
Ah, I see.. this quick reply box works a little to "quick" for me sometimes. :)
Feb 26 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.