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

Option Compare Database
Option Explicit


Function unionwi()
    
    Dim PauseTime, Start, Finish, TotalTime
    PauseTime = 5   ' Set duration.
    Start = Timer   ' Set start time.
    
    Dim execacc As String, execacc1 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 TABLE emp11RES; "
    dbs.execute "CREATE TABLE emp11RES " _
        & "(EID INTEGER, " _
        & "StartDate Date, " _
        & "EndDate   DATE); "
    
    dbs.execute "DROP INDEX NewIndex ON emp10;"
    dbs.execute "CREATE INDEX NewIndex ON emp10 " _
        & "(EID, StartDate, EndDate);"
        
    execacc = " INSERT INTO emp10RES(EID, StartDate, EndDate) " _
            & "  SELECT E1.EID, E1.StartDate, E1.EndDate" _
            & "  FROM emp10 AS E1, emp10 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   emp10 AS E1, emp10 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   emp10 AS E1, emp10 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   emp10 AS E1, emp10 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
    
    execacc1 = " INSERT INTO emp11RES (EID, StartDate, EndDate) " _
             & " SELECT DISTINCT  " _
             & " EID, StartDate, EndDate " _
             & " FROM emp10RES; "
    dbs.execute execacc1

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

End Function




