Report in 1/2 hour intervals instead of 1 hour?

[ollyb303]

Hello,

Need some help with an Access 2000 query.

My query is based on a table which is a log of calls to my company.
I am using a date range entered by the user
(Date Between [StartDate] And [EndDate])
and CountOfRefNumber (RefNumber is the PK).
Currently I am using this:
LogTimes: Format(TimeSerial(Hour([Time]),0,0),"Short Time") & "-" & Format(TimeSerial(Hour([Time]),59,0),"Short Time")
to break the number of calls down by hour, so my query looks like this:

Date LogTimes CountOfRef Number
14/01/2008 08:00-08:59 1
14/01/2008 09:00-09:59 3
14/01/2008 10:00-10:59 4
14/01/2008 11:00-11:59 1
14/01/2008 13:00-13:59 5
14/01/2008 14:00-14:59 1
15/01/2008 08:00-08:59 2
15/01/2008 11:00-11:59 1
15/01/2008 14:00-14:59 1
16/01/2008 09:00-09:59 1
17/01/2008 10:00-10:59 1

This is all well and good, but now I need to break it down by 1/2 hour intervals (e.g. 08:00-08:29, 08:30-08:59, 09:00-09:30, etc.) and I don't know how.
Any help gratefully received.

Many thanks,

Olly

[ollyb303]

Really need some help with this as I have a deadline...

I would now also like to include those time intervals for which there are no calls logged, so it would look this this:

25/01/2008
TIME LOGGED
00:00 0
00:30 0
01:00 0
01:30 1
02:00 0
02:30 0
03:00 0
03:30 0
04:00 0
04:30 0
05:00 1
05:30 1
06:00 0
06:30 0
07:00 0
07:30 0
08:00 0
08:30 0
09:00 1
09:30 1
10:00 5
10:30 0
11:00 1
11:30 1
12:00 0
12:30 0
13:00 0
13:30 3
14:00 1
14:30 1
15:00 1
15:30 5
16:00 1
16:30 6
17:00 2
17:30 1
18:00 2
18:30 0
19:00 1
19:30 5
20:00 1
20:30 1
21:00 0
21:30 1
22:00 2
22:30 0
23:00 0
23:30 0

Please help.

[Stewart Ross]

Create a table to hold all the time boundaries you require, minimum two columns. Populate the table with all of the times you wish to report. As a quick test I created a table called Times with From and To columns, both type date-time, formatted as short time, and tried a few test values:

From To
08:00 08:29
08:30 08:59
09:00 09:29
09:30 09:59

Add this table to your base query (the one with the log of calls before you do the count) without joining it on any field. Add the From and To columns to your base query, then add 'between [from] and [to]' as the criteria in the log time field of your original query. In the test dataset I used you can see the result below:

Year Ref Val Time From To
2008 1 First 09:28:00 09:00 09:29
2008 2 Second 09:40:00 09:30 09:59
2008 4 Fourth 08:56:00 08:30 08:59
2008 5 Fifth 08:35:00 08:30 08:59

SELECT Test.Year, Test.Ref, Test.Val, Test.Time, Times.From, Times.To
FROM Test, Times
WHERE (((Test.Time) Between [From] And [To]));

You can now use this to compute counts for each time period. In my test version this gives

From N
08:30 2
09:00 1
09:30 1

SELECT [Test 1].From, Count([Test 1].From) AS N
FROM [Test 1]
GROUP BY [Test 1].From;

As you want to report all time periods, a final query joins the counts query back to the times, this time using a left join from the times table to the counts query to include all the time values but only the counts where these are present. This will give nulls for no counts in the time period, so for simplicity I use an inline IIF to test for null and return a 0 instead:

From List Val
08:00 0
08:30 2
09:00 1
09:30 1

SELECT Times.From, IIf(IsNull([N]),0,[N]) AS[List Val]
FROM Times LEFT JOIN [Test 2] ON Times.From = [Test 2].From;

It takes longer to describe than to do, and has the advantage that if you need to change the time intervals at some time in the future you just change them in the Times table and all dependent queries and reports follow suit.

Hope this helps

Regards

Stewart

[Stewart Ross]

Forgot to mention - for the final join you will need to include the date along with the times (and not just the times on their own) otherwise there will be N rows for each time period instead of 1, where N is the number of dates in your report - the cartesian product of the two tables. If you can provide a copy of the count query you use I can use this to query the dates then join this to the times query to provide a date-time query that fits the need for 1 row per date and time period.

Cheers

Stewart

[ollyb303]

Forgot to mention - for the final join you will need to include the date along with the times (and not just the times on their own) otherwise there will be N rows for each time period instead of 1, where N is the number of dates in your report - the cartesian product of the two tables. If you can provide a copy of the count query you use I can use this to query the dates then join this to the times query to provide a date-time query that fits the need for 1 row per date and time period.

Cheers

Stewart

Thanks Stewart,

I managed to get as far as the final join and am getting very confused about the dates. Without the date included I end up with this:
From LogCount
00:00 0
00:30 0
01:00 0
01:30 0
02:00 0
02:30 0
03:00 0
03:30 0
04:00 0
04:30 0
05:00 0
05:30 0
06:00 0
06:30 0
07:00 0
07:30 0
08:00 0
08:30 1
09:00 4
09:30 6
10:00 1
10:30 7
11:00 2
11:30 6
12:00 5
12:30 3
13:00 1
13:30 4
14:00 1
14:30 2
15:00 4
15:30 2
16:00 3
16:30 5
17:00 0
17:30 0
18:00 0
18:30 0
19:00 0
19:30 0
20:00 0
20:30 0
21:00 0
21:30 0
22:00 0
22:30 0
23:00 0
23:30 0

As you'd expect. This covers all records stored in the database since its beginning. But that's not what I need (as I think you realise).

My original table is called LogMain, I have created the Times table as you suggest and I now have a sequence of queries as follows:

BaseLogTimes:

SELECT LogMain.Date, LogMain.Time, LogMain.[Ref Number], Times.From, Times.To

LogTimes1:

SELECT BaseLogTimes.Date, BaseLogTimes.Time, Count(BaseLogTimes.[Ref Number]) AS [CountOfRef Number], BaseLogTimes.From, BaseLogTimes.To
FROM BaseLogTimes
WHERE (((BaseLogTimes.Time) Between [From] And [To]))
GROUP BY BaseLogTimes.Date, BaseLogTimes.Time, BaseLogTimes.From, BaseLogTimes.To;

LogTimes2:

SELECT LogTimes1.From, Count(LogTimes1.From) AS N
FROM LogTimes1
GROUP BY LogTimes1.From;

LogTimes3:

SELECT Times.From, IIf(IsNull([N]),0,[N]) AS [LogCount]
FROM Times LEFT JOIN LogTimes2 ON Times.From = LogTimes2.From;

Getting a bit lost here. What I need to do is run the report ideally on a week long period at a time, listing each date separately, or at the very least to be able to run it on just one day.

Feels like we're getting there though!

Many thanks for your help so far,

Olly

[Stewart Ross]

Hi Olly. Nearly there! We add a query to select the unique dates from your logmain table, join it to the times to create a timelist for each date, then use this to join back to the logmain count query.

Renaming my test table as Logmain and adding some more data for test:

Date Ref No Time Val
12-Feb-08 1 09:28:00 First
12-Feb-08 2 09:40:00 Second
12-Feb-08 3 10:02:00 Third
12-Feb-08 4 08:56:00 Fourth
12-Feb-08 5 08:35:00 Fifth
13-Feb-08 6 14:40:00 a
13-Feb-08 7 14:50:00 b
13-Feb-08 8 15:50:00 c
13-Feb-08 9 08:45:00 d
13-Feb-08 10 09:30:00 e

To select unique dates the query is just

SELECT Logmain.Date, Count(Logmain.[Ref No]) AS N
FROM Logmain
GROUP BY Logmain.Date
ORDER BY Logmain.Date;

which I saved as Date Entries.

When run, this gives

Date N
12-Feb-08 5
13-Feb-08 5

N is just the count of the entries for that date, which I added for test purposes.

Create another query to join this to the times. I called it Date Times in my test version:

SELECT [Date Entries].Date, Times.From, Times.To
FROM Times, [Date Entries]
ORDER BY [Date Entries].Date, Times.From;

This gives all times for the logged dates:

Date From To
12-Feb-08 00:00 00:29
12-Feb-08 00:30 00:59
12-Feb-08 01:00 01:29
12-Feb-08 01:30 01:59
[remaining values for 12-Feb omitted for brevity]
13-Feb-08 00:00 00:29
13-Feb-08 00:30 00:59
13-Feb-08 01:00 01:29
13-Feb-08 01:30 01:59
13-Feb-08 02:00 02:29
13-Feb-08 02:30 02:59
etc

Finally, left join this on to the log count (in place of the Times table used before), joining the Date as well as the Time on left-join:

SELECT [Date Times].Date, [Date Times].From, IIf(IsNull([N]),0,[N]) AS Logs
FROM [Date Times] LEFT JOIN [Test 2] ON ([Date Times].Date = [Test 2].Date) AND ([Date Times].From = [Test 2].From)
ORDER BY [Date Times].Date, [Date Times].From;

This produces an ordered list by date, which you can then filter for daily, weekly, monthly reporting.

Final list for the queries on the data as shown is listed at the end of the reply. Hope this helps!

If I knew how to attach the test file to this reply I would send it to you and you could add to it/change it as necessary.

If I can assist further please let me know.

Cheers

Stewart

Date From Logs
12-Feb-08 00:00 0
12-Feb-08 00:30 0
12-Feb-08 01:00 0
12-Feb-08 01:30 0
12-Feb-08 02:00 0
12-Feb-08 02:30 0
12-Feb-08 03:00 0
12-Feb-08 03:30 0
12-Feb-08 04:00 0
12-Feb-08 04:30 0
12-Feb-08 05:00 0
12-Feb-08 05:30 0
12-Feb-08 06:00 0
12-Feb-08 06:30 0
12-Feb-08 07:00 0
12-Feb-08 07:30 0
12-Feb-08 08:00 0
12-Feb-08 08:30 2
12-Feb-08 09:00 1
12-Feb-08 09:30 1
12-Feb-08 10:00 1
12-Feb-08 10:30 0
12-Feb-08 11:00 0
12-Feb-08 11:30 0
12-Feb-08 12:00 0
12-Feb-08 12:30 0
12-Feb-08 13:00 0
12-Feb-08 13:30 0
12-Feb-08 14:00 0
12-Feb-08 14:30 0
12-Feb-08 15:00 0
12-Feb-08 15:30 0
12-Feb-08 16:00 0
12-Feb-08 16:30 0
12-Feb-08 17:00 0
12-Feb-08 17:30 0
12-Feb-08 18:00 0
12-Feb-08 18:30 0
12-Feb-08 19:00 0
12-Feb-08 19:30 0
12-Feb-08 20:00 0
12-Feb-08 20:30 0
12-Feb-08 21:00 0
12-Feb-08 21:30 0
12-Feb-08 22:00 0
12-Feb-08 22:30 0
12-Feb-08 23:00 0
12-Feb-08 23:30 0
13-Feb-08 00:00 0
13-Feb-08 00:30 0
13-Feb-08 01:00 0
13-Feb-08 01:30 0
13-Feb-08 02:00 0
13-Feb-08 02:30 0
13-Feb-08 03:00 0
13-Feb-08 03:30 0
13-Feb-08 04:00 0
13-Feb-08 04:30 0
13-Feb-08 05:00 0
13-Feb-08 05:30 0
13-Feb-08 06:00 0
13-Feb-08 06:30 0
13-Feb-08 07:00 0
13-Feb-08 07:30 0
13-Feb-08 08:00 0
13-Feb-08 08:30 1
13-Feb-08 09:00 0
13-Feb-08 09:30 1
13-Feb-08 10:00 0
13-Feb-08 10:30 0
13-Feb-08 11:00 0
13-Feb-08 11:30 0
13-Feb-08 12:00 0
13-Feb-08 12:30 0
13-Feb-08 13:00 0
13-Feb-08 13:30 0
13-Feb-08 14:00 0
13-Feb-08 14:30 2
13-Feb-08 15:00 0
13-Feb-08 15:30 1
13-Feb-08 16:00 0
13-Feb-08 16:30 0
13-Feb-08 17:00 0
13-Feb-08 17:30 0
13-Feb-08 18:00 0
13-Feb-08 18:30 0
13-Feb-08 19:00 0
13-Feb-08 19:30 0
13-Feb-08 20:00 0
13-Feb-08 20:30 0
13-Feb-08 21:00 0
13-Feb-08 21:30 0
13-Feb-08 22:00 0
13-Feb-08 22:30 0
13-Feb-08 23:00 0
13-Feb-08 23:30 0

[mshmyob]

After creating your message - Choose edit at the bottom and then in edit mode you can attach a file.

If I knew how to attach the test file to this reply I would send it to you and you could add to it/change it as necessary.

[ollyb303]

Thanks so much Stewart - this is just the badger!

I was really struggling to get my head round this and your guidance has been absolutely invaluable.

Thanks again.

Olly

[ollyb303]

In fact, there is one last thing... overlooked this initially.

I have another table (same db) for logging calls transferred to our department in error. These should ideally be included in the report.

Now I have created 2 sets of queries as Stewart suggests, which give me exactly the data I need (calls logged, broken down by half-hour for each date) and (calls transferred in error, broken down by half-hour for each date).

Ideally I'd like to combine these two queries. My problem is that there are not calls logged on every date, nor are there calls transferred in error on every date - some days there is one and not the other.

The options I've tried to combine them result in dates being missed out because of the join.

I can run the 2 separately and combine the data in a spreadsheet later (I have to add some data from a 3rd source anyway!) but It would be one less job if I could get this right.

Any ideas?

[Stewart Ross]

In fact, there is one last thing... overlooked this initially.

I have another table (same db) for logging calls transferred to our department in error. These should ideally be included in the report.

Now I have created 2 sets of queries as Stewart suggests, which give me exactly the data I need (calls logged, broken down by half-hour for each date) and (calls transferred in error, broken down by half-hour for each date).

Ideally I'd like to combine these two queries. My problem is that there are not calls logged on every date, nor are there calls transferred in error on every date - some days there is one and not the other.

The options I've tried to combine them result in dates being missed out because of the join.

I can run the 2 separately and combine the data in a spreadsheet later (I have to add some data from a 3rd source anyway!) but It would be one less job if I could get this right.

Any ideas?

Hi Ollie. This one is more difficult, as SQL and ORing two tables do not really go together. There are potential solutions involving inner joining a master table of dates to the two sets of overall dates (the logged calls dates and the failed calls dates), but even if you select the relevant dates by ORing failed and successful call dates you still have to combine the two log tables. Although it is possible it is getting very, very complex.

I wonder if you have considered the much-simpler option of having a call status fieId in the log table? You could then differentiate between successful and unsuccesful calls without changing the log table in any other way. This would solve the problem by doing away with the two separate tables - you'd just report the status of the calls along with the date and time period, and can then report separately if necessary on the logs for the failed or successful calls.

Wherever possible I use queries on underlying tables to create views of the minimised set of underlying data which satisfy requirements like 'show all successful calls', 'show all unsuccessful calls', 'total all calls received on X date, and show the proportion successfully answered'. Optimal design is the key, I am sure.

Cheers

Stewart

[ollyb303]

Hi again Stewart,

I did think that changing the way the calls are logged in the first place would make things much easier, but unfortunately I don't have that option at present (maybe in a future redesign).

I think for now I will live with reporting on the two types of calls separately and combining the data in a spreadsheet. It's a few seconds extra work each week rather than what's turning out to be several hours of head scratching right now!

Thanks again for all your help with this - I would have been working on this an awful lot longer without your assistance.

Olly

edit: No longer a Newbie - hooray!