Impossible Query..

[Guest]

....I'm sure it's possible.

I have a table that shows each month of the year. During each month one
person is assigned to complete a certain # of reviews for a CSR during the
1st half of the month, and another person is assigned to do reviews for the
same CSR during the 2nd half of the month. So the fields in the table are:
Month, CSR, 1st Half Person, # of Reviews Needed 1st Half, 2nd Half Person, #
Reviews Needed 2nd Half. This table is called Monthly Assignments.

I have another table called Reviews. It lists all the reviews done by
person by month for each CSR. The fields in this table are: Month,CSR,
Claim Number, Reviewer. There are many more fields in each table but these
are the main ones.

How do I create a query (or multiple queries) that will show me how many
reviews were assigned to each reviewer each month, and how many reviews they
acutally completed?

 

[Jason Lepack]

I notice that you don't divide your reviews up into half months, so
this may or may not work.



SELECT
A.Month,
A.CSR,
A.[1st Half Person],
A.[# of Reviews Needed 1st Half],
(
select
count(*)
from
Reviews as B
where
B.CSR=A.CSR
and B.Reviewer = A.[1st Half Person]
and A.Month = B.Month) AS [Reviews Done 1st Half],
A.[2nd Half Person],
A.[# of Reviews Needed 2nd Half],
(
select
count(*)
from
Reviews as C
where
C.CSR=A.CSR
and C.Reviewer = A.[2nd Half Person]
and A.Month = C.Month) AS [Reviews Done 2nd Half]
FROM
[Monthly Assignments] AS A;

 

[Guest]

Thanks for the response. Isn't there a way to just sum the # of assignments
one reviewer has for each month?

I notice that you don't divide your reviews up into half months, so
this may or may not work.



SELECT
A.Month,
A.CSR,
A.[1st Half Person],
A.[# of Reviews Needed 1st Half],
(
select
count(*)
from
Reviews as B
where
B.CSR=A.CSR
and B.Reviewer = A.[1st Half Person]
and A.Month = B.Month) AS [Reviews Done 1st Half],
A.[2nd Half Person],
A.[# of Reviews Needed 2nd Half],
(
select
count(*)
from
Reviews as C
where
C.CSR=A.CSR
and C.Reviewer = A.[2nd Half Person]
and A.Month = C.Month) AS [Reviews Done 2nd Half]
FROM
[Monthly Assignments] AS A;

...I'm sure it's possible.

I have a table that shows each month of the year. During each month one
person is assigned to complete a certain # of reviews for a CSR during the
1st half of the month, and another person is assigned to do reviews for the
same CSR during the 2nd half of the month. So the fields in the table are:
Month, CSR, 1st Half Person, # of Reviews Needed 1st Half, 2nd Half Person, #
Reviews Needed 2nd Half. This table is called Monthly Assignments.

I have another table called Reviews. It lists all the reviews done by
person by month for each CSR. The fields in this table are: Month,CSR,
Claim Number, Reviewer. There are many more fields in each table but these
are the main ones.

How do I create a query (or multiple queries) that will show me how many
reviews were assigned to each reviewer each month, and how many reviews they
acutally completed?

 

[Jason Lepack]

select
reviewer,
count(*)
from
reviews
group by
reviewer

Thanks for the response. Isn't there a way to just sum the # of assignments
one reviewer has for each month?

I notice that you don't divide your reviews up into half months, so
this may or may not work.

SELECT
A.Month,
A.CSR,
A.[1st Half Person],
A.[# of Reviews Needed 1st Half],
(
select
count(*)
from
Reviews as B
where
B.CSR=A.CSR
and B.Reviewer = A.[1st Half Person]
and A.Month = B.Month) AS [Reviews Done 1st Half],
A.[2nd Half Person],
A.[# of Reviews Needed 2nd Half],
(
select
count(*)
from
Reviews as C
where
C.CSR=A.CSR
and C.Reviewer = A.[2nd Half Person]
and A.Month = C.Month) AS [Reviews Done 2nd Half]
FROM
[Monthly Assignments] AS A;

- Show quoted text -

 

[Jason Lepack]

Forgot to add the month.

select
reviewer,
month,
count(*)
from
reviews
group by
reviewer,
month

Thanks for the response. Isn't there a way to just sum the # of assignments
one reviewer has for each month?

I notice that you don't divide your reviews up into half months, so
this may or may not work.

SELECT
A.Month,
A.CSR,
A.[1st Half Person],
A.[# of Reviews Needed 1st Half],
(
select
count(*)
from
Reviews as B
where
B.CSR=A.CSR
and B.Reviewer = A.[1st Half Person]
and A.Month = B.Month) AS [Reviews Done 1st Half],
A.[2nd Half Person],
A.[# of Reviews Needed 2nd Half],
(
select
count(*)
from
Reviews as C
where
C.CSR=A.CSR
and C.Reviewer = A.[2nd Half Person]
and A.Month = C.Month) AS [Reviews Done 2nd Half]
FROM
[Monthly Assignments] AS A;

- Show quoted text -

 

[Steve]

What will make it possible is to change the design of your tables!!

For a recommendation of the tables needed, please explain what a CSR is and
is a Reviewer in the Reviews table, the same as 1st Half Person and 2nd Half
Person in the Assignments table?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Genius reply Jason, but you are right, our reviews are not divided into half
months. When I was asking about just summing the reviews assigned to each
reviewer, I meant from the Monthly Assignments table. I have no problem
pulling the number of reviews completed by each reviewer (from the reviews
table) but I am stuck at how to calculate the number of reviews assigned to
each reviewer for the month. Unfortunately I am unable to change the layout
of the tables as Steve suggested.

I notice that you don't divide your reviews up into half months, so
this may or may not work.



SELECT
A.Month,
A.CSR,
A.[1st Half Person],
A.[# of Reviews Needed 1st Half],
(
select
count(*)
from
Reviews as B
where
B.CSR=A.CSR
and B.Reviewer = A.[1st Half Person]
and A.Month = B.Month) AS [Reviews Done 1st Half],
A.[2nd Half Person],
A.[# of Reviews Needed 2nd Half],
(
select
count(*)
from
Reviews as C
where
C.CSR=A.CSR
and C.Reviewer = A.[2nd Half Person]
and A.Month = C.Month) AS [Reviews Done 2nd Half]
FROM
[Monthly Assignments] AS A;

...I'm sure it's possible.

I have a table that shows each month of the year. During each month one
person is assigned to complete a certain # of reviews for a CSR during the
1st half of the month, and another person is assigned to do reviews for the
same CSR during the 2nd half of the month. So the fields in the table are:
Month, CSR, 1st Half Person, # of Reviews Needed 1st Half, 2nd Half Person, #
Reviews Needed 2nd Half. This table is called Monthly Assignments.

I have another table called Reviews. It lists all the reviews done by
person by month for each CSR. The fields in this table are: Month,CSR,
Claim Number, Reviewer. There are many more fields in each table but these
are the main ones.

How do I create a query (or multiple queries) that will show me how many
reviews were assigned to each reviewer each month, and how many reviews they
acutally completed?

 

[Jason Lepack]

So my solution did, or did not work?

If it didn't work, please post sample data as well as expected output
so that I can help you further.

Cheers,
Jason Lepack

Genius reply Jason, but you are right, our reviews are not divided into half
months. When I was asking about just summing the reviews assigned to each
reviewer, I meant from the Monthly Assignments table. I have no problem
pulling the number of reviews completed by each reviewer (from the reviews
table) but I am stuck at how to calculate the number of reviews assigned to
each reviewer for the month. Unfortunately I am unable to change the layout
of the tables as Steve suggested.

I notice that you don't divide your reviews up into half months, so
this may or may not work.

SELECT
A.Month,
A.CSR,
A.[1st Half Person],
A.[# of Reviews Needed 1st Half],
(
select
count(*)
from
Reviews as B
where
B.CSR=A.CSR
and B.Reviewer = A.[1st Half Person]
and A.Month = B.Month) AS [Reviews Done 1st Half],
A.[2nd Half Person],
A.[# of Reviews Needed 2nd Half],
(
select
count(*)
from
Reviews as C
where
C.CSR=A.CSR
and C.Reviewer = A.[2nd Half Person]
and A.Month = C.Month) AS [Reviews Done 2nd Half]
FROM
[Monthly Assignments] AS A;

- Show quoted text -

 

[Guest]

No the solution did not work since the reviews are not in halves. Here is
some sample data:

***Monthly Assignments Table (not sure how to post the data, hopefully this
makes sense):
The fields are: Month, Last Name, Total Reviews Needed, 1st Reviewer
Initials, # Needed 1st Half, 2nd Reviewer Initials, # Needed 2nd Half

Record 1: 2007-07, Johnson, 10, BAB, 6, VHM, 4
Record 2: 2007-07, Adams, 10, VHM, 5, BAB, 5
Record 3: 2007-06, Smith, 12, BAB, 7, VHM, 5

** Reviews Table
The fields are: Month, Last Name, Reviewer Initials, Claim Number

Record 1: 2007-07,Johnson, BAB, ABC1234
Record 2: 2007-07,Johnson, BAB, ABC1235
Record 3: 2007-07,Johnson, BAB, ABC1236
Record 4: 2007-07,Adams, BAB, ABC1237
Record 5: 2007-07,Adams, BAB, ABC1238
Record 6: 2007-06,Smith, BAB, ABC1239
Record 7: 2007-07,Johnson, VHM, ABC1221
Record 8: 2007-07,Johnson, VHM, ABC1222
Record 9: 2007-07,Johnson, VHM, ABC1223
Record 10: 2007-07,Johnson, VHM, ABC1224
Record 11: 2007-06,Smith, VHM, ABC1225


**Desired Query Results

BAB Results
2007-07 Assigned: 11 reviews Completed: 5 Reviews
2007-06 Assigned: 7 reviews Completed: 1 Review

VHM Results
2007-07 Assigned: 9 reviews Completed: 4 Reviews
2007-06 Assigned:5 Reviews Completed: 1 Review

So my solution did, or did not work?

If it didn't work, please post sample data as well as expected output
so that I can help you further.

Cheers,
Jason Lepack

Genius reply Jason, but you are right, our reviews are not divided into half
months. When I was asking about just summing the reviews assigned to each
reviewer, I meant from the Monthly Assignments table. I have no problem
pulling the number of reviews completed by each reviewer (from the reviews
table) but I am stuck at how to calculate the number of reviews assigned to
each reviewer for the month. Unfortunately I am unable to change the layout
of the tables as Steve suggested.

I notice that you don't divide your reviews up into half months, so
this may or may not work.

SELECT
A.Month,
A.CSR,
A.[1st Half Person],
A.[# of Reviews Needed 1st Half],
(
select
count(*)
from
Reviews as B
where
B.CSR=A.CSR
and B.Reviewer = A.[1st Half Person]
and A.Month = B.Month) AS [Reviews Done 1st Half],
A.[2nd Half Person],
A.[# of Reviews Needed 2nd Half],
(
select
count(*)
from
Reviews as C
where
C.CSR=A.CSR
and C.Reviewer = A.[2nd Half Person]
and A.Month = C.Month) AS [Reviews Done 2nd Half]
FROM
[Monthly Assignments] AS A;

On Jul 6, 4:06 pm, nperpill <[email protected]>
wrote:
...I'm sure it's possible.

I have a table that shows each month of the year. During each month one
person is assigned to complete a certain # of reviews for a CSR during the
1st half of the month, and another person is assigned to do reviews for the
same CSR during the 2nd half of the month. So the fields in the table are:
Month, CSR, 1st Half Person, # of Reviews Needed 1st Half, 2nd Half Person, #
Reviews Needed 2nd Half. This table is called Monthly Assignments.

I have another table called Reviews. It lists all the reviews done by
person by month for each CSR. The fields in this table are: Month,CSR,
Claim Number, Reviewer. There are many more fields in each table but these
are the main ones.

How do I create a query (or multiple queries) that will show me how many
reviews were assigned to each reviewer each month, and how many reviews they
acutally completed?- Hide quoted text -

- Show quoted text -

 

[Jason Lepack]

I ended up doing using 4 queries, 3 of them were intermediate. The
first was a Union Query to get a list of all the Required Reviews by
month and reviewer. The second just aggregated them because there
were duplicates based on month and reviewer. The third collected the
amount of reviews by required reviewer and month. The 4th put them
all together.

I then created a report that diplays like your example.

You can see the results in a database I uploaded here.

http://www.megaupload.com/?d=DZEUYWRP

Cheers,
Jason Lepack

No the solution did not work since the reviews are not in halves. Here is
some sample data:

***Monthly Assignments Table (not sure how to post the data, hopefully this
makes sense):
The fields are: Month, Last Name, Total Reviews Needed, 1st Reviewer
Initials, # Needed 1st Half, 2nd Reviewer Initials, # Needed 2nd Half

Record 1: 2007-07, Johnson, 10, BAB, 6, VHM, 4
Record 2: 2007-07, Adams, 10, VHM, 5, BAB, 5
Record 3: 2007-06, Smith, 12, BAB, 7, VHM, 5

** Reviews Table
The fields are: Month, Last Name, Reviewer Initials, Claim Number

Record 1: 2007-07,Johnson, BAB, ABC1234
Record 2: 2007-07,Johnson, BAB, ABC1235
Record 3: 2007-07,Johnson, BAB, ABC1236
Record 4: 2007-07,Adams, BAB, ABC1237
Record 5: 2007-07,Adams, BAB, ABC1238
Record 6: 2007-06,Smith, BAB, ABC1239
Record 7: 2007-07,Johnson, VHM, ABC1221
Record 8: 2007-07,Johnson, VHM, ABC1222
Record 9: 2007-07,Johnson, VHM, ABC1223
Record 10: 2007-07,Johnson, VHM, ABC1224
Record 11: 2007-06,Smith, VHM, ABC1225

**Desired Query Results

BAB Results
2007-07 Assigned: 11 reviews Completed: 5 Reviews
2007-06 Assigned: 7 reviews Completed: 1 Review

VHM Results
2007-07 Assigned: 9 reviews Completed: 4 Reviews
2007-06 Assigned:5 Reviews Completed: 1 Review

So my solution did, or did not work?

If it didn't work, please post sample data as well as expected output
so that I can help you further.

Cheers,
Jason Lepack

Genius reply Jason, but you are right, our reviews are not divided into half
months. When I was asking about just summing the reviews assigned to each
reviewer, I meant from the Monthly Assignments table. I have no problem
pulling the number of reviews completed by each reviewer (from the reviews
table) but I am stuck at how to calculate the number of reviews assigned to
each reviewer for the month. Unfortunately I am unable to change the layout
of the tables as Steve suggested.
:
I notice that you don't divide your reviews up into half months, so
this may or may not work.
SELECT
A.Month,
A.CSR,
A.[1st Half Person],
A.[# of Reviews Needed 1st Half],
(
select
count(*)
from
Reviews as B
where
B.CSR=A.CSR
and B.Reviewer = A.[1st Half Person]
and A.Month = B.Month) AS [Reviews Done 1st Half],
A.[2nd Half Person],
A.[# of Reviews Needed 2nd Half],
(
select
count(*)
from
Reviews as C
where
C.CSR=A.CSR
and C.Reviewer = A.[2nd Half Person]
and A.Month = C.Month) AS [Reviews Done 2nd Half]
FROM
[Monthly Assignments] AS A;
On Jul 6, 4:06 pm, nperpill <[email protected]>
wrote:
...I'm sure it's possible.
I have a table that shows each month of the year. During each month one
person is assigned to complete a certain # of reviews for a CSR during the
1st half of the month, and another person is assigned to do reviews for the
same CSR during the 2nd half of the month. So the fields in the table are:
Month, CSR, 1st Half Person, # of Reviews Needed 1st Half, 2nd Half Person, #
Reviews Needed 2nd Half. This table is called Monthly Assignments.
I have another table called Reviews. It lists all the reviews done by
person by month for each CSR. The fields in this table are: Month,CSR,
Claim Number, Reviewer. There are many more fields in each table but these
are the main ones.
How do I create a query (or multiple queries) that will show me how many
reviews were assigned to each reviewer each month, and how many reviews they
acutally completed?- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -