Concepts of Update Query

[shelby55]

Hello

I am using Access 2003.

I want to be able to create a button in my database that will import data to a temp table and then update my "real" table with the contents of the temp table.

So if existing records have changes in data then I want them to be overwritten with the temp table and if the temp table has records that aren't currently in the "real" table then to add them.

Is the update query the way to go? Thanks.

 

[dhookom]

An update query will probably work if you have matching primary key fields in the temp and real tables.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi Duane

That is what I thought so I created the query and linked all fields that matched (which is all of them) but all it did was clear all data from the temp table but not appending to the real table.

The temp table still had 16 records but no data in any of the rows. In this instance, all 16 were new records so shouldn't have updating them mean they'd get added?

Thanks.

 

[PWise]

Sorry but no to add records you have to use an append query

 

[PWise]

what you can do is run this append query

insert into tablename(Primarykey)
Select Primarykey
from temptable
left join tablename
on temptable.Primarykey=tablename.Primarykey
where tablename.Primarykey is null

then run your updatequery no need to link all fields just Primarykey

 

[Gammachaser]

An Update Query changes data that is already in the table. An Append Query ("SELECT INTO...") adds rows to a table -- I think you need to use both to get what you want. If there is a one-step way, I would like to learn it, too.

 

[Gammachaser]

Dang -- I hate when someone else answers a question while I am typing my answer...

 

[dhookom]

There is a one step query that uses a LEFT or RIGHT JOIN. If shelby55 would share the SQL or table structures and primary keys, someone might be able to help.

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

Gammachaser:
Sorry about that it happans to all of us all the time but this the nice about tek tips someone posts a question and you have a couple of pepole answereing it

 

[shelby55]

Hi

I may have gotten ahead of myself on this one so let me back up.

I have data in an Excel format that I will need to import into my database. I jumped ahead by assuming I would need a temporary table to import to and then either add or overwrite depending on whether the record already existed in the table. I also knew that I wanted the command button to delete all entries in the temp table before proceeding with each import.

So both the real and temp tables are exactly the same:
Surg_area (text)
PrimaryPx (text)
PatientName (text)
MRN (text)
AdmDateTime (date/time)
PrimSurgeon (text)
CaseCreateDateTime (date/time)
SurgeryStartDateTime (date/time)
ORCaseNumber (text)

Right now the unique identifier is the MRN and AdmDateTime combined but since sometimes the admit date will be changing, that isn't good. However, ORCaseNumber is unique so I'll be switching to that.

The code that I have so far which is working opens up a dialog box for me to pick the import file, deletes whatever is in the temp table and imports the data I selected to the temp file:

Private Sub cmdImportData_Click()
On Error GoTo Err_cmdImportData_Click
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog, strTableName As String, strSQL As String
        
    If MsgBox("Navigate to your data file. Please press 'Cancel' if you do not wish to proceed", _
            vbInformation + vbOKCancel, "Raw Data Import") = vbCancel Then GoTo Exit_cmdImportData_Click
 
    strTableName = "tmpFracturedHips"
    strSQL = "DELETE tmpFracturedHips.* FROM tmpFracturedHips"
    
    Application.SetOption "Confirm Action Queries", False
    Application.SetOption "Confirm Record Changes", False
    
    DoCmd.RunSQL strSQL
    
    Application.SetOption "Confirm Action Queries", True
    Application.SetOption "Confirm Record Changes", True

    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    Dim vrtSelectedItem As Variant
    Dim strFileName

    'Use a With...End With block to reference the FileDialog object.
    With fd
            
         'Do Not Allow the selection of multiple file.
        .AllowMultiSelect = False

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                DoCmd.Hourglass True
                DoCmd.TransferSpreadsheet acImport, , strTableName, vrtSelectedItem, True
                DoCmd.Hourglass False

            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
            GoTo Exit_cmdImportData_Click
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing

Exit_cmdImportData_Click:
    DoCmd.Hourglass False
    Exit Sub

Err_cmdImportData_Click:
    DoCmd.Hourglass False
    MsgBox "Import of raw data was not successful" & vbCrLf & vbCrLf & _
            "Ensure that your Excel file corresponds fully to the template format " & _
            "(check against example data provided in the Excel raw data template)", _
            vbCritical + vbOKOnly, "Raw Data Import Failed"
    MsgBox Err.Description
    Resume Exit_cmdImportData_Click


End Sub

So what is left is to figure out what data to append and what to overwrite. Thanks.

 

[dhookom]

You could start by creating a query with both tables and joining the unique/primary key fields. Then change it to an update query to update the fields from one table with the fields from the other table.

For the append query, you can create a query with both table but change the join to include all records from the temp table. Add all the fields from the temp table and change the query to an append query. Set a criteria to the real table primary key field is null.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Thanks Duane.

When linking for the update query what are the join properties i.e. is the left table name the temp or real table?

 

[shelby55]

P.S.

if I only want one button how can I create the "case" statement so if unmatched then append otherwise, update?

Thanks.

 

[Gammachaser]

@PWise -- I know it happens all the time... I should have used a when I typed that. I post on a Nuclear forum (Nukeworker.com) often and it happens there, too. What I really hate is that your answer was better and more complete.

 

[dhookom]

Considering the Employees table in Northwind, the partial update query would be:

UPDATE Employees INNER JOIN EmployeesImport ON Employees.EmployeeID = EmployeesImport.EmployeeID SET Employees.LastName = [EmployeesImport].[Lastname], Employees.MI = [EmployeesImport].[MI], Employees.FirstName = [EmployeesImport].[Firstname], Employees.Title = [EmployeesImport].[Title], Employees.TitleOfCourtesy = [EmployeesImport].[TitleOfCourtesy], Employees.BirthDate = [EmployeesImport].[BirthDate], Employees.HireDate = [EmployeesImport].[Hiredate];

The append query would look like:

INSERT INTO Employees ( EmployeeID, LastName, MI, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo )
SELECT EmployeesImport.EmployeeID, EmployeesImport.LastName, EmployeesImport.MI, EmployeesImport.FirstName, EmployeesImport.Title, EmployeesImport.TitleOfCourtesy, EmployeesImport.BirthDate, EmployeesImport.HireDate, EmployeesImport.Address, EmployeesImport.City, EmployeesImport.Region, EmployeesImport.PostalCode, EmployeesImport.Country, EmployeesImport.HomePhone, EmployeesImport.Extension, EmployeesImport.Photo, EmployeesImport.Notes, EmployeesImport.ReportsTo
FROM Employees RIGHT JOIN EmployeesImport ON Employees.EmployeeID = EmployeesImport.EmployeeID
WHERE (((Employees.EmployeeID) Is Null));

Duane
Hook'D on Access
MS Access MVP