PeterDuthie
Programmer
It'll probably be more clear if I show you the query I'm using:
SELECT DecontaminationRecord.Location, Sum(DecontaminationRecord.LOShrs_mins) AS LOS_SUM, tblLocation.Loc
FROM DecontaminationRecord, tblLocation
WHERE (DecontaminationRecord.[Date] = [Forms]![frmGetDate]![DateForReport]) and (tblLocation.ID = DecontaminationRecord.Location)
GROUP BY DecontaminationRecord.Location, tblLocation.Loc;
The query is supposed to return a record set of the location of a decontamination centre, and the sum of the time spent decontaminating vehicles at the various decontamination centre. (It's to do with Foot and Mouth in the UK in case your interested)
running the query get's me something like this.
Location LOS_SUM Loc
1 1.38888888888888E-02 Yard
4 9.02777777777775E-03 RDX 3
6 5.20833333333333E-02 RDX 7
The problem is the LOS_SUM which is a a time field (it holds a time period - hence LengthOfStay = LOShrs_mns) The query gets data for a report which needs the time periods summed. I can't see how to get the query to return a time nor can I see how to convert the figure returned to a time period in the report.
Please help me again: I've already told people that this report will be easy to do so I'm going to look (deservedly?) foolish if I don't get this done today.
Cheers,
Peter
SELECT DecontaminationRecord.Location, Sum(DecontaminationRecord.LOShrs_mins) AS LOS_SUM, tblLocation.Loc
FROM DecontaminationRecord, tblLocation
WHERE (DecontaminationRecord.[Date] = [Forms]![frmGetDate]![DateForReport]) and (tblLocation.ID = DecontaminationRecord.Location)
GROUP BY DecontaminationRecord.Location, tblLocation.Loc;
The query is supposed to return a record set of the location of a decontamination centre, and the sum of the time spent decontaminating vehicles at the various decontamination centre. (It's to do with Foot and Mouth in the UK in case your interested)
running the query get's me something like this.
Location LOS_SUM Loc
1 1.38888888888888E-02 Yard
4 9.02777777777775E-03 RDX 3
6 5.20833333333333E-02 RDX 7
The problem is the LOS_SUM which is a a time field (it holds a time period - hence LengthOfStay = LOShrs_mns) The query gets data for a report which needs the time periods summed. I can't see how to get the query to return a time nor can I see how to convert the figure returned to a time period in the report.
Please help me again: I've already told people that this report will be easy to do so I'm going to look (deservedly?) foolish if I don't get this done today.
Cheers,
Peter