If you want to report every hour and date, regardless of whether there are
records or not, the data has to come from somewhere, so you will need to
create a pair of tables that supply all the work dates, and and all the work
hours. By combining these 2 tables, you get every work hour of every work
date. You can then outer-join that to your sales query, and get a record for
every hour.
Steps:
1. Create a new table with one field named HourID, type Number.
Mark it as primary key.
Save the table with the name tblHour.
Enter records for the hours you want, i.e. 9, 10, 11, 12, 13, ... 23.
2. Create a table with one field named TheDate, type Date/Time.
Mark it as primary key.
Save the table with the name tblDate.
Enter records for all the dates you want to report.
(Use the code below to populate the table if you wish.)
3. Create a query using tblDate and tblHour.
Add TheDate to the grid.
Add HourID to the grid.
Note that there must be no line joining the 2 tables in the upper pane of
query design.
Save this query with the name qryDateHour.
(This generates every possible combination, i.e. each hour for each date,
known as a Cartesian Product.)
4. Sounds like you already have a query that groups and gives the count, but
it needs to be set up with fields like this:
- TheDate:DateValue([CallDateTime])
- TheHour: DatePart("h", [CallDateTime])
Depress the Total button on the toolbar.
Access adds a Total row to the grid.
Accept Group By under both fields.
Add your fields for Contacts and Sales, choosing Count in the Total row
under these fields.
Save this query with the name qrySalesByDateHour.
5. Create another query using qryDateHour and qrySalesByDateHour as source
"tables." Drag TheDate from qryDateHour, and drop onto TheDate in
qrySalesByDateHour.
Access shows line joining the 2 tables.
Double-click this join line.
Access pops up a dialog offering 3 choices.
Choose the one that says:
All records from qryDateHour, and any matches from qrySalesByDateHour.
(This is known as an outer join.)
6. Repeat step 5 to outer join qryDateHour!TheHour to
qrySalesByDateHour!TheHour.
If you have done this right, you have 2 lines joining the tables, both with
the arrowhead pointing the same way.
Here's the code to populate the table of dates if you want to use it:
Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

Signature
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
> Good Morning,
>
[quoted text clipped - 29 lines]
>
> Can you help me....
Access To Access - 15 Apr 2007 12:46 GMT
It worked perfectly, just can't believe i missed something this simple. Thank
you...
> If you want to report every hour and date, regardless of whether there are
> records or not, the data has to come from somewhere, so you will need to
[quoted text clipped - 102 lines]
> >
> > Can you help me....