UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query For Counting Records Associated With Fk, Access 2016    
 
   
Scooter1
post Mar 4 2018, 09:50 AM
Post#1



Posts: 62
Joined: 8-January 18



I need a query to show these:

auditor name------totalrecord(count)----reviewer* (count)---performance errors(sum)----returned for review(count)

*the count of reviewerID associated to each corresponding record and auditor name where record was under review(check box is yes).


This is what I need:
Name---totalRecord-----totalReview(based on reviewerID)----totalReturnedforReview
Brown---------3-------------1-----------------1
Seally---------3-------------2-----------------2


Please see tblPerformance and tblPerformanceErrors to see these two records.

Hoping UA experts can point me in the right direction.
Attached File(s)
Attached File  StaffPerformance__2_.zip ( 67.94K )Number of downloads: 4
 
Go to the top of the page
 
cheekybuddha
post Mar 4 2018, 10:14 AM
Post#2


UtterAccess VIP
Posts: 9,978
Joined: 6-December 03
From: Telegraph Hill


Please list the fieldnames and datatypes of each table involved (I can't open A2016 db's)

--------------------


Regards,

David Marten
Go to the top of the page
 
Scooter1
post Mar 4 2018, 03:28 PM
Post#3



Posts: 62
Joined: 8-January 18



tblJobTitle

titleID---Auto/PK
jobTitle--short text


----

tblPerformance

wpID--Auto/PK
staffID--number
performance--short text
auditID--number
reviewID--number
dateEvaluated--Date/Time
auditComment--short text
returnedForReview--yes/no


-----

tblPerformanceErrors

prErrorID--auto/PK
wpID--number
pErrID--number

------
tblpErrors
pErrID---auto/PK
perfError--short text

------

tblStaff

staffID--auto/pk
fName---short text
lName---short text
titleID--number



























Go to the top of the page
 
RJD
post Mar 4 2018, 04:12 PM
Post#4


UtterAccess VIP
Posts: 8,370
Joined: 25-October 10
From: Gulf South USA


Hi: You said ... "*the count of reviewerID..." ...

Did you mean the ReviewID? Or did you mean the StaffID of the person doing the review?

And we did something like this before. Can you use that previous work to develop a set of queries for this requirement as well?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Scooter1
post Mar 4 2018, 05:47 PM
Post#5



Posts: 62
Joined: 8-January 18



Hi everyone and RJD,

All day today, been working on getting this completed, since the combo boxes has different jobtitleID, it makes my query even more confusing on how to even get it off the ground. I am attaching the db. See query3 that is what I am attempting to do, and not successful.

Here is what I am actually trying to accomplish.

I need a query that I will use it to create my report. My report will have these following fields:

Total records completed or audited by that staff, total combined records(reports/performance evaluation reports) for that reporting period(query should have dateEvaluated has the date criteria for filtering), I will need a sum of all problems discovered on the evaluation reports for each staff(who audited the report), and lastly the total count of ReturnedForReview(this is done by a reviewer, but the count should correlated the staff who did the auditing.

So in summary: this query is about finding the metrics for the staff who was assigned to do the auditing.

In the table Performance and tblPerformanceErrors, there are 3 staff whose jobtitleID were assigned to do the auditing they are ID: 4, 5 and 9. These are the staff that the query should based on.

so here it is:
ID-------name---------total records-------QC review-------total records-------total performance errors on report

4------Kotaschi--------------3--------------------1-----------------3--------------------3
5------Brown----------------3--------------------1-----------------3---------------------3
9------Seally----------------3---------------------2-----------------3---------------------3

I hope this makes sense, I have looked what RJD provided, and I guess because I am not dealing with the staff who are being evaluated, this created confusion on my part...as a result, I created query 3, but not able to get the numbers I was looking for, I could not move to the next query.






Attached File(s)
Attached File  StaffPerformance__2_.zip ( 60.43K )Number of downloads: 0
 
Go to the top of the page
 
Scooter1
post Mar 4 2018, 08:25 PM
Post#6



Posts: 62
Joined: 8-January 18



RJD,

I think I've got this corrected, my only issue now is how to add the name of the staff that were assigned as the auditor and reviewer. The query5(sorry I am pressed for time, so naming these queries appropriately is not on my priority list) is what I think I was attempting to do.

I need to be able to show the names as what has been said above; so when I used this on my report they will show up in a readable form.


Thank you all...
Attached File(s)
Attached File  StaffPerformance.zip ( 63.03K )Number of downloads: 2
 
Go to the top of the page
 
DanielPineault
post Mar 4 2018, 09:06 PM
Post#7


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



What about something like

SELECT Query4.auditID, [tblStaff].[fName] & " " & [tblStaff].[lName] AS Auditor, [tblStaff_1].[fName] & " " & [tblStaff_1].[lName] AS Reviewer, Count(Query4.wpID) AS CountOfwpID, Sum(Query4.CountOfpErrID) AS SumOfCountOfpErrID, Sum(Query4.Ret) AS SumOfRet, Count(Query4.reviewID) AS CountOfreviewID
FROM ((Query4 INNER JOIN tblPerformance ON Query4.wpID = tblPerformance.wpID) INNER JOIN tblStaff ON tblPerformance.auditID = tblStaff.staffID) INNER JOIN tblStaff AS tblStaff_1 ON tblPerformance.reviewID = tblStaff_1.staffID
GROUP BY Query4.auditID, [tblStaff].[fName] & " " & [tblStaff].[lName], [tblStaff_1].[fName] & " " & [tblStaff_1].[lName];

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
RJD
post Mar 5 2018, 06:46 PM
Post#8


UtterAccess VIP
Posts: 8,370
Joined: 25-October 10
From: Gulf South USA


Hi: If Query5 gives you the data you want, then see the mod to Query5 in the attached revision to your db, with the auditor name included.

HTH
Joe
Attached File(s)
Attached File  StaffPerformance_Rev1.zip ( 35.38K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Scooter1
post Mar 5 2018, 10:35 PM
Post#9



Posts: 62
Joined: 8-January 18



RJD,

That is exactly what I was looking for. Thank you very much!

Everyone who contributed, thank you as well!!!!!!!!!!!!!!!!!!

Go to the top of the page
 
RJD
post Mar 5 2018, 10:45 PM
Post#10


UtterAccess VIP
Posts: 8,370
Joined: 25-October 10
From: Gulf South USA


You are very welcome. We are all happy to assist.

Regards
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 02:21 AM