Grouping with Dates

[dalebeitz]

Greetings!

I have a database that logs salesman activity by date. tblActMaster holds one record for each salesman / date combination, including the salesman name and the date. tblActDetail holds one record for each customer call for a given salesman and date. For this report we only need the master since the presence of a master record for a given salesman and date implies activity on that date.

I'm trying to write a report that groups the activity by salesman and calendar quarter, printing the actual dates in each quarter on which the salesman had activity. I've desinged the report based on a parameter query that restricts the records to be within one calendar year. The report should look something like the following:

HDR: Salesman QTR1 QTR2 QTR3 QTR4
DET: John Doe 1/1, 2/1 4/1 7/1 10/1, 11/1

The above would indicate that John Doe had activity on Jan 1, Feb 1, Apr 1, Jul 1, Oct 1, and Nov 1. Of course, the fields would be wider, and I plan to wrap the list of dates in each quarter. I'm using IIF statements to determine whether a date falls into a given quarter, like this:

QTR1: IIF([ActDate] between #01/01/2002# and #03/31/2002#, [ActDate],Null)

The final version won't have constants for the date range, I'll calculate the proper dates based on the calendar year. The problem I'm having is with the grouping. As soon as I turn grouping on in the query to group by the salesman, Access insists that I have a grouping function on the quarter fields. As near as I can tell, none of the grouping functions that work with dates (max, min, etc.) will do what I want. If all I wanted was to count the number of days that a salesman had activity or something similar, I'd be done by now. But I need to print the actual dates on which the activity occured. What I really need is some sort of concatenation grouping function that would let me build the list of dates via my IFF statement.

I've also tried to write this as a report running directly against tblActMaster instead of through a query, and can't figure out teh grouping there either. If I eliminate the grouping and print each record on it's own detail line, I can make that work. But I really need to print all of the dates for a salesman on one line.

Any ideas greatly appreciated!
Dale Beitz

 

[danvlas]

You need to create your own custom function.
Something like:

Function ColumnToLine(Salesman, SalesQuarter) As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Select [ActDate] From tblActDetail Where [SalesmanField] = '" & Salesman & "' And DatePart("q", [ActDate]) = " & SalesQuarter & ";&quot
With rst
'if no records, result will be ""
If .RecordCount = 0 Then GoTo FinishPoint

'else loop through the dates and build the result
While Not .EOF
ColumnToLine = ![ActDate] & "; "
.MoveNext
Wend
'remove the ending "; "
ColumnToLine = Left(ColumnToLine, Len(ColumnToLine)-2)
FinishPoint:
'close and clean up
.Close
End With
Set rst = Nothing
End Function

Change SalesmanField to the exact name of the field in the table.

QTR1: IIF(DatePart("q",[ActDate]) = 1,ColumnToLine([Salesmanfield,1),Null)
QTR2: IIF(DatePart("q",[ActDate]) = 2,ColumnToLine([Salesmanfield,2),Null)
QTR3: IIF(DatePart("q",[ActDate]) = 3,ColumnToLine([Salesmanfield,3),Null)
QTR1: IIF(DatePart("q",[ActDate]) = 4,ColumnToLine([Salesmanfield,4),Null)

Let me know how it goes...

Dan

 

[Guest_imported]

Thanks, Dan! This looks like exactly what I need. With the long Labor Day weekend coming up it may be a while before I get to try this, but I'll definitely let you know how it works.

Thank you!
Dale Beitz