
'***********************************************************************
'** Description : UNION ALL with Index
'** Author      : Dhumil Sheth & Yuji Nishimura
'** Date        : December 4th, 1997
'***********************************************************************

Option Compare Database
Option Explicit

    Function unialwi()
    
    
    Dim PauseTime, Start, Finish, TotalTime
    PauseTime = 5   ' Set duration.
    Start = Timer   ' Set start time.

    
    Dim execacc As String
    
    Dim dbs As Database, rst As Recordset
    Set dbs = CurrentDb()
    dbs.execute "Drop TABLE emp10RES; "
    dbs.execute "CREATE TABLE emp10RES " _
        & "(EID INTEGER, " _
        & "StartDate Date, " _
        & "EndDate   DATE); "
        
    dbs.execute "DROP INDEX NewIndex ON emp20000; "
    dbs.execute "CREATE INDEX NewIndex ON emp20000 " _
        & "(EID, StartDate, EndDate);"

    
    execacc = " INSERT INTO emp10RES(EID, StartDate, EndDate) " _
            & "  SELECT E1.EID, E1.StartDate, E1.EndDate" _
            & "  FROM emp20000 AS E1, emp20000 AS E2" _
            & "  WHERE  E1.MID = E2.EID AND" _
            & "  E1.Salary > E2.Salary AND" _
            & "  E2.StartDate <= E1.StartDate AND " _
            & "  E1.EndDate <= E2.EndDate;"
    dbs.execute execacc
    
    execacc = " INSERT INTO emp10RES (EID, StartDate, EndDate) " _
            & " SELECT E1.EID, E1.StartDate, E2.EndDate" _
            & " FROM   emp20000 AS E1, emp20000 AS E2 " _
            & " WHERE  E1.MID = E2.EID AND " _
            & " E1.Salary > E2.Salary AND" _
            & " E1.StartDate > E2.StartDate AND " _
            & " E2.EndDate < E1.EndDate AND " _
            & " E1.StartDate < E2.EndDate;"
    dbs.execute execacc
    
    execacc = " INSERT INTO emp10RES(EID, StartDate, EndDate) " _
            & " SELECT E1.EID, E2.StartDate, E1.EndDate" _
            & "    FROM   emp20000 AS E1, emp20000 AS E2" _
            & " WHERE  E1.MID = E2.EID AND " _
            & " E1.Salary > E2.Salary AND " _
            & " E2.StartDate > E1.StartDate AND " _
            & " E1.EndDate < E2.EndDate AND " _
            & " E2.StartDate < E1.EndDate; "
    dbs.execute execacc
    
    execacc = " INSERT INTO emp10RES (EID, StartDate, EndDate) " _
            & " SELECT E1.EID, E2.StartDate, E2.EndDate" _
            & "    FROM   emp20000 AS E1, emp20000 AS E2" _
            & " WHERE  E1.MID = E2.EID AND " _
            & " E1.Salary > E2.Salary AND " _
            & " E2.StartDate >= E1.StartDate AND " _
            & " E2.EndDate <= E1.EndDate AND " _
            & " NOT (E2.Startdate = E1.StartDate AND " _
            & " E2.EndDate = E1.EndDate);"
    dbs.execute execacc

    Finish = Timer  ' Set end time.
    TotalTime = Finish - Start  ' Calculate total time.
    MsgBox "Paused for " & TotalTime & " seconds"


End Function

