Crystal Reports- Grouping different fields by identical results......HELP.....

[NNNY]

Hello all
I have a bit of an issue, and I’m wondering if anyone can be of assistance…
I am trying to group data that is recorded in different fields, but is identical in some cases…..let me explain:
In my Vehicle_Schedule table, I have a separate field for “Driver” and another for “Helper”.
When employees are reporting to work, their assignments can interchange, so one might be designated as the “Driver” one day, only to be designated as the “Helper” on the next.
I need to be able to produce various reports that will summarize the result by employee…
Currently, I am running 2 separate reports, one for “Driver” and another for “Helper”, and combining the data in Excel, which is tedious and I do believe there must be a way in Crystal to do it, whether it is a sub report or a view….
Here is a quick sample of what I’m trying to accomplish:

Date Trip ID Driver Helper Time to complete
15-Oct 100 Joe Sam 30 minutes
16-Oct 101 Eric Sam 20 minutes
17-Oct 102 Sam Joe 20 minues
18-Oct 103 Dave Sam 20 minutes
19-Oct 104 Joe Dave 10 minutes
20-Oct 105 Dave Sam 20 minutes

Based on this example, the report should be able to provide the following results:

Employee Avg time to complete
Joe (30+20+10)/3=20 (Trips 100,102 and 104)
Eric 20 Trip 101
Sam (30+20+20+20+20)/5=22 (Trips 100,101,102,103,105)
Dave (20+10+20)/3=16.66 (Trips 103,104,105)

Any help would be greatly appreciated….

 

[IdoMillet]

As the data source for your report, create a Command (in Crystal) or a VIEW (in your DBMS) that UNIONs the data like this:

SELECT Date, Trip ID, Driver as Employee, Time to complete, 'Driver' as Emp_Type FROM Vehicle_Schedule
UNION ALL
SELECT Date, Trip ID, Helper as Employee, Time to complete, 'Helper' as Emp_Type FROM Vehicle_Schedule

Cheers,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[NNNY]

Hello, and thank you for your reply

My apologies, I neglected to list the other tables that are involved in this query, as I am not sure how to link them, and whether the command should change based on that...

Here is the location of the data:

Jobs table - All job information with regards to job date, all time stamps (scheduled, en route, at location, completed etc.)
Vehicle_Schedule table- All data pertaining to the vehicle that was assigned to the job (Employee codes, vehicle number etc.)
Driver table - All information pertaining to the driver (Name, address, employee number etc.)

In order to get the information for the Helper, I simply duplicate the "Driver" table and name it "Helper", as the fields are identical.

Usually, I have the the following links:

Jobs.Veh----Vehicle_Schedule.Veh
Vehicle_Schedule.Driver----Drivers.Code
Vehicle_Schedule.Helper----Helpers.Code

I am unsure how to structure the linking or codes, being that I need to group based on the employee name or number, while the job data is coming from the jobs table....

 

[IdoMillet]

If your DBMS supports the creation of views, simply create the view as suggested above, You can then treat it as a table and join to it from other tables or views.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[NNNY]

Gotcha....Tried initially as a command and it slowed down the server in a big way.....
Finally had a chance to create the view and it worked perfectly....THANK YOU

 

[IdoMillet]

Excellent. Thanks for closing the loop.
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com