Help with grouping on report.

[bpenney]

Hi all,

I'm having a little difficulty in getting the information in my report the way I want.

I'm using CR 8.5 on a MS Jet database.

I have two tables I'm using Employee and Timedata. The employee table contains the employees name and the timedata contains the hours charged to each code, the two tables are linked by the primary key.

The information I want is the hours charged to each code per day, the only problem I have is when there is vacation time on the same day. I have everything in the details section, there are no formulas in the report except for the parameter field I use for the date range.

The fields in the report are {Employee.fullname},{timedata.date}, {timedata.reg}, {timedata.OT}, {timedata.DT} and {timedata.vac}

The output of my report looks like this.

Smith, Lana M 02/25/2004 REG 0.00
OVT 0.00
DBL 0.00
VAC 4.00

Smith, Lana M REG 4.00
OVT 1.37
DBL 0.00
VAC 0.00

As you can see it is giving me a duplicate record when there is vacation time. What I want is a single record that includes all of the hours charged to the day, like this.

Smith, Lana M 02/25/200 REG 4.00
OVT 1.37
DBL 0.00
VAC 4.00

I have tried grouping in various ways but still no joy, so any help you could give would be greatly appreciated.

Once I do get the info grouped the way I want I then want to only show those records that have both regular hours and vacation hours on the same day.

TIA

 

[lbass]

What fields are linked--Employee ID? What groups have you inserted? There is nothing in what you have told us that would predict a duplication of fields. Are all of the fields you identified in the report actual database fields or are they formulas? If formulas, you need to share them.

-LB

 

[synapsevampire]

From your example the OT daat doen't have a date, so how could you know which date it applies to?

Insert a group by the {Employee.fullname} (Insert->Group) and one for {timedata.reg}, and then Right click each of the fields to sum and select Insert->Summary->Sum

This will create sums in the group footer.

Now move the other fields out of the details and right click the details and select suppress.

-k

 

[bpenney]

The fields are linked by the employee ID.

All of the fields in the report are actual database fields, except for the date field which is the only formula in the report and it is used to convert the Julian date, as seen below.

{timedata.date}+date(1800,12,28)

I had tried some grouping but it didn't work, so I removed them, there are no groupings in the report as it stands.

I've included a little larger view of the data returned. It is a very simple report really, just the name, date and hours fields. I just can't figure out why it produces a second record when vacation hours exist. All of the fields are dropped in the details section.

McInroy, Alana M 02/20/2004 REG 8.00
OVT 0.00
DBL 0.00
VAC 0.00

McInroy, Alana M 02/21/2004 REG 0.00
OVT 0.00
DBL 0.00
VAC 4.00

McInroy, Alana M REG 4.00
OVT 0.00
DBL 0.00
VAC 0.00

McInroy, Alana M 02/24/2004 REG 8.00
OVT 0.00
DBL 0.00
VAC 0.00

McInroy, Alana M 02/25/2004 REG 0.00
OVT 0.00
DBL 0.00
VAC 4.00

McInroy, Alana M REG 4.00
OVT 0.00
DBL 0.00
VAC 0.00

McInroy, Alana M 02/26/2004 REG 8.00
OVT 0.00
DBL 0.00
VAC 0.00


This one's got me stumped, I may have to go about it another way, thanks for trying to help me out.

 

[lbass]

I think you could take SV's advice, and insert a group on employee, but then I think you should group on {timedata.date} (he probably meant this). Then, again as SV said, insert sums on the four fields and arrange them in the group footer, and suppress the details.

-LB