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
altiplanoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NatchiketCommented:
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 / Systems AnalystCommented:
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
Gustav BrockCIOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
Gustav BrockCIOCommented:
well, I never got further than the trial subscription.
But I still have those copies on the shelf somewhere ...

/gustav
altiplanoAuthor 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 / Systems AnalystCommented:
"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
Gustav BrockCIOCommented:
Well, try. I've never heard of that Autonumber limitation.

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

/gustav
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
developingprogrammerCommented:
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!! = ))
altiplanoAuthor 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
developingprogrammerCommented:
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 = )
altiplanoAuthor 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
Gustav BrockCIOCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.