Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / April 2007

Tip: Looking for answers? Try searching our database.

Show all rows...Please help...

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Access To Access - 15 Apr 2007 10:42 GMT
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 - 15 Apr 2007 11:20 GMT
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....
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.