UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Date Parameter In Query For Report, Access 2013    
 
   
masterYoda16
post Feb 5 2018, 03:52 PM
Post#1



Posts: 30
Joined: 8-January 16



I have a report that shows the count of records and the sum of records. I also have a date field that has a date parameter. My problem is that without using the date parameter, I have my records counted ok.

Employee callCenterTickets DateCreated

Doe, Jane 10 01Dec2017
Doe, Jane 33 02Dec2017
Doe, John 15 01Dec2017
Doe, John 22 02Dec2017



What I have been having big problem solving is how to have my report only shows this.


Doe, Jane 43
Doe, John 37

I don't need the date to show on report, but I do need to have it as a way to filter the query that will be used for report ( using between Start date and End Date). Right now with the date parameter, I get the data above. I don't need multiple entries of each employee. just the total count and I need to be able to filter that with a start date, and a end date parameter in query to use for my report. Can UA developers help? a small sample db would be nice to see how to do this right.
Go to the top of the page
 
RJD
post Feb 5 2018, 04:29 PM
Post#2


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


Hi: This should do it...

SELECT Employee, Count(Employee) AS CallCenterTickets
FROM tblMyRecords
WHERE DateCreated Between #12/1/2017# And #12/2/2017#
GROUP BY Employee;

You didn't show the SQL you were using, but I suspect you had the date shown in SELECT rather than just in the WHERE section.

HTH
Joe
Go to the top of the page
 
projecttoday
post Feb 5 2018, 07:44 PM
Post#3


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


Group the report on the name (or its ID).
Go to the top of the page
 
masterYoda16
post Feb 5 2018, 08:14 PM
Post#4



Posts: 30
Joined: 8-January 16



Sorry Itried your advise no luck. here is what I have.

I want to filter my report using the dates, but I don't want the report to show each calls received by employee multiple times. I want that to be counted.
Jane Doe 34 calls total for example
John Doe 20 calls total for example

not like this:

Jane Doe 10 calls
Jane Doe 5 calls
Jane Doe 1 call

John Doe 1 call
John Doe 2 calls
John Doe 1 call

not like this.
This post has been edited by masterYoda16: Feb 5 2018, 08:21 PM
Attached File(s)
Attached File  CallCenter.zip ( 33.53K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Feb 5 2018, 11:12 PM
Post#5


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


Hi: As I suspected (and indicated before in my previous post), you included the date as a field in the SELECT area, and not just the date range in the WHERE clause. That caused your issue. You also needed to adjust the JOIN from an INNER JOIN to the JOIN you see below. Here's what the query should look like...

SELECT employee.employeeID, employee.lastname, employee.firstname, Count(Call.[call#]) AS CallCount
FROM Call RIGHT JOIN employee ON Call.employeeID = employee.employeeID
WHERE (((Call.dateCalled) Between [Forms]![FRM_MyReport]![StartDate] And [Forms]![FRM_MyReport]![EndDate]))
GROUP BY employee.employeeID, employee.lastname, employee.firstname;

This could be adjusted a bit more, but I will let you do that.

See the revision to your db attached.

HTH
Joe

Attached File(s)
Attached File  CallCenter_Rev1.zip ( 29.73K )Number of downloads: 5
 
Go to the top of the page
 
projecttoday
post Feb 5 2018, 11:25 PM
Post#6


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


Attached. I had to make 2 ID's in your calls table where you had the actual lookup values.

Look at report rptCalls.
Attached File(s)
Attached File  CallCenter.zip ( 30.86K )Number of downloads: 15
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 03:45 AM