CR XI - Multi line record table????

[CRXIuser2005]

I have a table that has the following data:

TABLE NAME = ASSIGNMENT

DEPT ASSIGN DATE FILE#
ABC 10/06/05 1234
DEF 10/06/05 1234
GHI 11/10/05 1234
CNC 11/11/05 1234

Each record (file) has multiple rows of data.

I would like to be able to do an AGING on the number of days spent in each department. My main objective is to get an AVERAGE NUMBER OF DAYS in each depart.

Secondarily, I would like to form a TREND analysis on what percentage of files go from DEPT TO DEPT.

As and example ALL files start in department ABC, then after review are sent out to our separate dept's for processing. I would like to create a report that tells me the number OR percentage of files going to the separate departments.

My ultimate goal is to create a TREND ANALYSIS of our FILE FLOW from dept to dept.

Your ideas are greatly appreciated.

Please let me know if you need addition info.

Thank you.

MV

"A man is only as happy, as he makes up his mind to be....

 

[synapsevampire]

What software are you using?

An example of your intended output would be useful.

You can insert a Cross-Tab into the report header or report footer, with the DEPT as the row, and the distinct count of File as the summary.

This will show the quantity in each DEPT.

You can also make the results a percentage of the total, or do both by adding it a second time and making one just the count, the other the percentage of the total.

-k

 

[CRXIuser2005]

I'm thinking I'll need to do a Command Prompt at DB level in order to track the change of departments by dates? I need to AGE the amount of time that a file is in any given dept. Within he same dept, it can appear numerous times...with differen dates...but I would need to age only on the FIRST date that it appeared within that dept.

thx

Thank you.

MV

"A man is only as happy, as he makes up his mind to be....

 

[CRXIuser2005]

The final report should look like this? Or give me the data so that I can obtain the data.

DEPT ASSIGN DATE FILE#
ABC 10/06/05 1234
DEF 10/06/05 1234
GHI 11/10/05 1234
CNC 11/11/05 1234

FROM-TO DAYS
ABC-DEF <1
DEF-GHI 36
GHI-CNC 1

I need to do something like this..and expand on this, for analysis.



Thank you.

MV

"A man is only as happy, as he makes up his mind to be....

 

[CRXIuser2005]

OR...

DEPT ASSIGN DATE FILE# DAYS
ABC 10/06/05 1234 0
DEF 10/06/05 1234 1
GHI 11/10/05 1234 36
CNC 11/11/05 1234 1






Thank you.

MV

"A man is only as happy, as he makes up his mind to be....