Combined "update or Append" query

altiplano
altiplano used Ask the Experts™
on
Hi experts,

Is it possible to construct a combined "update or append" query in Access?
I have two identically structured tables with partially overlapping data which I want to merge:
- If the record exists in TableA (based on certain criteria), update it with some of the fields from Table B
- If the record doesn't exist in TableA, append it

I could do it in various steps with intermediate tables, but I'm sure there is a much cleverer way of doing this.

regards,
Michiel
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It can be done, under certain circumstances.  check out this link
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23455292.html
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
No.  You need to run two independent queries ...

You might be able to accomplish that using VBA code, but the simplest approach is two queries ... Append and Update.

mx
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
It is, if the tables have a unique key.
This old tip from Smart Access is one of my favourites:

<quote>

Update and Append Records with One Query

By Alan Biggs

Did you know that you can use an update query in Access to both update
and add records at the same time? This is useful if you have two
versions of a table, tblOld and tblNew, and you want to integrate the
changes from tblNew into tblOld.

Follow these steps:

1. Create an update query and add the two tables. Join the two tables
by dragging the key field of tblNew onto the matching field of tblOld.

2. Double-click on the relationship and choose the join option that
includes all records from tblNew and only those that match from
tblOld.

3. Select all the fields from tblOld and drag them onto the QBE grid.

4. For each field, in the Update To cell type in tblNew.FieldName,
where FieldName matches the field name of tblOld.

5. Select Query Properties from the View menu and change Unique
Records to False. (This switches off the DISTINCTROW option in the SQL
view. If you leave this on you'll get only one blank record in your
results, but you want one blank record for each new record to be added
to tblOld.)

6. Run the query and you'll see the changes to tblNew are now in
tblOld.

This will only add records to tblOld that have been added to tblNew.
Records in tblOld that aren't present in tblNew will still remain in
tblOld.

</quote>

/gustav

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
I MUST have been assuming the tables did not have the same unique key, lol.  Good ole Smart Access. To damn bad the publishers have gotten greedy and gone to web only.  Consequently - after 15 years, I have not renewed my subscription :-(

mx
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
well, I never got further than the trial subscription.
But I still have those copies on the shelf somewhere ...

/gustav

Author

Commented:
Thanks all for answering. Unfortunately, the unique keys are autonumbered so your suggestions won't work.

So I guess I really need three queries:
- An update-query to update matching records
- A non-matching-records-query as input for an  append-query
A bit cumbersome, but it will do.

On a side note, shouldn't the advice be never to use autonumbering? They are utterly useless if you try to reconcile or merge tables.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"On a side note, shouldn't the advice be never to use autonumbering?"

On the contrary. The are generally the only unique Primary Key available.  Of course, there are issues if you are doing a lot of importing / merging.

mx
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, try. I've never heard of that Autonumber limitation.

/gustav

Author

Commented:
I stand corrected, Gustav. Your solution works like a charm.
thanks,
Michiel
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great. You are welcome!

/gustav
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Funny ... I just receive yet ANOTHER email from Smart Access (aka Inside MS Access)  to renew ... gee for only $112!

"  This opportunity is not for everyone. However, many of our subscribers have taken advantage of this special offer in the past. They have found it to be a great way to save money. -- and we just wanted to give you the opportunity to take advantage of a lower price."

Translation:

We are  offering this to almost everyone, cuz almost everyone has dropped their subscription since we went all web based, and we are now getting really desperate !!!

mx
hi gustav! i tried implementing this fantastic outer join solution to insert or update but then i got the message "Cannot join on Memo, OLE, or Hyperlink Objects"

2 of my fields are memo fields.

so whilst this is a SUPERB tip! if anyone has memo fields they need to update or insert as a matching criteria i don't think it's going to work. i'll be more than happy to be corrected! = )

alternative
1) for a more complex solution using this outer join, generate a matching ID by trimming the memo field first to 255 and then comparing.
2) 2 step update then insert (but this still faces the memo outer join problem)
3) use recordset

thanks gustav!! = ))

Author

Commented:
Hi

There are more limitations to memo-fields in queries. They cannot be grouped and it is not possible to sort them. If I'm not mistaken this is due to the fact that the actual content of a memo field is stored separately from the table.

Trimming the field to 255 characters is by no means fool proof and on larger tables probably too slow. Personally I would create a CRC for each memo field and store it in an indexed field of type long.

regards,
Michiel
Yup they have many limitations but I don't know if it's cause they're stored on a separate table. I do know though that they are only processed to 255 characters because joining / comparing / whichever other process and repeated for every single record in a query join might take days even - that's what I read from Allen Browne's website = )
 
Sorry Michiel, what's a CRC? Class Responsibility Collaborator?

So how have you gone about it in the end? I'm using recordsets. Am fixing my code now, will post soon when it's done = )

Author

Commented:
CRC stands for "Cyclic redundancy check" and is an error-detecting code to detect changes to data. So basically you calculate a unique value based on the entire memo field and use that value as outer join. The chances of two different memo fields resulting in the same CRC-value are extremely small and using a numeric value as outer join will beat a string value any time.

If data integrity is really crucial you should probably use MD5 instead of CRC, but for your purposes it really should be enough. Below you will find the function I use to calculate 32-bits CRC:

Hope this helps.
regards,
Michiel
'---------------------------------------------------------------------------------------
' Procedure : CalculateCRC32
'             modified from code downloaded from www.hardandsoftware.net
'             provided by Richard L. Grier
'---------------------------------------------------------------------------------------
'
Public Function CalculateCRC32(str As String) As Long
Dim i As Long
Dim J As Long
Dim Limit As Long
Dim CRC As Long
Dim Temp1 As Long
Dim Temp2 As Long
Dim CRCTable(0 To 255) As Long
 
  Limit = &HEDB88320
  For i = 0 To 255
    CRC = i
    For J = 8 To 1 Step -1
      If CRC < 0 Then
        Temp1 = CRC And &H7FFFFFFF
        Temp1 = Temp1 \ 2
        Temp1 = Temp1 Or &H40000000
      Else
        Temp1 = CRC \ 2
      End If
      If CRC And 1 Then
        CRC = Temp1 Xor Limit
      Else
        CRC = Temp1
      End If
    Next J
    CRCTable(i) = CRC
  Next i
  Limit = Len(str) 'UBound(ByteArray)
  CRC = -1
  For i = 1 To Limit
    If CRC < 0 Then
      Temp1 = CRC And &H7FFFFFFF
      Temp1 = Temp1 \ 256
      Temp1 = (Temp1 Or &H800000) And &HFFFFFF
    Else
      Temp1 = (CRC \ 256) And &HFFFFFF
    End If
    Temp2 = Asc(Mid(str, i, 1)) ' ByteArray(I)   ' get the byte
    Temp2 = CRCTable((CRC Xor Temp2) And &HFF)
    CRC = Temp1 Xor Temp2
  Next i
  CRC = CRC Xor &HFFFFFFFF
  CalculateCRC32 = CRC
End Function
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Instead of using the tables directly, create a simple select query for each where you add a column of the memo fields: Left([YourMemoField], 255).

Then compare the source and target records using that expression. Right() can be used as well.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial