September 6, 2008 09:39pm pdt

Member Login

Hall of Fame

Yearly

1capricorn1 2,388,276
2DatabaseMX 1,647,762
3LSMCons... 777,888
4boag2000 677,273
5peter57r 560,613
6GRayL 471,439
7harfang 445,253
8jimhorn 328,360
9cactus_d... 309,076
10matthews... 262,674
11TextReport 236,057
12hnasr 234,650
13angelIII 176,604
14ee_rlee 173,155
15mbizup 160,554
16puppydog... 156,767
17jimpen 152,284
18LPurvis 141,629
19jefftwilley 140,435
20JezWalters 138,435
21omgang 132,199
22Natchiket 129,463
23Rick_Ric... 128,752
24tbsgadi 124,218
25irudyk 123,601

	[x] Attachment Details

VBA INSERT Statement - Get MAX(Date/Time)

Zones: Microsoft Access Database, Microsoft Programming

Tags: VBA, Microsoft Access 2003

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

Start your free trial to view this solution

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

5.0

Question Stats

Zone: Microsoft

Question Asked By: frogman22

Solution Provided By: JezWalters

Participating Experts: 3

Solution Grade: A

Translate:

Loading Advertisement...

Open Discussion

Comment by JezWalters

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.

Comment by JezWalters

It's impossible for me to know whether it's any faster, but here's a solution using record sets:

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

           
           
             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 Select All

Comment by JezWalters

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! :-)

Comment by JezWalters

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!

Comment by frogman22

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.