Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2012
    Posts
    12

    Question Unanswered: need help to perform data selection !!

    i am facing difficulties in using microsoft access 2003 i need your help please,
    the case is similar to this small example:
    I have 2 tables T1, T2
    T1 contains 1 column (Col1)
    T2 contains 1 column (Col2)
    the data in T1
    158AFC
    657CSC
    698EOA
    the data in T2
    158
    657
    i wrote a select query
    select mid(col1,1,3) from T1, T2 where mid(col1,1,3) <> col2
    the right answer is 698 but i got a wrong result which is
    158
    657
    698
    698

    appreciate any help...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Her's a solution:
    Code:
    SELECT Mid(T1.Col1,1,3)
    FROM   T1
    WHERE  Mid(T1.Col1,1,3) NOT IN ( SELECT DISTINCT a.x
                                     FROM ( SELECT mid(Col1,1,3) AS x
                                            FROM T1 
                                          ) AS a
    INNER JOIN T2 ON a.x = T2.Col2 );
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Posts
    12
    Thank you very much Sinndho for your help I really appreciate it
    I tried the select you sent me and it worked successfully...
    THANK YOUUU

    Have a nice day

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    12

    enable/disable buttons

    Hi,
    I have another question if u can help me please
    I created an autoexec macro which runs automatically when opening the access file, is there any possibility to enable and disable buttons on my form in this macro

    on the design view of this macro i used

    SetValue
    [Forms]![MAIN_MENU]![Command5].[Enabled]=fasle
    ** (as attachment pic1 shows)
    but it didnt work !!!!

    any suggestions
    Attached Thumbnails Attached Thumbnails pic1.bmp  

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    new question
    new thread
    please

    ..its helps others if they have a similar problem to you
    it helps you as other contribtuors may think its the same question and not look at it
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2012
    Posts
    12

    Exclamation

    Dear Sinndho,
    Am using the application which contains the select you suggested and all was going well until i faced a problem when the number of records in the tables (T1, T2 as the below example shows) was large
    each contains about 700 records.
    on e=running the query which contains the select i waited for more than 30 min. but didn't get any result and the application was hanged and i stopped it using the task manager!!

    any suggestions

    THANK YOU VERY MUCH

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There must be something wrong with your system, or the actual data set is very different from the sample data you posted formerly.

    I performed my tests using the protocol described hereafter on the following system:

    Processor:
    - Type: Intel Core 2 Duo E6850
    - Core speed: 2.998.7 MHz
    - Bus speed: 333.3 MHz
    - L1 Data cache: 2 x 32 KBytes
    - L2 Data cache: 4096 KBytes

    Memory:
    - Type: DDR2
    - Size: 2048 MBytes
    - DRAM Frequecy: 400 MHz

    Operating system:
    - Microsoft Windows XP Home
    - Version 2002
    - Service Pack 3

    Access:
    - MS Office Professional Edition 2003
    - Microsoft Office Access 2003 (11.8321.8341 ) SP3

    The test runs as follows:

    1. Create the tables T1 and T2, as described in your post.
    2. Fill both tables with 1000 rows of randomly generated data.
    3. Open a DAO.Recordset using the original query I posted.
    4. Open a DAO.Recordset using a slightly modified query where the Left() function replaces the Mid() function. The reason for this is that the Left() function is simpler than the Mid() function and so could yield faster results.
    5. Open ADODB Recordsets using the same queries as those described in 3 and 4.
    6. Create an index on the column Col1 in the Table T1 and another index on the column Col2 in the table T2.
    7. Perform the steps 3 to 5 on the indexed tables.

    Note: For a better accuracy of the results, every recordset (steps 3 to 7) was open 10 times in a loop and the mean time of execution was computed.

    Here are the results I got:
    Code:
    Without index:
                  DAO (Mid): 7128,1 (ms.)
                  DAO (Left): 6440,6 (ms.)
                  ADO (Mid): 7190,6 (ms.)
                  ADO (Left): 6534,4 (ms.)
    With index:
                  DAO (Mid): 6164,1 (ms.)
                  DAO (Left): 5445,3 (ms.)
                  ADO (Mid): 6195,3 (ms.)
                  ADO (Left): 5512,5 (ms.)
    As expected, the use of the Left() function brings a little gain in speed and DAO is slightly faster than ADODB (DAO is optimized for Access, unless it's Access that's optimized for DAO).

    The full code of the test protocol follows:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Declare Function GetTickCount Lib "Kernel32" () As Long
    
    Sub CreateTable_T1_T2()
    
        Const c_SQLDrop As String = "DROP TABLE T@I;"
        Const c_SQLCreate As String = "CREATE TABLE T@I ( Col@I TEXT(50) );"
        
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strT As String
        Dim i As Long
        
        Set dbs = CurrentDb
        For i = 1 To 2
            '
            ' Delete the existing table if any.
            '
            For Each tdf In dbs.TableDefs
                If tdf.Name = "T" & i Then Exit For
            Next tdf
            If Not tdf Is Nothing Then
                Set tdf = Nothing
                dbs.Execute Replace(c_SQLDrop, "@I", i), dbFailOnError
            End If
            '
            ' Create the table.
            '
            dbs.Execute Replace(c_SQLCreate, "@I", i), dbFailOnError
        Next i
        
    End Sub
    
    Sub CreateIndex_T1_T2()
    
        Const c_SQL As String = "CREATE INDEX IX_T@I ON T@I ( Col@I );"
        
        Dim i As Long
        
        For i = 1 To 2
            '
            ' Create the index.
            '
            CurrentDb.Execute Replace(c_SQL, "@I", i), dbFailOnError
        Next i
        
    End Sub
    
    Sub FillTable_T1_T2()
    '
    ' Fill both tables with 1000 rows of random data.
    '
        Const c_SQLT1 As String = "INSERT INTO T1 ( Col1 ) VALUES ( '@V' );"
        Const c_SQLT2 As String = "INSERT INTO T2 ( Col2 ) VALUES ( '@V' );"
        
        Dim i As Long
        Dim j As Long
        Dim str As String
        
        For i = 1 To 1000
            str = ""
            For j = 1 To 3
                str = str & Int((9 - 0 + 1) * Rnd + 0)
            Next j
            CurrentDb.Execute Replace(c_SQLT2, "@V", str), dbFailOnError
            str = ""
            For j = 1 To 3
                str = str & Int((9 - 0 + 1) * Rnd + 0)
            Next j
            For j = 1 To 3
                str = str & Chr(Int((90 - 65 + 1) * Rnd + 65))
            Next j
            CurrentDb.Execute Replace(c_SQLT1, "@V", str), dbFailOnError
        Next i
    
    End Sub
    
    Sub TestQueries()
        '
        ' Query using the Mid() function.
        '
        Const c_SQL1 As String = "SELECT Mid(T1.Col1,1,3) AS Expr1 " & _
                                 "FROM T1 " & _
                                 "WHERE (((Mid([T1].[Col1],1,3)) Not In (SELECT DISTINCT a.x FROM ( SELECT mid(Col1,1,3) AS x FROM T1) AS a " & _
                                 "INNER JOIN T2 ON a.x = T2.Col2 )));"
        '
        ' Query using the Left() function.
        '
        Const c_SQL2 As String = "SELECT Left(T1.Col1,3) AS Expr1 " & _
                                 "FROM T1 " & _
                                 "WHERE (((Left([T1].[Col1],3)) Not In (SELECT DISTINCT a.x FROM ( SELECT Left(Col1,3) AS x FROM T1) AS a " & _
                                 "INNER JOIN T2 ON a.x = T2.Col2 )));"
                                 
        Dim rstDAO As DAO.Recordset
        Dim rstADO As ADODB.Recordset
        Dim lngTcount As Long
        Dim i As Long
    '
    ' === Test DAO ===
    '
        lngTcount = GetTickCount
        For i = 1 To 10
            '
            ' With the query using the Mid() function.
            '
            Set rstDAO = CurrentDb.OpenRecordset(c_SQL1, dbOpenSnapshot)
            rstDAO.MoveLast
            rstDAO.Close
            Set rstDAO = Nothing
        Next i
        Debug.Print , "DAO (Mid): " & (GetTickCount - lngTcount) / 10 & " (ms.)"
        
        lngTcount = GetTickCount
        For i = 1 To 10
            '
            ' With the query using the Left() function.
            '
            Set rstDAO = CurrentDb.OpenRecordset(c_SQL2, dbOpenSnapshot)
            rstDAO.MoveLast
            rstDAO.Close
            Set rstDAO = Nothing
        Next i
        Debug.Print , "DAO (Left): " & (GetTickCount - lngTcount) / 10 & " (ms.)"
    '
    ' === Test ADODB ===
    '
        lngTcount = GetTickCount
        For i = 1 To 10
            '
            ' With the query using the Mid() function.
            '
            Set rstADO = New ADODB.Recordset
            rstADO.Open c_SQL1, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
            rstADO.MoveLast
            rstADO.Close
            Set rstADO = Nothing
        Next i
        Debug.Print , "ADO (Mid): " & (GetTickCount - lngTcount) / 10 & " (ms.)"
        
        lngTcount = GetTickCount
        For i = 1 To 10
            '
            ' With the query using the Mid() function.
            '
            Set rstADO = New ADODB.Recordset
            rstADO.Open c_SQL2, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
            rstADO.MoveLast
            rstADO.Close
            Set rstADO = Nothing
        Next i
        Debug.Print , "ADO (Left): " & (GetTickCount - lngTcount) / 10 & " (ms.)"
    
    End Sub
    
    Sub TestAll()
    '
    ' Run the full test protocol.
    '
        CreateTable_T1_T2
        FillTable_T1_T2
        Debug.Print "Without index:"
        TestQueries
        CreateIndex_T1_T2
        Debug.Print "With index:"
        TestQueries
        
    End Sub
    Have a nice day!

  9. #9
    Join Date
    Feb 2012
    Posts
    12
    Dear Sinndho,

    THANK YOU VERY MUCH for your time and for your help

    I checked the settings of my system our values are close
    and I even tried other computers and different versions of microsoft access with no success!!!

    I attached a compressed file (test.zip) of an access file if you can take a look on it, it contains part of my application

    Note: the access file contains
    tables
    1. ORIG (757 records)
    2. TEMP (750 records)

    Query
    slct_newID_many_records

    and for testing purposes I created same tables with less records
    ORIG_copy (3 records), TEMP_copy (2 records) and Query slct_newID_less_records same as the previous query for the tables with the less number of records

    Query slct_newID_less_records worked successfully
    Query slct_newID_many_records hanged until I ended the application using the task manager.


    please i need your help any suggestions
    thank you
    Attached Files Attached Files

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No wonder why it's so slow: your query performs a Cartesian product of the tables:
    Code:
    FROM ORIG, TEMP
    Also, the Format function is useless (but consumes ressources when the query is executed):
    Code:
    Format(Mid(all_data,1,7),'0000000')
    If you retrieve 7 characters from a string, you don't need to add heading zeroes until the resulting substring is 7 characters long: it is 7 char. long from the beginning. Moreover, if the substring is not numeric, the row won't be selected anyway.

    Finally, I'm almost sure that the DISTINCT predicate is useless here too, however it does not seem to significantly alter the performance.

    This query (which is a corrected version of the original slct_newID_many_records):
    Code:
    SELECT DISTINCT Mid(all_data,1,7) AS NEW_ID, 
                    Mid(all_data,32,32) AS C_NAME, 
                    Format(Mid(all_data,22,7),'000000000000000') AS S_AMT, 
                    Format(Now(),'yyyymmdd') AS S_DATE
    FROM ORIG
    WHERE (((Mid([all_data],1,7)) NOT IN (SELECT DISTINCT a.x  
                                          FROM (SELECT Mid(all_data,1,7 ) as x 
                                                FROM ORIG
                                         ) as a
    INNER JOIN TEMP ON a.x =TEMP.id)));
    Opens in less than 4 seconds on the same machine as the one I used for the tests in my previous post.
    Have a nice day!

  11. #11
    Join Date
    Feb 2012
    Posts
    12
    Thank you very much...
    My application is working successfully
    Your help is much appreciated

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •