Report to count records

[Stuart Green]

Fairly new to Access, having moved from DataEase but am having problems with what should be a fairly straight forward report. I have a table which is used to record enquiries received. On this are 2 fields, one for date enquiry received and one for date answered. I want to run a report based on a specific date range that not only counts the number of enquiries received within this period but also the number where the date answered field is blank (enquiry is still being dealt with) and those where the time taken to answer is longer than 5 days, and also express these as percentages, eg x% were dealt with within 5 days. Many thanks

 

[Paul_Bricker]

Stuart, to get the Count of Date Enquiry Received you should be able to use a textbox in the Report Footer with the Control Source set to

=Count([Date Enquiry Received])

replace [Date Enquiry Received] with the actual name of the Textbox on your Report.

To get the number of Null Date Enquiry Answered use

=Sum(IIf(IsNull([Date Enquiry Answered]),1,0))

Again change the name to the name of the textbox on your Report. To check for answers greater than 5 days use

=Sum(IIf(DateDif("d",[Date Enquiry Received], [Date Enquiry Answered]) > 5,1,0))

To get the percentage that is less than 5 days try,

Sum(IIf(DateDif("d",[Date Enquiry Received], [Date Enquiry Answered]) < 5,1,0))/Count([Date Enquiry Received])

These textboxes will all need to be in the Report Footer. Post back with any specific problems.

Paul

 

[Stuart Green]

Thanks very much Paul that sorted it out for me and taught me a thing or 2 at the same time

 

[Paul_Bricker]

Great, glad it helped.
Paul