Recent Date in report

[shakenama]

I've designed a Database involving quality assurance for our call center. I've created one report which lists the dispatchers names, and monitoring pass/fail categories. The source data for this is a table called Monitoring.tbl

I've created another report named Dispatchers which lists the current dispatchers in the database. This report looks up the names in Dispatchers.tbl

But I'd like to include a column in the Dispatcher report on the most recent date that each Dispatcher was monitored which is in Monitoring.tbl

I've tried to run the Dispatcher report from a query which includes First and Last Name from Dispatcher.tbl and most Recent Date of each record from Monitoring.tbl (with Total order by Max) but this doesn't bring up any dates at all in the report.

Can I use VB in this, or does this have to go thru a query?

 

[ajetrumpet]

But I'd like to include a column in the Dispatcher report on the most recent date that each Dispatcher was monitored which is in Monitoring.tbl

This is an aggregate grouping. The function that is grouped is MAX. In order to get the data from both tables, you will need to join them in your query, and group by First and Last name from the dispatcher table. No coding is need for this. Your query will look like (assuming a one-to-many relationship between the dispatcher table and monitoring table (on dispatcher name, or ID))....

SELECT dispatcher.firstname, dispatcher.lastname, 
MAX(monitoring.DateMonitored) AS [Most Recent Date Monitored]

          FROM monitoring.DateMonitored INNER JOIN dispatcher ON
          dispatcher.LinkedField = monitoring.LinkedField

GROUP BY dispatcher.firstname, dispatcher.lastname

That will give you the most recent dates for each record in the monitoring table with a unique combination of First Name and Last Name (e.g. - every dispatcher).

The FAQ on this topic is here.

 

[shakenama]

RE: Recent Date in Report

This looks great...I've been off for a while so a let ya know how it turns out... thanks