|
Hi,
I am using Crystal Reports 9.0
I have three sql tables. MonthlyStatus, DailyStatus, and FlightLog
In the report I am puting these three table together with a join with aircraftkey.
The DailyStatus table has data every day and the Flightlog has many transaction per day and some days no data.
So we are looking at a One to many.
Example:
Date Model Tail No Flt Hrs Total Hrs Landings Tol Landings
03/01/2005 F-35 1234 3.5 4324.5 2 3212
03/02/2005 F-35 1234 2.0 4326.5 2 3214
03/03/2005 F-35 1234 0.0 0.0 0 0
03/04/2005 F-35 1234 2.0 4328.5 2 3216
I am grouping on the aircraftkey then also grouping on Date by month then grouping by date then grouping by flightlog date then suppressing the detail.
The reason in the example the date on 03/03/2005 the flightlog did not have any transactions.
Also the flightlog table looks like this
Date Flt Hrs Tot Hrs Landings
03/01/2005 0.5 4321.5 2
03/01/2005 1.0 4324.5 3
03/01/2005 1.5 4323.5 2
03/01/2005 0.5 4322.0 1
03/02/2005 2.0 4326.5 2
03/04/2005 0.5 4327.0 1
03/04/2005 1.5 4328.5 1
In the flightlog group I group on date and sum the Flt Hrs and max the Tot Hrs and sum the Landings
Date Flt Hrs Tot Hrs Landings
03/01/2005 3.5 4324.5 8
03/02/2005 2.0 4326.5 2
03/04/2005 2.0 4328.5 2
The monthlystatus has the following:
Date Total Hrs Tot Landings
02/28/2005 4321.0 3204
03/31/2005 4410.5 3315
Would like it to be like this:
Begin Hrs 4321.0 Begin Landing 3204
Date Model Tail No Flt Hrs Total Hrs Landings Tol Landings
03/01/2005 F-35 1234 3.5 4324.5 8 3212
03/02/2005 F-35 1234 2.0 4326.5 2 3214
03/03/2005 F-35 1234 0.0 4326.5 0 3214
03/04/2005 F-35 1234 2.0 4328.5 2 3216
I would like to continue the Total Hrs and the Tol Landing when the flight log does not have a record for that date no matter if it is at the first of the month or the middle of the month
I think I need some global variables but I do need help in using them.
Please help
Thanks in advance
LarryM
|