Date (Time) Count query

[MKH2001]

I have created a query that gives me a count of all records by a particular person per day and ordered it by date by formatting a year month and day field and ordering by that progressively.

SELECT tbl_AnnualServInsp.Date, tbl_AnnualServInsp.DBName, Format(Tbl_AnnualServInsp!Date,"dd/mm/yyyy") AS FormattedDate, Format(Tbl_AnnualServInsp!Date,"yyyy") AS GroupDate, Format(Tbl_AnnualServInsp!Date,"mm") AS FormattedMonth, Format(Tbl_AnnualServInsp!Date,"dd") AS FormattedDay, Format(tbl_AnnualServInsp!Date,"hh:nn:ss") AS AVTime, Format(tbl_NDTInspection!Date,"hh:nn:ss") AS NDTTime, Format(tbl_BCCInspection!Date,"hh:nn:ss") AS BCCTime, tbl_AnnualServInsp.OBJECTID, tbl_NDTInspection.OBJECTID, tbl_BCCInspection.OBJECTID INTO tbl_StreetlightInspectionFigures_stp1 IN 'W:\WorkOrd\Other Apps\SL_DB\StreetLightingDatabase-v2003-1_be.mdb'
FROM (tbl_AnnualServInsp LEFT JOIN tbl_BCCInspection ON tbl_AnnualServInsp.OBJECTID = tbl_BCCInspection.OBJECTID) INNER JOIN tbl_NDTInspection ON tbl_AnnualServInsp.OBJECTID = tbl_NDTInspection.OBJECTID
GROUP BY tbl_AnnualServInsp.Date, tbl_AnnualServInsp.DBName, Format(Tbl_AnnualServInsp!Date,"dd/mm/yyyy"), Format(Tbl_AnnualServInsp!Date,"yyyy"), Format(Tbl_AnnualServInsp!Date,"mm"), Format(Tbl_AnnualServInsp!Date,"dd"), Format(tbl_AnnualServInsp!Date,"hh:nn:ss"), Format(tbl_NDTInspection!Date,"hh:nn:ss"), Format(tbl_BCCInspection!Date,"hh:nn:ss"), tbl_AnnualServInsp.OBJECTID, tbl_NDTInspection.OBJECTID, tbl_BCCInspection.OBJECTID
HAVING (((tbl_AnnualServInsp.Date) Between [Forms]![frm_DailyFiguresbyInspector]![datefrom] And [Forms]![frm_DailyFiguresbyInspector]![dateto] & " 23:59:59"))
ORDER BY tbl_AnnualServInsp.DBName, Format(Tbl_AnnualServInsp!Date,"yyyy"), Format(Tbl_AnnualServInsp!Date,"mm"), Format(Tbl_AnnualServInsp!Date,"dd");

However I need the report to go one step further and count what how many records were created in the morning and how many in the afternoon etc.

I created a formatted time field and thought I could run 2 expressions against that (one expression to return a count of the Morning and the other to return the count of the Afternoon).

I am however failing miserably.

Any ideas?

 

[Golom]

Maybe something like

SELECT AI.Date
     , AI.DBName
     , Format(AI.Date,"dd/mm/yyyy") AS FormattedDate
     , Format(AI.Date,"yyyy") AS GroupDate
     , Format(AI.Date,"mm") AS FormattedMonth
     , Format(AI.Date,"dd") AS FormattedDay
     , Format(AI.Date,"hh:nn:ss") AS AVTime
     , Format(ND.Date,"hh:nn:ss") AS NDTTime
     , Format(BC.Date,"hh:nn:ss") AS BCCTime
     , SUM(IIF(Hour(AI.Date)<12,1,0))) As [MorningCount]
     , SUM(IIF(Hour(AI.Date)<12,0,1))) As [AfternoonCount]
     , AI.OBJECTID, ND.OBJECTID, BC.OBJECTID 

INTO tbl_StreetlightInspectionFigures_stp1 IN 'W:\WorkOrd\Other Apps\SL_DB\StreetLightingDatabase-v2003-1_be.mdb'

FROM (tbl_AnnualServInsp As AI LEFT JOIN tbl_BCCInspection As BC ON AI.OBJECTID = BC.OBJECTID) INNER JOIN tbl_NDTInspection As ND ON AI.OBJECTID = ND.OBJECTID

WHERE (((AI.Date) Between [Forms]![frm_DailyFiguresbyInspector]![datefrom] And [Forms]![frm_DailyFiguresbyInspector]![dateto] & " 23:59:59"))

GROUP BY AI.Date, AI.DBName, Format(AI.Date,"dd/mm/yyyy"), Format(AI.Date,"yyyy"), Format(AI.Date,"mm"), Format(AI.Date,"dd"), Format(AI.Date,"hh:nn:ss"), Format(ND.Date,"hh:nn:ss"), Format(BC.Date,"hh:nn:ss"), AI.OBJECTID, ND.OBJECTID, BC.OBJECTID

ORDER BY AI.DBName, AI.Date