Left Join Query

[Kelvin]

Hi...

tblCallVolsByHalfHour:-
Date <--Short Date Format
Time <--Short Time Format eg. 00:00, 00:30 etc../
Dialled Number <--Telephone number
Call Volumes <--on dialled number in the said half hour slot.

tblTimeInterval:-
Interval <--Eg. 00:00, 00:30, 01:00 etc... (48 rows in total)

tblCallVolsByHalfHour only has rows for dialled numbers where call volumes
exist, and each row is a half hour interval in a day.

I want to query tblCallVolsByHalfHour by dialled number and return calls
volumes by half hour interval but show the half hours which don't have any
volumes as blank.

I created tblTimeInterval so I could create a Left Join onto
tblCallVolsByHalfHour to accomplish this but it only works if I only select
the [tblCallVolsByHalfHour].[Time] field and [tblTimeInterval].[Interval]
field with an 'Is Null' under tblCallVolsByHalfHour.Time.

What am I missing/doing wrong. TIA.

Kelvin

 

[Allen Browne]

Kelvin, trying to join tables on floating point values such as date/time
will not give reliable results.

You could create the tblTimeInterval with an Integer field for the 48 day
segments, i.e. values 0, 1, 2, ... 48.

Then outer join that to:
(tblCallVolsByHalfHour.Time \ 48)
using integer division to divide the time into 48 timeslots.

You would need to use SQL View to type in this Join statement.

(You probably already know that Date and Time are not good field names.)