Link to home
Start Free TrialLog in
Avatar of altiplano
altiplano

asked on

Combined "update or Append" query

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
Avatar of Natchiket
Natchiket
Flag of United Kingdom of Great Britain and Northern Ireland image

It can be done, under certain circumstances.  check out this link
https://www.experts-exchange.com/questions/23455292/Access-DB-update-and-insert.html
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
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
well, I never got further than the trial subscription.
But I still have those copies on the shelf somewhere ...

/gustav
Avatar of altiplano
altiplano

ASKER

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.
"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
Well, try. I've never heard of that Autonumber limitation.

/gustav
I stand corrected, Gustav. Your solution works like a charm.
thanks,
Michiel
Great. You are welcome!

/gustav
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!! = ))
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 = )
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
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