Displaying totals within a specified date range in Microsoft Access

Question

Although I have experience with SQL and generating HTML reports with PHP, I'm a relative beginner with Microsoft Access.

I'm currently using Microsoft Access 2007 to connect to MSSQL Server 2005.

I have a table of Reports that looks something like this:

ReportID
DateCreated
Author
...

I'd like to create a form that allows the user to specify a start date and an end date, which would then show the number of reports by each author within the specified date range.

I've already done this in a form by first retrieving a list of unique authors into a combo box, and then allowing the user to select the author, start date, and end date, and displaying the count in a text box. However, I was wondering if there was an easier or better way, or if there was a way to display all of the authors and their totals simultaneously.

Thanks in advance :)

Answer 1

You can have multiple fields associated with a combobox, so first have them pick the dates, then initialize the combobox with both author and total field.

Answer 2

This should show you all your information.

SELECT Author, Min(DateCreated) As Earliest, Max(DateCreated) As Latest, count(ReportID) As Titles
FROM YourTable
GROUP By Author
ORDER BY Author

Answer 3

Make 2 unbound text controls for StartDate and EndDate. Put those in the header of a continuous form. Use a button or an AfterUpdate event to change the recordsource of your form. Something like:

me.recordsource = "SELECT author, count(*) from myTable GROUP BY author WHERE DateCreated BETWEEN  #" & format(startDate, "mm/dd/yyyy") & "# AND #" & format(startDate, "mm/dd/yyyy") & "#"