Updating a table

[paul.clarke]

Hi.

To cut a long story short, i gather excel sheets from various suppliers, bang them into 1 excel sheet and import this in access and use an append query so it updates into a table.

my problem is when i run do the same the next week as i do on a weekly basis, if the suppliers change data on a record which i have already have in my access table i.e a despatch date changes, my append query does not update the table with the new information.

How can i get the new information updated into my table, without creating duplicates.

Thanks

 

[DCrake]

What you need to do is to only run the append query for records that don't already exist in the table. This could be by identifying the invoice number. This will give you the new records.

Then run an update query based on matching records and update all the fields accordingly.

It all depends if you do a like for like comparison to find (non)matching records.

 

[paul.clarke]

OK i can do the append to get all the information in my table.

Is there an easy way which would identify which records have new data, or would it be a manual process going record by record, there could be more than 1 new peice of info on 1 record so i need all fields to update at the same time.
And there will be hundreds of records each week, some will be new, some changed, some not etc if u understand.

I tried creating a update query without success, kept saying something about specify what field/table i want to update. Is it not like append query where u add the table and then pick table u want to append from drop dwn list.

 

[DCrake]

When creating an update query you need to include both tables in the top pane and create a join on the matching fields.

Then bring down the fields in the target table into the lower pane you will see a row that says "Update With" choose the matching field in the source table.

 

[paul.clarke]

Thank you, I will give that a try.
So to confirm i run the append to get all data into table regardless whether its got new information or not.
Then i run the update and that will update any changes to existing records already within the table

 

[DCrake]

Step 1
Import the whole file into a temp file

Step 2
Create an unmatched query between the temp table and the live table. (this will be the basis of your append query)

Step 3
Create a matched query between the temp table and the live table. (this wiill be the basis of your update query)

Run the matched query first
run the unmatched second
Delete the contents of the temp table.

 

[paul.clarke]

Thanks. The unmatched query works great.

I tried the matched, by using the new query - find duplicate option, but that only seems to find duplicates within 1 table i.e either my temp file or my table.. How do i create a "matched query"?

 

[DCrake]

When you create the matched query it does as it suggests it finds records in both tables that have the same matching field contents, ie invoice numbers. Once you have got this you can bring down other fields and update one table from the other with the knowldege that the correct record will be updated.

 

[paul.clarke]

i undertand that, however i dont have create a matched query option in the list when i click "new" query

 

[DCrake]

Here is a code snippet that will do the whole process in one fail swoop.

Function UpdateInvoices()
'This function enumerates through a source table and checks if a matching record
'exists in a target table.
'If the match is not found it adds the record, otherwise it edits the record
'All fields are either added or updated in the target table.

'Define two recordsets and three counters

Dim RsSource As DAO.Recordset
Dim RsTarget As DAO.Recordset
Dim iOld As Integer
Dim iNew As Integer
Dim iIndex As Integer


'Open the temporary import table (Source table)
Set RsSource = CurrentDb.OpenRecordset("TblTempImport")
'Check for null records
If Not RsSource.EOF And Not RsSource.BOF Then
    'Loop through the table
    Do Until RsSource.EOF
        'Test to see if the current record exists in the target table
        Set RsTarget = CurrentDb.OpenRecordset("Select * from TblLiveTable Where MatchingKey = " & RsSource("MatchingKey"))
        'If missing then treat record as new record and append the primary key
        If RsTarget.EOF Then
            RsTarget.AddNew
            RsTarget("MatchingKey") = RsSource("MatchingKey")
            'keep a running count of how many new records have been created
            iNew = iNew + 1
        Else
        'Record found switch to edit mode
            RsTarget.Edit
            'keep a running count of how many new records have been editied
            iOld = iOld + 1
        End If
        'Loop through all the fields in the source table and update/append them to the target table
        'It is important that the fields are in the same position in each the source and target tables
        'Note the -2 equates to setting the ordinal number to the second field in the list to the last field
        For nIndex = 1 To RsSource.Fields.Count - 2
            RsTarget(nIndex) = RsSource(nIndex)
        Next
        'Save the current record
        RsTarget.Update
        'Move to the next record in the temp import table
        RsSource.MoveNext
        'Close the target recordset
        RsTarget.Close
    Loop
    'Close the source recordset
    RsSource.Close
    'clear the instances of the recordsets
    Set RsSource = Nothing
    Set RsTarget = Nothing
    Dim Msg As String
    'Build as string for the user message box to provide feedback
    Msg = "A total of " & iNew & " record(s) were added and a total of " & iOld & " record(s) were updated."
    MsgBox Msg, vbInformation + vbOKOnly, "Import Completed"
End Function

You need to copy this function into an existing or new standard module. If new do not give it same name as function.

To activate it either use a command button or a macro

Command button
Call UpdateInvoices()

Macro
RunCommand UpdateInvoices()


Note you will have to change the table and field names to suit your structure and naming conventions.

Code is unchecked and table/fieldnames are for brevity only.

 

[paul.clarke]

now im completly lost, i have no experience what so ever with code, macros etc etc

 

[DCrake]

Then I suggest you start learning. If you want to use automated processes this is the place to start.