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
> Count In Query Returns Duplicates, Access 2016    
 
   
Scooter1
post Jan 13 2018, 03:41 PM
Post#1



Posts: 62
Joined: 8-January 18



Ok, just when I thought I am ready to knock this thing off, one more hit me smack right in the face.

I have these tables

tblStaff
staffID---PK
firstName--txt
lastname--txt
titleID--FK
deptID---FK


tblDept
deptID---PK
dept---(D100, D101, D102 etc)


tblJobTitle
titleID--PK
title---(Tech Support, Manager)




tblServiceTicket
serviceTID---PK
ticket----short text
staffID---FK
completionDate---date


PROBLEM: I want to count total service tickets assigned to each staff (tech support---help desk), less the duplicates. One service ticket can have many services as in removed HD, reimaged, procured new Cat 5 etc...when I use count, it counted the duplicates. I attempted to use Distinct but I kept getting missing parameters and so on.

Missing from this is my junction table.
I think I got that one ok, just when I am attempting to count all the serivice tickets, this is where I have the most problems.

Thank you!

Go to the top of the page
 
RJD
post Jan 13 2018, 04:06 PM
Post#2


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


Hi: You could start with a DISTINCT query (StaffID and Ticket) or Totals query (grouping on StaffID and Ticket), then use that as the source for another query that does the counts. That final query can then be joined to the staff list (with criteria to include just Tech) to get a staff list with count of tickets. This can be done with separate saved queries or one query with sub- and sub-sub-queries.

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 Jan 13 2018, 08:03 PM
Post#3



Posts: 62
Joined: 8-January 18



RJD,

I know you've helped me out a lot on this database, all of your recommendations are being implemented. I have attached what I have so far, most of the corrections are provided by you, and I am hoping to get this wrapped up soon. Ok, so I followed what you suggested, and I think I came close, but no cigar.

What I am attempting to do, is be able to print a report that will have list of all techs with total of all tickets assigned to them, and total of services on each assigned tickets. The report you provided is perfect! the missing pieces are the ones with total ticket assigned, total services for each ticket assigned to staff(in this case techs, because Lead will only be there to assist, but not assigned to any)

So taking this a bit farther:

Team-----Lead Name first and lastname

techs name----total ticket assigned--------total service

Team-----Lead FirstName and lastname
techs name----total ticket assigned--------total service


Team-----Lead Name first and lastname

techs name----total ticket assigned--------total service

Team-----Lead FirstName and lastname
techs name----total ticket assigned--------total service



In the attachment, I have created two queries that I was attempting to resolved my initial problem. I've tried the distinct in the query (view SQL) and it just kept showing errors. Am I missing something here?






Attached File(s)
Attached File  TechSupport.zip ( 51.63K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Jan 14 2018, 09:33 AM
Post#4


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


Okay, you just didn't go far enough.

In qryServiceTicketCounts you must add the StaffID and get rid of the LastName. LastName is not definitive, but the ID is.
In qryAssociates you must add StaffID. Again, this is the linkage now that you are adding associated data, not LastName.
Create a new query using qryServiceTicketCounts to group, count and sum to get the data you need for the report.
Modify qryReportRecords to add the new query, joined by StaffID.
Add the new fields to the report, with sums at dept and total levels if that is what you want.

Also, be careful how you use JOINs. You used some INNER JOINs seemingly by default where LEFT JOINs should be more appropriate.

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 Jan 14 2018, 01:28 PM
Post#5



Posts: 62
Joined: 8-January 18



RJD,

Thank you for all your knowledge sharing and empowerment! I like you to know that I have completed my project and it was a SUCCESS!

Once again UA as come to the rescue and arrived in the nick of time.

Go to the top of the page
 
RJD
post Jan 14 2018, 01:55 PM
Post#6


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


You are very welcome. Glad you got that solved. thumbup.gif

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    22nd September 2018 - 11:46 PM