Count of fields 1

[kastaman]

Hi there,

I hope somebody can shed some light to my problem.
I have a table which contains:
Employee
Request #
Date Opened
Date Closed

My requirement is to count the # by Employee on a specific date the number of Request Opened and Closed but the count for Opened should not count anything closed on the specified date to prevent duplicate counts.

My hope for the report should sort of appear like:

Employee:
Adam
Date Opened - 15
Date Closed - 10
Totals - 25

Joe
Date Opened - 5
Date Closed - 21
Totals - 26
Thanks in advance,

Kastaman

 

[BSman]

This query counts the number opened and not closed (yourdate is the value of the date you want to use):

qgrpOpened:
SELECT [Employee], "Opened", COUNT([Request #]) FROM yourtable GROUP BY [Employee]
WHERE [Date Opened] = yourdate AND IsNull([Date Closed])

This query counts the number closed:

qgrpClosed:
SELECT [Employee], "Closed", COUNT([Request #]) FROM yourtable GROUP BY [Employee]
WHERE [Date Closed] = yourdate

Then put the two together (I haven't tested this code, so you will probably have to tweak it):

qgrpAll:
SELECT * FROM qgrpClosed UNION SELECT * FROM qgrpOpened

 

[kastaman]

BSman,

Thanks a ton!! I had the 2 queries but it didn't dawn on me to use union query.

I'm just creating the report and everything looks good.

Thanks in advance,

Kastaman

 

[kastaman]

BSman,

I have a slight problem, while counting the # for calls closed the same query counts the calls opened for the same date.

I added the date opened field coz the union query requires the fields to equal. Thanks in advance,

Kastaman

 

[BSman]

Try changing the queries to something like this:

qgrpOpened:
SELECT [Employee], "Opened" AS Status, COUNT([Request #]) FROM yourtable GROUP BY [Employee]
WHERE [Date Opened] = yourdate AND IsNull([Date Closed])

This query counts the number closed:

qgrpClosed:
SELECT [Employee], "Closed" AS Status, COUNT([Request #]) FROM yourtable GROUP BY [Employee]
WHERE [Date Closed] = yourdate


Then the result of the union query will have the fields:
Employee
Status
Count of Request #

 

[kastaman]

BSman,

Thanks it works. What I was trying to do is to seperately count the # of opened and closed calls by Employee. Can this be done with within the union query? Thanks in advance,

Kastaman

 

[BSman]

If you're looking to make a report that is something like this:

Employee Opened Calls Closed Calls
Jane Smith 50 48

Then set up your report so that it sorts by employee and has a group footer by employee.

In the detail section, put two controls: txtStatus and txtCount, where each contains the status and the count. In code behind the detail section for the onFormat (or onPrint sometimes works better) put the following code, where the employee group footer contains the following controls: txtEmployee (using the contents of the employee field), txtCountOpen, txtCountClosed.

IF me.txtStatus = "Closed" then
me.txtCountClosed = nz(me.txtCountClosed) + nz(me.txtCount)
ELSE
me.txtCountOpen = nz(me.txtCountOpen) + nz(me.txtCount)
END IF

In the employee group header put the following code for onFormat (or onPrint):

me.txtCountOpen = 0
me.txtCountClosed = 0

Experiment with setting the employee header and the detail sections property of visible to false (or making them 0" high).

You'll have to play around with the onFormat/onPrint properties for the best place to put the code, but this is an easy way to get the report that I think you are looking for.

 

[kastaman]

BSman,

Sorry I took too long to reply. Thanks for your assistance as I've accomplished what I required.

Thanks in advance,

Kastaman