Crosstab Query

[dmorse]

I have a start time and end time on a call log database (and of course a bunch of other information). Anyway, I need to display the time lapse in different views, and allow them to enter a date range. The time lapse needs to be hh:mm:ss I can get the detail to show correctly, but it says that the crosstab is too complicated if I try to format it and display it summed in the crosstab.

Any suggestions?

 

[VicRauch]

Would really help to have all the particulars here. Don't need to know all the fields you have, only the ones you want to show in the crosstab query, which ones are Rows, Column, and Data, and the format code you are using. Also, include the SQL you have used so far.

 

[dmorse]

tblCallHeader contains the following fields:CallNbr, NetworkID, CallDate, StartTime, EndTime, CustomerNbr, CallerNotes, OperatorNotes, and CallType.

What I am attempting with a query -
Column - CallType
Row - NetworkID
Data - Sum([tblCallHeader!EndTime]-[tblCallHeader!StartTime])

The problem is that they want the Data to appear as hh:mm:ss. I found a subroutine that will format it that way, but then it is not numeric and can't be added.

Any help would be really appreciated.

Thanks in advance.

 

[PHV]

Data - yourFunction(Sum([tblCallHeader!EndTime]-[tblCallHeader!StartTime]))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VicRauch]

One other thing you will need along with this from PHV is to select "Expression" in the "Total" row of the Crosstab query. You would normally select "Sum", but that is being done in the "Field" row of the query. But, "Expression" finishes this off so it works just as you want.

 

[dmorse]

Thanks bunches for all your help!