Query By Date and Count Records

[Laurie]

I have searched the posts and found several suggestions, but they did not
work for the query I need. I am trying to do a query based on a start date
and an end date and have the query total the number of records between that
date for each employee, based on the type of record. An example of the table
follows:

Decision Date Employee Manager Type of Record

01/01/09 John Doe Harry Hughes Denied
01/01/09 John Doe Harry Hughes Approved
02/01/09 John Johnson Mary Jones Approved
02/01/09 John Doe Harry Hughes Approved
03/01/09 Jane Smith Mary Jones Denied

I would like to pull a report by Manager, between certain dates and just
have the total number of records for each record type per employee. For
Example the report would be:

Date of Report: 01/01/09 to 02/01/09

Employee Denied Approved

John Doe 1 2

Manager: Harry Hughes

Thank you,

 

[KARL DEWEY]

Try this --
SELECT [Manager], [Employee], SUM(IIF([Type of Record] = "Denied", 1, 0)) AS
[Denied Records], SUM(IIF([Type of Record] = "Approved", 1, 0)) AS [Approved
Records]
FROM YourTable
WHERE [Decision Date] Between #01/01/09# And #02/01/09#
GROUP BY [Manager], [Employee];

 

[Laurie]

I tried this, but it is still pulling a separate line for each employee. Is
there a way to just have the managers name show once (manager could have
several employees but only want manager name to show once on query), and then
list all of the employees under that manager once and have a total for each
type of record per employee? I think my first post was a little unclear as
to what I wanted to do so here is another example.

Manager Employee Denied Approved

Harry Hughes John Doe 1 2
Jane Smith 3 5
John Jones 1 3

Thank you,
--
Laurie

Try this --
SELECT [Manager], [Employee], SUM(IIF([Type of Record] = "Denied", 1, 0)) AS
[Denied Records], SUM(IIF([Type of Record] = "Approved", 1, 0)) AS [Approved
Records]
FROM YourTable
WHERE [Decision Date] Between #01/01/09# And #02/01/09#
GROUP BY [Manager], [Employee];

I have searched the posts and found several suggestions, but they did not
work for the query I need. I am trying to do a query based on a start date
and an end date and have the query total the number of records between that
date for each employee, based on the type of record. An example of the table
follows:

Decision Date Employee Manager Type of Record

01/01/09 John Doe Harry Hughes Denied
01/01/09 John Doe Harry Hughes Approved
02/01/09 John Johnson Mary Jones Approved
02/01/09 John Doe Harry Hughes Approved
03/01/09 Jane Smith Mary Jones Denied

I would like to pull a report by Manager, between certain dates and just
have the total number of records for each record type per employee. For
Example the report would be:

Date of Report: 01/01/09 to 02/01/09

Employee Denied Approved

John Doe 1 2

Manager: Harry Hughes

Thank you,

 

[KARL DEWEY]

To get the display you want you need to use a report from the query. In
design view click on the Manager text box of the report, then right-click and
scroll down to select properites. Set the Hide Duplicates to Yes.

I tried this, but it is still pulling a separate line for each employee. Is
there a way to just have the managers name show once (manager could have
several employees but only want manager name to show once on query), and then
list all of the employees under that manager once and have a total for each
type of record per employee? I think my first post was a little unclear as
to what I wanted to do so here is another example.

Manager Employee Denied Approved

Harry Hughes John Doe 1 2
Jane Smith 3 5
John Jones 1 3

Thank you,
--
Laurie

Try this --
SELECT [Manager], [Employee], SUM(IIF([Type of Record] = "Denied", 1, 0)) AS
[Denied Records], SUM(IIF([Type of Record] = "Approved", 1, 0)) AS [Approved
Records]
FROM YourTable
WHERE [Decision Date] Between #01/01/09# And #02/01/09#
GROUP BY [Manager], [Employee];

I have searched the posts and found several suggestions, but they did not
work for the query I need. I am trying to do a query based on a start date
and an end date and have the query total the number of records between that
date for each employee, based on the type of record. An example of the table
follows:

Decision Date Employee Manager Type of Record

01/01/09 John Doe Harry Hughes Denied
01/01/09 John Doe Harry Hughes Approved
02/01/09 John Johnson Mary Jones Approved
02/01/09 John Doe Harry Hughes Approved
03/01/09 Jane Smith Mary Jones Denied

I would like to pull a report by Manager, between certain dates and just
have the total number of records for each record type per employee. For
Example the report would be:

Date of Report: 01/01/09 to 02/01/09

Employee Denied Approved

John Doe 1 2

Manager: Harry Hughes

Thank you,