Move data from 1 table to another

[MyMel]

Hi,

I have a Invoice table that is imported into Access from Oracle
that has multiple entries for the same invoices. Everytime an
adjustment is made to the invoice in Oracle a new record is
created. What I am trying to do is to move only the most current
invoice row to another table using fields call "Record Complete
Date" and Invoice#.

This is my code and I'm not getting anywhere with it. Would
someone please offer some guidance please.

Function DIRTRoll()

Dim StoreInvoice As String
Dim StoreDate As Date
Dim intCntr As Integer

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Invoice#")

Set db = CurrentDb()
Set ToTable = db.OpenRecordset("DIRT_Table")
Set FromTable = db.OpenRecordset("DIRT_ImportTable")

StoreInvoice = FromTable![Invoice#]
StoreDate = FromTable![Record Completion Date]

FromTable.MoveFirst

'ToTable.MoveLast
'FromTable.MoveLast

If FromTable.EOF = False Then StoreDate = FromTable![Record
Completion Date]


Do Until FromTable.EOF
If StoreInvoice = FromTable![Invoice#] Then
If StoreDate <= FromTable![Record Completion Date] Then
FromTable.MoveNext
Else
If StoreDate > FromTable![Record Completion Date]
Then
'CopyRecord(,"DIRT_Table",,,,,)

End If
End If
Else

End If

FromTable![Invoice#].SetFocus
FromTable.MoveNext
Loop


ToTable.Close
FromTable.Close

End Function


Thank you,

Melody

 

[Brotha Lee]

Mel,

Maybe this piece of code will do the trick for you:

'First open recordset with all invoice numbers (unique values only)
dim rsInvoice as recordset
dim AppendSQL as string

set rsInvoice = currentdb.openrecordset(SELECT DISTINCT INVOICE# FROM
DIRT_importtable)

'Loop through all the invoices in the recordset and retreive the most
current records for that invoice

while not rsInvoice.eof
'Query the invoice table and only retreive the most current record
(based on date and append to the new table
'The Field1, Field2 etc should be the names of your table columns.
'Remember to have the fields from the INSERT INTO in exactly the same order
as the SELECT clause
AppendSQL = "INSERT INTO DIRT_Table (Field1, Field2, Field3, Field4) "
& _
"SELECT TOP 1 (Field1, Field2, Field3, Field4)
FROM DIRT_Importtable where Dirt_importtable.invoice# =
rsInvoice.fields("Invoice#") ORDER BY [DirtRecord Completion Date] Desc"

'Uncomment this line if you want to suppress warnings
'Docmd.setwarnings false

'Execute the SQL statement
docmd.runSQL AppendSQL
rsInvoice.movenext
wend

'If you have suppressed the errors run this code to set warnings back on
'docmd.setwarnings true

HTH

 

[MyMel]

Brotha I Thank you,

Melody

Mel,

Maybe this piece of code will do the trick for you:

'First open recordset with all invoice numbers (unique values only)
dim rsInvoice as recordset
dim AppendSQL as string

set rsInvoice = currentdb.openrecordset(SELECT DISTINCT INVOICE# FROM
DIRT_importtable)

'Loop through all the invoices in the recordset and retreive the most
current records for that invoice

while not rsInvoice.eof
'Query the invoice table and only retreive the most current record
(based on date and append to the new table
'The Field1, Field2 etc should be the names of your table columns.
'Remember to have the fields from the INSERT INTO in exactly the same order
as the SELECT clause
AppendSQL = "INSERT INTO DIRT_Table (Field1, Field2, Field3, Field4) "
& _
"SELECT TOP 1 (Field1, Field2, Field3, Field4)
FROM DIRT_Importtable where Dirt_importtable.invoice# =
rsInvoice.fields("Invoice#") ORDER BY [DirtRecord Completion Date] Desc"

'Uncomment this line if you want to suppress warnings
'Docmd.setwarnings false

'Execute the SQL statement
docmd.runSQL AppendSQL
rsInvoice.movenext
wend

'If you have suppressed the errors run this code to set warnings back on
'docmd.setwarnings true

HTH

Hi,

I have a Invoice table that is imported into Access from Oracle
that has multiple entries for the same invoices. Everytime an
adjustment is made to the invoice in Oracle a new record is
created. What I am trying to do is to move only the most current
invoice row to another table using fields call "Record Complete
Date" and Invoice#.

This is my code and I'm not getting anywhere with it. Would
someone please offer some guidance please.

Function DIRTRoll()

Dim StoreInvoice As String
Dim StoreDate As Date
Dim intCntr As Integer

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Invoice#")

Set db = CurrentDb()
Set ToTable = db.OpenRecordset("DIRT_Table")
Set FromTable = db.OpenRecordset("DIRT_ImportTable")

StoreInvoice = FromTable![Invoice#]
StoreDate = FromTable![Record Completion Date]

FromTable.MoveFirst

'ToTable.MoveLast
'FromTable.MoveLast

If FromTable.EOF = False Then StoreDate = FromTable![Record
Completion Date]


Do Until FromTable.EOF
If StoreInvoice = FromTable![Invoice#] Then
If StoreDate <= FromTable![Record Completion Date] Then
FromTable.MoveNext
Else
If StoreDate > FromTable![Record Completion Date]
Then
'CopyRecord(,"DIRT_Table",,,,,)

End If
End If
Else

End If

FromTable![Invoice#].SetFocus
FromTable.MoveNext
Loop


ToTable.Close
FromTable.Close

End Function


Thank you,

Melody