Need to create an alias for the totals of a field in query

[sheckay]

Hello. I need to create a total for a field in a query. Simple query. 1 table. But the problem (for me anyway) is that the field that I'm referring to is in the query twice. Once as a group by and another time as a count (to count the total times a value is in that field of the table). So when I try to create an alias for the sum of that field, how do I tell Access that I'm trying to total the count not the group by?
What got me here was that I was trying to alter a query that gave the sum of tickets closed per technician for the year. I have that functioning without the date criteria. So the query for the sum of closed tickets per tech works fine. But when I put the criteria of the closed date of being less than 365 days old from the current date, the data is there but not in a sum per tech. So I thought I would create an alias of the sum of the count.

This works but doesn't condense them into a total per tech:

SELECT TicketT.StatusID, Count(TicketT.Technician) AS CountOfTechnician, TicketT.Technician
FROM TicketT
GROUP BY TicketT.StatusID, TicketT.Technician, TicketT.TicketDate
HAVING (((TicketT.StatusID)=5) AND ((TicketT.TicketDate)>Date()-365))
ORDER BY Count(TicketT.Technician);

This condenses them in to a total per tech, but it's not restricted to tickets within a year:

SELECT TicketT.Technician, Count(TicketT.Technician) AS CountOfTechnician, TicketT.StatusID
FROM TicketT
GROUP BY TicketT.Technician, TicketT.StatusID
HAVING (((TicketT.StatusID)=5))
ORDER BY Count(TicketT.Technician) DESC;


Thanks in advance.

 

[plog]

I'm trying to total the count not the group by?

Huh? Count is a total. Sum is a total as well.

I think the best way to communicate data issues is with sample data. So please provide 2 sets of data to demonstrate your issue:

A. Starting data from TicketT. Include field names and enough sample data to cover all cases.

B. Expected results of A. Show us what you expect to end up with once you feed the data from A into the final query.

Again, no more words, just data. Give us sample data to show what you want

 

[Pat Hartman]

Once as a group by and another time as a count (to count the total times a value is in that field of the table).

Does not compute. You can't use Count(xxx) to count ANYTHING but the total rows the query returns.

Lots wrong with the query. Needs a WHERE not a HAVING. You are grouping by date so you won't get a single row per technician.

SELECT Count(*) AS CountOfTechnician, TicketT.Technician
FROM TicketT
WHERE TicketT.StatusID =5 AND TicketT.TicketDate >Date()-365
GROUP BY TicketT.Technician
ORDER BY Count(TicketT.Technician);

When you use count(xxx), you are counting the number of non-null instances of technician. Grouping by technician limits the count to a specific technician but that is purely an accident based on your comment. Count(*) is more efficient and counts all rows returned and that gives you what you want. It is the grouping by technician that is giving you the count by technician, NOT using TicketT.Technician in the Count() function.

 

[sheckay]

Does not compute. You can't use Count(xxx) to count ANYTHING but the total rows the query returns.

Lots wrong with the query. Needs a WHERE not a HAVING. You are grouping by date so you won't get a single row per technician.

SELECT Count(*) AS CountOfTechnician, TicketT.Technician
FROM TicketT
WHERE TicketT.StatusID =5 AND TicketT.TicketDate >Date()-365
GROUP BY TicketT.Technician
ORDER BY Count(TicketT.Technician);

When you use count(xxx), you are counting the number of non-null instances of technician. Grouping by technician limits the count to a specific technician but that is purely an accident based on your comment. Count(*) is more efficient and counts all rows returned and that gives you what you want. It is the grouping by technician that is giving you the count by technician, NOT using TicketT.Technician in the Count() function.

You're right. Once I put the "where" info in the date criteria it started working like I was expecting.

 

[Pat Hartman]

Glad to be of help. Count() always confuses newbees.

 

[sheckay]

Glad to be of help. Count() always confuses newbees.

Yeah. It was strange to start having so much trouble with something that was working fine. Especially after entering new criteria that seemed predictable.