counting unique values.

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I have a report that lists records sorted by employee. Each record has a quantity, a call management ticket number, a client, etc. At the end of each employee I need to total the number of assignments by summing the [quantity]. I also need to tell how many call management tickets each employee had. A ticket could contain multiple records, so I would like to see the following...

Client Assignment Ticket# Qty
-----------------------------------------------------------------------------------
Employee: John smith
AAA Office Supply Service 55435 2
AAA Office Supply Install 55435 1
AAA Office Supply Delivery 56728 1
Jones Automotive Install 54645 2
Jones Automotive Delivery 57688 1
Jones Automotive Delivery 59755 2
Tickets: 5 Assignments: 8

Employee: Phil Wilson
Office Automation, Inc. Delivery 57984 2
Tickets: 1 Assignments: 2




I can count the assignments with no problem, but can't get the tickets right.

Thanks, in advance for your help!

Rick
 
Try creating a query that just does your counting and
totaling. Add the query to you report query.

Jim
-----Original Message-----
I have a report that lists records sorted by employee.
Click to expand...
Each record has a quantity, a call management ticket
number, a client, etc. At the end of each employee I need
to total the number of assignments by summing the
[quantity]. I also need to tell how many call management
tickets each employee had. A ticket could contain multiple
records, so I would like to see the following...
Client Assignment Ticket# Qty
-----------------------------------------------------------------------------------
Employee: John smith
AAA Office Supply Service 55435 2
AAA Office Supply Install 55435 1
AAA Office Supply Delivery 56728 1
Jones Automotive Install 54645 2
Jones Automotive Delivery 57688 1
Jones Automotive Delivery 59755 2
Click to expand...
Tickets: 5
Assignments: 8
Employee: Phil Wilson
Office Automation, Inc.
Click to expand...
Delivery 57984
2Tickets: 1
Assignments: 2
 
Thanks for the help. Can you give me more guidance? How do I do the
counts? Would I need a query to count tickets, a query to count
assignments, and then a query to pull tose together?

Rick
Try creating a query that just does your counting and
totaling. Add the query to you report query.

Jim
-----Original Message-----
I have a report that lists records sorted by employee.
Click to expand...
Each record has a quantity, a call management ticket
number, a client, etc. At the end of each employee I need
to total the number of assignments by summing the
[quantity]. I also need to tell how many call management
tickets each employee had. A ticket could contain multiple
records, so I would like to see the following...
Client Assignment Ticket# Qty
--------------------------------------------------------------------------- --------
Employee: John smith
AAA Office Supply Service 55435 2
AAA Office Supply Install 55435 1
AAA Office Supply Delivery 56728 1
Jones Automotive Install 54645 2
Jones Automotive Delivery 57688 1
Jones Automotive Delivery 59755 2
Click to expand...
Tickets: 5
Assignments: 8
Employee: Phil Wilson
Office Automation, Inc.
Click to expand...
Delivery 57984
2Tickets: 1
Assignments: 2
 
I am not saying this is the best way but i create a query
groups by the desired field. Then I creat a second query
with the previous query as the input. the results of this
query is one line with the total for what you are counting.
Since you have 2 fields you want to count you will have 4
queries. Add these queries to your report query. These
new fields will be your totals. I recomend this heirarchy
for your queries.
main query that does your filtering
main query is the input for both of the first totaling
queries and the input for your report query.

I hope I expalined this correctly. If not you can send me
your email and I will give you my number to call. It is
alot easier to explain over the phone.

Jim

-----Original Message-----
Thanks for the help. Can you give me more guidance? How do I do the
counts? Would I need a query to count tickets, a query to count
assignments, and then a query to pull tose together?

Rick
Try creating a query that just does your counting and
totaling. Add the query to you report query.

Jim
-----Original Message-----
I have a report that lists records sorted by employee.
Click to expand...
Each record has a quantity, a call management ticket
number, a client, etc. At the end of each employee I need
to total the number of assignments by summing the
[quantity]. I also need to tell how many call management
tickets each employee had. A ticket could contain multiple
records, so I would like to see the following...
Client Assignment Ticket# Qty
--------------------------------------------------------------------------- --------
Employee: John smith
AAA Office Supply Service 55435 2
AAA Office Supply Install 55435 1
AAA Office Supply Delivery 56728 1
Jones Automotive Install 54645 2
Jones Automotive Delivery 57688 1
Jones Automotive Delivery 59755 2
Click to expand...
Tickets: 5
Assignments: 8
Employee: Phil Wilson
Office Automation, Inc.
Click to expand...
Delivery 57984
2Tickets: 1
Assignments: 2
I can count the assignments with no problem, but can't get the tickets right.

Thanks, in advance for your help!

Rick
Click to expand...


.
Click to expand...
 
Thanks, Jim!

I think I can work with that.

Rick

I am not saying this is the best way but i create a query
groups by the desired field. Then I creat a second query
with the previous query as the input. the results of this
query is one line with the total for what you are counting.
Since you have 2 fields you want to count you will have 4
queries. Add these queries to your report query. These
new fields will be your totals. I recomend this heirarchy
for your queries.
main query that does your filtering
main query is the input for both of the first totaling
queries and the input for your report query.

I hope I expalined this correctly. If not you can send me
your email and I will give you my number to call. It is
alot easier to explain over the phone.

Jim

-----Original Message-----
Thanks for the help. Can you give me more guidance? How do I do the
counts? Would I need a query to count tickets, a query to count
assignments, and then a query to pull tose together?

Rick
Try creating a query that just does your counting and
totaling. Add the query to you report query.

Jim
-----Original Message-----
I have a report that lists records sorted by employee.
Click to expand...
Each record has a quantity, a call management ticket
number, a client, etc. At the end of each employee I need
to total the number of assignments by summing the
[quantity]. I also need to tell how many call management
tickets each employee had. A ticket could contain multiple
records, so I would like to see the following...
Client Assignment Ticket# Qty
--------------------------------------------------------------------------
Click to expand...
-
--------
Employee: John smith
AAA Office Supply Service 55435 2
AAA Office Supply Install 55435 1
AAA Office Supply Delivery 56728 1
Jones Automotive Install 54645 2
Jones Automotive Delivery 57688 1
Jones Automotive Delivery 59755 2
Click to expand...
Tickets: 5
Assignments: 8
Employee: Phil Wilson
Office Automation, Inc.
Click to expand...
Delivery 57984
2Tickets: 1
Assignments: 2
I can count the assignments with no problem, but can't get the tickets right.

Thanks, in advance for your help!

Rick
Click to expand...


.
Click to expand...
 
Back
Top