grouping

[mtagliaferri]

here I am again having problems grouping data....
I need to group the following qry, grouping together all data with same value in TBLTRIP with record source PSR I have the joint table TBLFLIGHT where I have add all values FLIGHTTIME which is a time format.
Can I get any help?
Thanks

SELECT tblTrip.PSR, Count(tblTrip.NDays) AS [Tot Of Trips], Sum(tblTrip.NDays) AS [Tot Of Days], Sum(tblTrip.NNightStop) AS [Night Stops], Int(Sum(tblTrip.FlyingTime)*24) & ":" & Format(Sum(tblTrip.FlyingTime),"nn") AS [Tot Flying Time], Sum(tblTrip.TAFB) AS [Tot TAFB], tblTrip.DutyPayRate, Sum([TAFB]*[DutyPayRate]) AS [Tot Duty Pay], tblFlight.FlightTime
FROM tblTrip INNER JOIN tblFlight ON tblTrip.IDTrip = tblFlight.IDTrip
GROUP BY tblTrip.PSR, tblTrip.DutyPayRate, tblFlight.FlightTime;

 

[spaddhu]

could you please give details on the structure of both the tables.

 

[spaddhu]

ie. what is actually the purpose of Duty Payrate and FlightTime??

 

[mtagliaferri]

duty pay rate i a value which the final user can change at any time and TAFB is the total of hours on the the table TBLTRIP, which will have the total pay for te month in TOTDUTYPAY, initialy the qry was built with only the TBLTRIP and worked correctly as following

SELECT tblTrip.PSR, Count(tblTrip.NDays) AS [Tot Of Trips], Sum(tblTrip.NDays) AS [Tot Of Days], Sum(tblTrip.NNightStop) AS [Night Stops], Int(Sum(tblTrip.FlyingTime)*24) & ":" & Format(Sum(tblTrip.FlyingTime),"nn") AS [Tot Flying Time], Sum(tblTrip.TAFB) AS [Tot TAFB], tblTrip.DutyPayRate, Sum([TAFB]*[DutyPayRate]) AS [Tot Duty Pay]
FROM tblTrip
GROUP BY tblTrip.PSR, tblTrip.DutyPayRate;

to have a full statistic I now need to add the total of flighing hours across the month which is contained in the table flight, the two tables are linked by the ID field where one record of TBLTRIP contains many records of TBLFLIGHT
In PIC1 attached you can see the original qry which was working with the currect results, as you can see I have an empty field TOTFLIGHING TIME which should be filled with the TOTAL of flighing hours, but the result in the qry is as SCREEN2, so I need to get the results as in SCREEN1 with the total of FLIGHT TIME as in SCREEN2.
Hope I gave all the information.
Thanks

 

[spaddhu]

Did you try giving sum(tblFlight.FlightTime) in the select clause
and removing it from the group by clause???

 

[spaddhu]

also clarify what type of column is FlightTime.
is it time datatype. ie. 1:34 denotes 1:34 am/pm
or does 1:34 signify 1 minute and 34 seconds???

 

[mtagliaferri]

I have done as you told me, and removing the group clause I get this error as in SCREEN3

 

[mtagliaferri]

1:34 is equal to 1 hour and 34 minutes as lenght of the flight not as time of the day.

 

[spaddhu]

If you note properly, in your query SELECT clause you still have tblFlight.FlightTime, before Sum(tblFlight.FlightTime). Remove that, and your error should vanish.

 

[mtagliaferri]

Thanks Spaddhu

could not see that! it now goups again by PSR, but the result is far from being a total of hour and minutes, see SCREEN4. If you look at records with PSR 2007 06 on SCREEN2 the total of the flight time should return 2 Hours and 18 minutes, other months could contain a high number of records bringing the total of hours over the 24h format a quick example is if I have 10 records with all the same value of 4hours and 0minutes the total should be 40hours 0minutes or 10 records with 4hours 30minutes the total would be 45hours 0minutes.
Hope it is clear.

SELECT tblTrip.PSR, Count(tblTrip.NDays) AS [Tot Of Trips], Sum(tblTrip.NDays) AS [Tot Of Days], Sum(tblTrip.NNightStop) AS [Night Stops], Int(Sum(tblTrip.FlyingTime)*24) & ":" & Format(Sum(tblTrip.FlyingTime),"nn") AS [Tot Flying Time], Sum(tblTrip.TAFB) AS [Tot TAFB], tblTrip.DutyPayRate, Sum([TAFB]*[DutyPayRate]) AS [Tot Duty Pay], sum(tblFlight.FlightTime) AS [TOT XXX]
FROM tblTrip INNER JOIN tblFlight ON tblTrip.IDTrip = tblFlight.IDTrip
GROUP BY tblTrip.PSR, tblTrip.DutyPayRate;

Thanks again!

 

[spaddhu]

can you tell the exact datatype of FlightTime column as in your access database??

 

[mtagliaferri]

On the table tblFlight the data type is date/time format short time imput mask 00:00;0;_

 

[spaddhu]

this is time field. i.e. your field does not indicate 1hr and 15 minutes or the like. but instead it is 1:15am. the datatype you have provided is wrong.

 

[spaddhu]

if you have used it to denote duration, then you have to do some conversions after summing up, so that it makes sense. visit the following link for further information on this conversion.

http://www.mvps.org/access/datetime/date0009.htm

 

[mtagliaferri]

Thanks, I had a look...seems quite complicated but will give a try to sort it out!

 

[spaddhu]

Sure. Don't forget to reflect your success or failure here. So that, others who view the thread are benefitted.