Show all rows...Please help...

[Guest]

Good Morning,

I have an annoying thing i am trying to sort. I am creating a report using
access to format the data. I am trying to give an hourly breakdown of
results. In my data i have a "CallDateTime" which records data in a
DD/MM/YYYY HH:MM:SS format. I have written some VBA which will now present
the data in an hourly time slot, for example 13/03/2007 16:30:44 would now
show as "16:00:00 - 16:59:59". I then have a sum and count happening in other
columns to give me the data i need. Now the problem i have is that we don't
always call people at all hours of the day, and so i am getting patches and
gaps between hours zones, which throws my reporting out. What i need is the
full hourly spectrum (that is 9:00:00 to 22:59:59 - Broken down into
individual hours segments) and if there is no activity showing then it
displays zeros.

So for example at the moment i have:
Call Hour Contacts Sales
09:00:00 - 09:59:59 16 2
16:00:00 - 16:59:59 3 0


What i need is:

Call Hour Contacts Sales
09:00:00 - 09:59:59 16 2
10:00:00 - 10:59:59 0 0
11:00:00 - 11:59:59 0 0
etc

Can you help me....

 

[Allen Browne]

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:
- TheDateateValue([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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Guest]

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
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:
- TheDateateValue([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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Good Morning,

I have an annoying thing i am trying to sort. I am creating a report using
access to format the data. I am trying to give an hourly breakdown of
results. In my data i have a "CallDateTime" which records data in a
DD/MM/YYYY HH:MM:SS format. I have written some VBA which will now present
the data in an hourly time slot, for example 13/03/2007 16:30:44 would now
show as "16:00:00 - 16:59:59". I then have a sum and count happening in
other
columns to give me the data i need. Now the problem i have is that we
don't
always call people at all hours of the day, and so i am getting patches
and
gaps between hours zones, which throws my reporting out. What i need is
the
full hourly spectrum (that is 9:00:00 to 22:59:59 - Broken down into
individual hours segments) and if there is no activity showing then it
displays zeros.

So for example at the moment i have:
Call Hour Contacts Sales
09:00:00 - 09:59:59 16 2
16:00:00 - 16:59:59 3 0


What i need is:

Call Hour Contacts Sales
09:00:00 - 09:59:59 16 2
10:00:00 - 10:59:59 0 0
11:00:00 - 11:59:59 0 0
etc

Can you help me....