Query Puzzler

[Kelvin Middleton]

Hi all, please help.

I have a DB which stores call data downloaded from an ACD (Call Centre
Server recording call information), this information specifically is
recording in half hours slots and counts the number of calls that come down
a specific telephone number.

I can sucessfully manipulate this information, so for example I have a query
which gives me the total call volumes received by half hour by date for a
specific telephone number or group of telephone numbers. The issue is that
if for instance there are no calls made on a specific tel number then the
ACD doesn't report it, it just skips that half hour segment, so when my
query returns the data instead of returning 48 half hours slots some of
which may be empty it just returns the number of slots with data in. I want
to do is for the query to return all 48 half hour slots and just leave blank
the call volumes for this which don't/shouldn't have any.

I've tried creating a seperate table which simply contains 48 rows and each
holds a different half hour eg. 00:00, 00:30, 01:00 and so on. I then place
this in the query along with my table that stores the call volumes and join
the 2 time fields with a join that says to return all records from
tblAllHalfsHours and only those records which match in tblCallData. However
this doens't work, I just get the rows which have data in and not the padded
out version. Has anyone got a solution please? I'm using Access 2000.

Many thanks,

Kelvin

 

[Guest]

Use 0 (zero) for criteria for your tblCallData count field.

OR Null for the tblCallData field of the join.

 

[Kelvin Middleton]

Okay I'm not understanding your answer or you're not understanding my
problem...

tblCallData:
00:00
00:30
01:00
02:00 <--- 01:30 missing inbetween values

I want my query to add the 01:30 value in the results.

Is that what your solution solves? Or are we at cross purposes?

Cheers.

 

[Guest]

Yep.
You said that you "join the 2 time fields with a join that says to return
all records from tblAllHalfsHours and only those records which match in
tblCallData."

If you open the query in Design View the graphicial display will have a line
connecting the tblAllHalfsHours and the tblCallData. The tblCallData end
should have an arrow head.

If the the tblCallData field for time is already on the FIELD row of the
grid just add the criteria - otherwise drag the field to the grid row and add
criteria.