Better Way than multiple Unions? 1

[scottyjohn]

Hi all,
I have put together a query which runs against a database to pull call volumes into a call centre per hour of the day. I am doing this using a union query for each hour of the day, as per the extract below.....

SELECT "07:00 - 08:00" AS PERIOD , SOFTPHONE.BRAND, Count(SOFTPHONE.BRAND) AS CALLS
FROM SOFTPHONE
WHERE (((SOFTPHONE.BRAND) Like Nz([Forms]![frmINTERVALINTRO]![cmbNEWBRAND],"*")) AND ((SOFTPHONE.STARTD) Like Nz([Forms]![frmINTERVALINTRO]![txtNEWDATE], "*")) AND ((SOFTPHONE.STARTT) LIKE "07*"))
GROUP BY SOFTPHONE.STARTD, SOFTPHONE.BRAND, SOFTPHONE.DIRECTION
HAVING (((SOFTPHONE.DIRECTION)="I"))
UNION ALL
SELECT "08:00 - 09:00" AS PERIOD , SOFTPHONE.BRAND, Count(SOFTPHONE.BRAND) AS CALLS
FROM SOFTPHONE
WHERE (((SOFTPHONE.BRAND) Like Nz([Forms]![frmINTERVALINTRO]![cmbNEWBRAND],"*")) AND ((SOFTPHONE.STARTD) Like Nz([Forms]![frmINTERVALINTRO]![txtNEWDATE], "*")) AND ((SOFTPHONE.STARTT) LIKE "08*"))
GROUP BY SOFTPHONE.STARTD, SOFTPHONE.BRAND, SOFTPHONE.DIRECTION
HAVING (((SOFTPHONE.DIRECTION)="I"))

This all works fine but the query takes about 5 minutes to run! The data is on an oracle remote instance which I connect to using ODBC. Any suggestions as to how I can speed this up?

John
ski_69@hotmail.com

 

[Crowley16]

why are you testing it by looking at the first 2 characters in the time field?

why not just do a single select with critera between 07:00 and 09:00

--------------------
Procrastinate Now!

 

[scottyjohn]

I need a seperate figure for each time period and the easiest way I could think of doing this was to test the STARTT field to select all records where the START time was within that hour i.e. "08*"

John
ski_69@hotmail.com

 

[Crowley16]

then why not select all the records normally, and then format them into time periods...

i.e. select blah, blah, time, iif(time between 7 and 8, "time1", "time2) AS TimePeriod

--------------------
Procrastinate Now!

 

[scottyjohn]

Sorry Crowley, Im not understanding you really, can you give an example using the example statement? Is this relating to improving the execution time?

John
ski_69@hotmail.com

 

[Crowley16]

use only 1 select statement...

use the criteria between 7 and 9...

add another field in your select statement which tests the time to test which time period it's in, then display the time period...

--------------------
Procrastinate Now!

 

[scottyjohn]

Sorry, Im just not getting it here, can you use the example select statements which I posted to create an example to help me understand?

John
ski_69@hotmail.com

 

[dhookom]

Try this query which just returns the left two characters of the time field. You can use an expression in your report or form to display a range:

SELECT   Left(STARTT,2) AS PERIOD , BRAND, Count(BRAND) AS CALLS
FROM SOFTPHONE
WHERE BRAND Like Nz([Forms]![frmINTERVALINTRO]![cmbNEWBRAND],"*") AND 
   STARTD = Nz([Forms]![frmINTERVALINTRO]![txtNEWDATE], STARTD) AND 
   DIRECTION="I"
GROUP BY Left(STARTT,2), STARTD, BRAND

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gol4]

And a few other options
Format([Time],"hh")
DatePart("h",[time])

 

[scottyjohn]

Wow, your the man! Does exactly what I need. I am displaying the results in a list box of a form, how do I get it to display the "PERIOD" column into somthing more meaningful such as "0700 - 0800"?

John
ski_69@hotmail.com

 

[dhookom]

Try an expression like:
Period & "00 - " & Format(Val(Period)+1,"00") & "00"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]