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
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
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
You might be able to accomplish that using VBA code, but the simplest approach is two queries ... Append and Update.
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
mx
well, I never got further than the trial subscription.
But I still have those copies on the shelf somewhere ...
/gustav
But I still have those copies on the shelf somewhere ...
/gustav
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.
So I guess I really need three queries:
- An update-query to update matching records
- A non-matching-records-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
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
/gustav
ASKER
I stand corrected, Gustav. Your solution works like a charm.
thanks,
Michiel
thanks,
Michiel
Great. You are welcome!
/gustav
/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
" 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!! = ))
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!! = ))
ASKER
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
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 = )
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 = )
ASKER
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
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
Then compare the source and target records using that expression. Right() can be used as well.
/gustav
https://www.experts-exchange.com/questions/23455292/Access-DB-update-and-insert.html