Link to home
Start Free TrialLog in
Avatar of frogman22
frogman22

asked on

VBA INSERT Statement - Get MAX(Date/Time)

I need help with code sample to get the max(system_creation_date) where the acct_nbr in Tbale 1 = the acc_nbr in table 2. That data needs to be inserted into another table. Asingle row should only be returned per acct_nbr match. Table 1 has 65,000 records so I need to loop through until complete. I am new at this so if there is a beeter way to do it please let me know. I have taken code from an update statement to get started. BAN is a number data type and system_creation_date is a date data type. I would like to insert [Table 2] field names of BAN and min(system_creation_date) into Table 3 where [Table 1] BAN = [Table 2] BAN. Here is whta I have so far:

Private Sub TestData()

    Set db = CurrentDb()

    strsql = "SELECT * FROM [Table 1]"
    Set rst1 = db.OpenRecordset(strsql, dbOpenDynaset)
   
    If rst1.BOF And rst1.EOF Then
        'Do Nothing
    Else
        rst1.MoveLast
        lineCount = rst1.RecordCount
        rst1.MoveFirst
        retVal = SysCmd(acSysCmdInitMeter, "Matching Against Charge Table: ", lineCount)
        lineProgress = 1
               
        Do Until rst1.EOF

'I am lost at this point and want to do this:

insert [Table 2] field names of BAN and max(system_creation_date) into Table 3 where [Table 1] BAN = [Table 2] BAN
           
        retVal = SysCmd(acSysCmdUpdateMeter, lineProgress)
        lineProgress = lineProgress + 1
        rst1.MoveNext
        Loop
    End If
    MsgBox ("Process Complete")
     retVal = SysCmd(acSysCmdRemoveMeter)
End Sub
Avatar of Rog D
Rog D
Flag of United States of America image

It looks to me like you should be doing a join on table1 and table2, then get the max(system_creation_date)

So,

Select table1.acctNbr, max(system_creation_dat) from table1, table2
where table1.acctNbr = table2.acctnbr
group by table1.acctNbr.

Something like this should return a recordset of the acctNbr with the max(system_creation_Date).

Then you can do the insert on each of the records.

You can actually do an insert from this select, but that is a little more complicated.

Rog
try this query

select * from T1
where system_creation_date in (select max(system_creation_date) from T1 as A where acct_nbr=A.acct_nbr) and T1.acct_nbr=T2.acct_nbr
Avatar of frogman22
frogman22

ASKER

Trying to incorporate the code. getting syntax error
Private Sub GetADDRNAMELINKINFO()

    Set db = CurrentDb()

    strsql = "SELECT * FROM [Tbl: Store Raw Data Here]"
    Set rst1 = db.OpenRecordset(strsql, dbOpenDynaset)
   
    If rst1.BOF And rst1.EOF Then
        'Do Nothing
    Else
        rst1.MoveLast
        lineCount = rst1.RecordCount
        rst1.MoveFirst
        retVal = SysCmd(acSysCmdInitMeter, "Matching Against Charge Table: ", lineCount)
        lineProgress = 1
               
        Do Until rst1.EOF
'GETTING SYNTAX ERROR HERE

            strsql2 = "SELECT * " & vbCrLf & _
            "FROM [NORSNAPADM_ADDRESS_NAME_LINK] WHERE [system_creation_date] in ("SELECT max(system_creation_date) & vbCrLf & _
            "FROM [NORSNAPADM_ADDRESS_NAME_LINK] as A WHERE [BAN] = " & rst1![BAN]") & " " & vbCrLf & _
            "AND [NORSNAPADM_ADDRESS_NAME_LINK] = " & rst1![BAN] & vbCrLf
   
            Set rst2 = db.OpenRecordset(strsql2, dbOpenDynaset)
       
                'INSERT STATEMENT HERE            
        retVal = SysCmd(acSysCmdUpdateMeter, lineProgress)
        lineProgress = lineProgress + 1
        rst1.MoveNext
        Loop
    End If
frogman22,
test first this query

select * from T1
where system_creation_date in (select max(system_creation_date) from T1 as A where acct_nbr=A.acct_nbr) and T1.acct_nbr=T2.acct_nbr


if it gives you the correct records...

just replace table name and field names accordingly
How's this (not too sure about the field names):

    INSERT INTO [Table 3] (acct_nbr, max_date)
    SELECT [Table 2].acct_nbr, Max([Table 2].system_creation_date)
    FROM [Table 1], [Table 2]
    WHERE [Table 2].acct_nbr = [Table 1].acct_nbr
    GROUP BY [Table 2].acct_nbr
You mention max(system_creation_date) and min(system_creation_date) in your question - which are you after?
sorry - min(system_creation_date)
In which case, this should do it:

    INSERT INTO [Table 3] (acct_nbr, min_date)
    SELECT [Table 2].acct_nbr, Min([Table 2].system_creation_date)
    FROM [Table 1], [Table 2]
    WHERE [Table 2].acct_nbr = [Table 1].acct_nbr
    GROUP BY [Table 2].acct_nbr

I'm still not too sure about the field names though - your question doesn't give enough detail to be 100% certain on this.
Just in case you haven't come to the same conclusion yet, using an INSERT INTO query is much less code that manipulating record sets - and therefore easier to understand & maintain.

You can invoke the above query from VB if you wish, like this:
Dim strSQL As String
 
strSQL = "INSERT INTO [Table 3] (acct_nbr, min_date) " & _
         "SELECT [Table 2].acct_nbr, Min([Table 2].system_creation_date) " & _
         "FROM [Table 1], [Table 2] " & _
         "WHERE [Table 2].acct_nbr = [Table 1].acct_nbr " & _
         "GROUP BY [Table 2].acct_nbr"
CurrentDb.Execute strSQL, dbFailOnError

Open in new window


currentdb.execute _
"insert into T3 (acct_nbr,min_date) select T2.acc_nbr, T2.system_creation_date from T2 where T2.system_creation_date in (select min(system_creation_date) from T2 as A where acct_nbr=A.acct_nbr) and T1.acct_nbr=T2.acct_nbr"
capricorn1,

Your latest query ends "... and T1.acct_nbr=T2.acct_nbr", but I don't see T1 in any FROM clause - am I missing something?
sorry, typo

currentdb.execute _
"insert into T3 (acct_nbr,min_date) select T2.acc_nbr, T2.system_creation_date from T2,T1 where T2.system_creation_date in (select min(system_creation_date) from T2 as A where acct_nbr=A.acct_nbr) and T1.acct_nbr=T2.acct_nbr"
capricorn1,

Within your last query (by the way, I think 'acc_nbr' should be 'acct_nbr') you have the following sub-query:

    select min(system_creation_date) from T2 as A where acct_nbr=A.acct_nbr

Looking at the WHERE clause for this, which table is being used to obtain the acct_nbr field on the left hand side of the equals sign?

Without a table qualifer, aren't the fields on BOTH sides of the equals sign effectively read from the SAME table (table T2, aliased to table A)?
Yep, definitely returns the wrong results, at least as it stands!  :-)
I think capricorn1's query should read as follows:

    INSERT INTO [Table 3] ( acct_nbr, min_date )
    SELECT [Table 2].acct_nbr, [Table 2].system_creation_date
    FROM [Table 2], [Table 1]
    WHERE [Table 2].system_creation_date IN (SELECT Min(system_creation_date) FROM [Table 2] AS A WHERE [Table 2].acct_nbr = A.acct_nbr) AND
    [Table 1].acct_nbr = [Table 2].acct_nbr

Perhaps you can confirm this right capricorn1?
I've also done some timings based on 65000 records in Table 1, with 3 records in Table 2 for each record in Table 1.  I do not have ANY indexes or relationships defined between the three tables.

On my machine it takes around 3 seconds using my GROUP BY query (ID: 20830669) and about 10.5 seconds using capricorn1's corrected suggestion (ID: 20831887).

On the basis that my suggestion is simpler SQL and is faster too, no guesses then as to which solution I'd recommend!  :-)
All the help is great. I am starting to understand. I do not need the "Loop" concept at all?I am using the following code and getting the following error.
Dim strSQL As String
 
strSQL = "INSERT INTO [Table 3] (BAN, min_date) " & _
         "SELECT [NORSNAPADM_NAME_LINK].BAN, Min([NORSNAPADM_NAME_LINK].sys_creation_date) " & _
         "FROM [Tbl: Individual Data], [NORSNAPADM_NAME_LINK] " & _
         "WHERE [NORSNAPADM_NAME_LINK].BAN = [Tbl: Individual Data].BAN" & _
         "GROUP BY [NORSNAPADM_NAME_LINK].BAN"
CurrentDb.Execute strSQL, dbFailOnError

syntax-error.doc
ASKER CERTIFIED SOLUTION
Avatar of Jez Walters
Jez Walters
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
try this correction

strSQL = "INSERT INTO [Table 3] (BAN, min_date) " & _
         "SELECT [NORSNAPADM_NAME_LINK].BAN, Min([NORSNAPADM_NAME_LINK].sys_creation_date) " & _
         "FROM [Tbl: Individual Data], [NORSNAPADM_NAME_LINK] " & _
         "WHERE [NORSNAPADM_NAME_LINK].BAN = [Tbl: Individual Data].BAN " & _
         "GROUP BY [NORSNAPADM_NAME_LINK].BAN"
CurrentDb.Execute strSQL, dbFailOnError


check the *minimum dates* return per record  after you run the query..
capricorn1,

How about some feedback on YOUR query?
All,

It is running as we speak. After it completes I will check the data and give an update. Thank you very much for your help, time, and patience. This has been a greta learning experience for me.
For what it's worth, you only need to delimit table/field names with square brackets if there is a space in the name (although it doesn't matter if you do it anyway):
Dim strSQL As String
 
strSQL = "INSERT INTO [Table 3] (BAN, min_date) " & _
         "SELECT NORSNAPADM_NAME_LINK.BAN, Min(NORSNAPADM_NAME_LINK.sys_creation_date) " & _
         "FROM [Tbl: Individual Data], NORSNAPADM_NAME_LINK " & _
         "WHERE NORSNAPADM_NAME_LINK.BAN = [Tbl: Individual Data].BAN " & _
         "GROUP BY NORSNAPADM_NAME_LINK.BAN"
CurrentDb.Execute strSQL, dbFailOnError

Open in new window

Happy to help - just remember to accept the solution you actually used!  ;-)
The query never finishes. I created a table with a single record and it does not finish either. The querey just runs and runs. I can run a select statement in TOAD and the return is instant. I can run an update query in the same module with no problem so there are no connection issues. Any Ideas?
Looks like a performance issue to me - it's difficult to be positive, but I suspect the query would finish eventually!  Are you SURE you're reading ALL fields of ALL records when you're using TOAD?
Perhaps you could describe the "single record" case you tried and I'll have a go finding out what's gone wrong.

Alternatively, you could attach a file to a comment here (as long as it's small and there are no privacy issues)
you're right. I am not. the NORSNAPADM_NAME_LINK table is VERY large.  Is there a way to not read all fields of all records to improve the code I am using?
I'm trying to upload a sample database to www.ee-stuff.com - does anybody know how to do this?!
By the way, I take it NORSNAPADM_NAME_LINK, [Tbl: Individual Data] and [Table 3] are all tables - NOT queries?
Also, from the name, is NORSNAPADM_NAME_LINK a linked table?
Right I'll have a go uploading the zip archive, but renaming it from "Sample.zip" to "Sample.txt" beforehand.

If this works you should be able to download the attached file and then rename it back again!
Sample.txt
Bingo!
If you open the modSample module, then enter the following in the Immediate Window:

    PopulateTables
    TimeQueries
Does that work for you?
Table 1 = Single field [BAN] with Single populated Record.  Table 3 = Two fields [BAN] - Number Data Type, [MIN_DATE] - Date data type, Table 3 is empty. NORSNAPADM_ADDRESS_NAME_LINK is a linked table to an oracle database.
CURRENT TEST CODE:

strSQL = "INSERT INTO [Table 3] (BAN, min_date) " & _
         "SELECT [NORSNAPADM_ADDRESS_NAME_LINK].BAN, Min([NORSNAPADM_ADDRESS_NAME_LINK].sys_creation_date) " & _
         "FROM [Table1], [NORSNAPADM_ADDRESS_NAME_LINK] " & _
         "WHERE [NORSNAPADM_ADDRESS_NAME_LINK].BAN = [Table1].BAN " & _
         "GROUP BY [NORSNAPADM_ADDRESS_NAME_LINK].BAN"
CurrentDb.Execute strSQL, dbFailOnError
test-data.txt
Aha, so the NORSNAPADM_ADDRESS_NAME_LINK table IS a linked table - that'll be why it's so slow then!
You could try snapshotting the linked table before running the query - this should significantly improve performance:
Dim strSQL As String
 
strSQL = "CREATE TABLE tblTempTable (BAN Long, sys_creation_date DateTime)"
CurrentDb.Execute strSQL, dbFailOnError
 
strSQL = "INSERT INTO tblTempTable (BAN, sys_creation_date) " & _
         "SELECT BAN, sys_creation_date " & _
         "FROM NORSNAPADM_NAME_LINK"
CurrentDb.Execute strSQL, dbFailOnError
 
strSQL = "INSERT INTO [Table 3] (BAN, min_date) " & _
         "SELECT tblTempTable.BAN, Min(tblTempTable.sys_creation_date) " & _
         "FROM [Tbl: Individual Data], tblTempTable " & _
         "WHERE tblTempTable.BAN = [Tbl: Individual Data].BAN " & _
         "GROUP BY tblTempTable.BAN"
CurrentDb.Execute strSQL, dbFailOnError
 
strSQL = "DROP TABLE tblTempTable"
CurrentDb.Execute strSQL, dbFailOnError

Open in new window

I uploaded your Db all works great. Thats some good code I can learn from. So it appears the code is good just the process is slow. No way to improve?
Please confirm that NORSNAPADM_NAME_LINK, [Tbl: Individual Data] and [Table 3] are all tables - NOT queries.
They are all tables. The temp table is a good idea but NORSNAPADM_NAME_LINK holds millions of records and would not work for me.
Hopefully you can now at least see first hand what I mean about the performance of capricorn1's query.
yes. I am going to accept your solution listed earlier. Thanks for all your help and extra effort on this issue.
From your question, I thought you said you had around 65000 records - not "millions of records"?

If NORSNAPADM_NAME_LINK has so many records, and it's a linked table that explains why the query takes so long!  A lot of records inevitably takes a lot of time to process - this will only be worse if network issues are also involved.
It's impossible for me to know whether it's any faster, but here's a solution using record sets:
Dim blnAddRecord As Boolean
Dim dbsDatabase As Database
Dim rstIndividualData As Recordset
Dim rstNORSNAPADM As Recordset
Dim rstTable3 As Recordset
Dim strSQL As String
 
Set dbsDatabase = CurrentDb
dbsDatabase.Execute "DELETE * FROM [Table 3]", dbFailOnError
Set rstTable3 = dbsDatabase.OpenRecordset("Table 3", dbOpenTable, dbAppendOnly)
strSQL = "SELECT * FROM [Tbl: Individual Data] ORDER BY BAN"
Set rstIndividualData = dbsDatabase.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
strSQL = "SELECT * FROM NORSNAPADM_NAME_LINK ORDER BY BAN, sys_creation_date"
Set rstNORSNAPADM = dbsDatabase.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
 
blnAddRecord = True
With rstIndividualData
    Do Until .EOF
        With rstNORSNAPADM
            Do Until .EOF
                If !BAN <> rstIndividualData!BAN Then  ' Out of step
                    blnAddRecord = True
                    Exit Do
                ElseIf blnAddRecord Then
                    With rstTable3
                        .AddNew
                        !BAN = rstNORSNAPADM!BAN
                        !MIN_DATE = rstNORSNAPADM!sys_creation_date
                        .Update
                    End With
                    blnAddRecord = False
                End If
                .MoveNext
            Loop
        End With
        .MoveNext
    Loop
End With

Open in new window

I realise this question is now closed, but I'd be REALLY, REALLY interested in knowing if this is quicker than using a temporary table.

Pretty please, give it a whirl and let me know how you get on!  :-)
There are other (more compact) ways of doing this, but I've deliberately structured the code to read each record once only, with separate record sets for the linked and non-linked tables.

For what it's worth, I time this as nearly 4 times slower than either using a GROUP BY query or using a temporary table - but you may well get completelty different results with the linked table you've got!

Please, please let me know what timings you observe!
i am testing it now. It is  at step:
strSQL = "SELECT * FROM NORSNAPADM_NAME_LINK ORDER BY BAN, sys_creation_date"
Set rstNORSNAPADM = dbsDatabase.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

when they completes I will advise on the time.  

Another question:
The code looks like it will get ALL sys_creation_date per BAN instead just the MIN(sys_creation_date) needed per ban.
frogman22,

did you get  the correct results?
yes
The code I posted reads all records in [Tbl: Individual Data] and looks for a matching record in NORSNAPADM_NAME_LINK.  If found, the FIRST matching record in NORSNAPADM_NAME_LINK is then used to add a record to [Table 3] (hence the blnAddRecord flag).  Because of the explicit ORDER BY clause I used, the sys_creation_date value used to populate [Table 3] will therefore be the minimum value for the relevant BAN value.

Like I said yesterday, I deliberately went for this approach so that separate record sets are used for the linked and non-linked tables.  Hopefully, this will speed up the reading from your Oracle database - but the only way to find out is to try it!
Hey, I just had a bright idea - if you can't snapshot ALL of NORSNAPADM_NAME_LINK, can you snapshot the minimum sys_creation_date values for each BAN instead?  This could well be the best solution, as the temporary table should have a lot less records.

I had a go with this idea, and my timings are around 10% quicker than snapshotting the entire table (as in ID: 20833248 above)!
strSQL = "CREATE TABLE tblTempTable (BAN Long, MIN_DATE DateTime)"
dbsDatabase.Execute strSQL, dbFailOnError
 
strSQL = "INSERT INTO tblTempTable (BAN, MIN_DATE) " & _
         "SELECT BAN, Min(sys_creation_date) " & _
         "FROM NORSNAPADM_NAME_LINK " & _
         "GROUP BY BAN"
dbsDatabase.Execute strSQL, dbFailOnError
 
strSQL = "INSERT INTO [Table 3] (BAN, MIN_DATE) " & _
         "SELECT tblTempTable.BAN, tblTempTable.MIN_DATE " & _
         "FROM [Tbl: Individual Data], tblTempTable " & _
         "WHERE tblTempTable.BAN = [Tbl: Individual Data].BAN"
dbsDatabase.Execute strSQL, dbFailOnError
 
strSQL = "DROP TABLE tblTempTable"
dbsDatabase.Execute strSQL, dbFailOnError

Open in new window

Fancy seeing if this is any quicker at your end?
I will try today and let you know. :-)