Selecting first and last date from group 1

[FatEric]

Hi everyone.

For my company I have to write a report. In that report they want the information of a individual worker. I have an link created to a Access DB. In this DB the records are ordered as follows. For each day there is:
The date; a worker number; a unit.

What I want to do is to view in which period the worker was in what unit. So the outcome must be:
"From date1 to date2: Unit ..."

Now what have I already done:
I grouped the records firstly by Worker number, and secondly by Unit. Then the records are sorted on Date.

Now I want to get the first and the last date of that group, so I can say "from ... to ... : unit ...", but I don't know how to do so.

Any advice is welcome.

Cheers, FatEric

 

[Madawc]

Use the maximum and minimum functions, which can be applied at group level, unit level in your case. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.



Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Create a formula for the Unit group header or footer:

"From "+totext(minimum({table.date},{table.unit}),"MM/dd/yyyy")+ " to "+
totext(maximum({table.date},{table.unit}),"MM/dd/yyyy")+": Unit "+
totext({table.unit},0,"")//if unit is a number

-LB

 

[FatEric]

Thanks both. The formula of lbass did it's work. But now they still aren't sorted on Date.

This is what I get:
From 05/14/2007 to 02/05/2008: AK H215
From 01/01/2007 to 05/13/2007: AK H253

Because I have firstly grouped them on Worker number and then on Unit, I can't seem to sorted them on Date.

In the Record sort order I added "Date - A", but it won't sort.

Any suggestions?

Cheers,
FatEric

 

[FatEric]

I also have another question. Is there any possibility to relative linking to the database. More persons than me have to use the report with the access file I have created. So if I send them the report and the database, must it always be at the same path, or is a relative link to the database possible?

Cheers, FatEric

 

[lbass]

On the date sort, insert a minimum on table.date at the unit level and then go to report->group sort and select the minimum of date and choose ascending.

I'm not sure about your second question, but I think as long as they know where to find the database to select it as the datasource, they should be all right. You could suggest that they save it in a certain place.

-LB

 

[FatEric]

Hi lbass,

Could you explain the date sort some more. I recently started with CR and I don't know where I have to insert the minimum on table.date (or how to create it).

Thanks, FatEric

 

[lbass]

Place the datefield in the details section and then right click on it->insert summary->minimum and choose the unit group level. Once you have done the group sort you can remove the summary from the report.

-LB

 

[FatEric]

It does not seem to work...

So this is what I did:
I took the field "PZWODTA_APOPPF.APBDAT" (this is the column with the date's in) and put it in the details section. Then I right clicked on it -> insert -> summary:
- choose field to summerize: PZWODTA_APOPPF.APBDAT
- calculate this summary: minimum
- summary location -> insert group: PZWODTA_APOPPF.APKSNR (unit group level) ascending

But that gives the same as without this sort...

I have found another error. example:
01/01/2007 - 13/04/2007: Unit 1
14/04/2007 - 18/08/2007: Unit 2
19/08/2007 - 31/12/2007: Unit 1

If I first group by Worker number, then by unit, it takes the first period and the last one together. MinDate gives 01/01/2007 and MaxDate give 31/12/2007. So I get following result:
01/01/2007 - 31/12/2007: Unit 1
14/04/2007 - 18/08/2007: Unit 2

Sorry for the long post, and thanks for any suggestions.

cheers, FatEric

 

[lbass]

You left out a step. After inserting the minimum, you have to go to report->group sort and choose minimum of the your date field ascending for the group sort.

I'm not following your last problem. Please post the exact formula you are using in the unit group footer.

-LB

 

[FatEric]

I will try the sort thing again. For the second part I will post a detailed explination later tonight or tomorrow (living in Belgium and 20:37 right now.

Thanks for all the support...

Cheers FatEric

 

[FatEric]

Hi lbass,

Because here at home I have no access to the database (stands on a server at work), I can't test the sort right away, but I will test it on Monday.

I will explain the second problem again. For each day there is a record in the database, which contains the worker number, the unit for the worker and the date. So I grouped the record, firstly on worker number, and the on unit. But when I do so, and the worker left a unit in the period, went to another unit, left this one again and comes back the the first unit, it groups the first and last unit together (because they are the same). But what I want is that I get the startdate for the first unit, then the end date of the unit, start and enddate for the next unit, and then again start en enddate for the last (same as the first) unit. If I apply the maximun and minimum on the date records as you said in your formula, I only get the real first and real last date, but not the enddate of the first period and the begindate of the last period. Hope this explains the error enough.

Hope to hear from you soon.

Cheers, FatEric

 

[lbass]

I see. I think you could do this instead. Forget about the group sort altogether. Group ONLY on employee and sort by date ascending. Then insert a detail_b section and format the section in the section expert->suppress->x+2:

not onlastrecord and
{table.unit} = next({table.unit}) and
{table.employee} = next({table.employee})

Then change your formula to be placed in the detail_b section to:

whileprintingrecords;
stringvar mindate;
stringvar maxdate;
if onfirstrecord or
{table.unit} <> previous({table.unit}) or
{table.employee} <> previous({table.employee}) then
mindate := totext({table.date},"dd/MM/yyyy");
if onlastrecord or
{table.unit} <> next({table.unit}) or
{table.employee} <> next({table.employee}) then
maxdate := totext({table.date},"dd/MM/yyyy");
"From "+mindate+ " to "+
maxdate+": "+ {table.unit}

In the employee group header add a reset formula:
whileprintingrecords;
stringvar mindate;
stringvar maxdate;
if not inrepeatedgroupheader then (
mindate := "";
maxdate := ""
);

-LB

 

[FatEric]

Thanks lbass,

It works. I have a last question for you. The database runs to some day in the future. On the last date I need "..." instead of the real date, so I changed the formula in the detail_b section, but it seemed that I did something wrong. Could you help me one last time.

Formula:
WHILEPRINTINGRECORDS;
STRINGVAR mindate;
STRINGVAR maxdate;
IF ONFIRSTRECORD OR {table.unit} <> PREVIOUS({table.unit}) OR
{table.employee} <> PREVIOUS({table.employee})
THEN mindate := TOTEXT({table.date},"dd/MM/yyyy");
IF {table.unit} <> NEXT({table.unit}) OR
{table.employee} <> NEXT({table.employee}) THEN
maxdate := TOTEXT({table.date} ,"dd/MM/yyyy");
IF ONLASTRECORD THEN (maxdate := ".....");
"From " + mindate + " to " + maxdate + ": " + {table.unit}

Thanks very much!

FatEric

 

[lbass]

Try changing the order. In your formula, because you hit the clause {table.unit} <> NEXT({table.unit}) first, the formula stopped evaluating, because there was no 'next':

WHILEPRINTINGRECORDS;
STRINGVAR mindate;
STRINGVAR maxdate;
IF ONFIRSTRECORD OR {table.unit} <> PREVIOUS({table.unit}) OR
{table.employee} <> PREVIOUS({table.employee})
THEN mindate := TOTEXT({table.date},"dd/MM/yyyy");
IF ONLASTRECORD THEN
maxdate := ".....";
IF {table.unit} <> NEXT({table.unit}) OR
{table.employee} <> NEXT({table.employee}) THEN
maxdate := TOTEXT({table.date} ,"dd/MM/yyyy");
"From " + mindate + " to " + maxdate + ": " + {table.unit}

-LB

 

[FatEric]

Hi lbass,

The formula still does not show the last line. In the preview screen I see an empty Detail_B section. The other sections have the from to dates in it, but the last one does not. To test I changed the "onlastrecord if" to maxdate := TOTEXT({PZWODTA_APOPPF.APBDAT}, "dd/MM/yyyy"); but that also does not seem to print the date (what it did when the two If's where put in one.

Any idea?

Thanks very much,
FatEric

 

[lbass]

Actually, the 'next' still tries to evaluate and fails, so use this:

WHILEPRINTINGRECORDS;
STRINGVAR mindate;
STRINGVAR maxdate;
IF ONFIRSTRECORD OR {table.unit} <> PREVIOUS({table.unit}) OR
{table.employee} <> PREVIOUS({table.employee})
THEN mindate := TOTEXT({table.date},"dd/MM/yyyy");
IF ONLASTRECORD THEN
maxdate := ".....";
[red]IF not onlastrecord and
([/red]
{table.unit} <> NEXT({table.unit}) OR
{table.employee} <> NEXT({table.employee})
[red])[/red] THEN
maxdate := TOTEXT({table.date} ,"dd/MM/yyyy");
"From " + mindate + " to " + maxdate + ": " + {table.unit}

-LB

 

[FatEric]

Thanks lbass,

That did the trick. Thanks you so much for all your help. It works really fine now.

Keep up the hard work, you're doing a great job.

Thanks again,

FatEric