Merging data without a consistent PK

[brian]

Basically what I would like to do is read each row from an Excel file and
using one of the columns in Excel, see if that value is present in an Access
Table column. If it is in Access, then update some fields in Access with
what is in Excel. The column "linked" to in Access may or may not have a
value entered in it so it isn't a Key and the Excel file doesn't have a
column with a corresponding Key in Access (AutoNumber field in Access).

Thanks

 

[Jeff Boyce]

Brian

I'm not sure I entirely followed that...

Unless you have a common field to use to indicate which record in an Access
table is to be updated by which row from an Excel spreadsheet, how would you
do the update? Forget about Access for a second, think about how you'd
explain it to someone who was going to do this manually...

One approach to consider would be to link to the Excel data and use a query
to select the rows that will be used to do the updating, and the table
records that will be updated.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[gllincoln]

Hi Brian,

Linking the Excel sheet would make it easier - although it could be done
from the Excel.Application object.

Assuming you have linked to the Excel sheet - we'll call that one tblLink,
the other one we'll call tblTarget
We'll call the source column SourceCol, the lookup in Access we'll call
LookupCol.
Here is the way I'd do it - substitute your column/field names and table
names as necessary.


Dim rsLink As DAO.Recordset
Dim rsTarget As DAO.Recordset

Set rsLink = CurrentDb.OpenRecordset("tblLink")
Set rsTarget = CurrentDb.OpenRecordset("tblTarget")

Do While Not rsLink.EOF

' We need to use a single quote (') to delimit text or # to delimit a
date criteria
'Numbers do not requre a delimiter - this example is for text
rsTarget.FindFirst "LookupCol= '" & rsLink.Fields("SourceCol") & "'"
If Not rsTarget.NoMatch Then
With rsTarget
.Edit
!FirstUpdateField = rsLink!FirstSourceCol
!SecondUpdateField = rsLink!SecondSourceCol
'however many you need to update
.Update
End With
End If
rsLink.MoveNext

Another assumption the code makes is that we will only have one match - in
any event, we are only grabbing the first one.
If multiple matches are an issue then I would use rsTarget.FindNext and
repeat until no more matches were found.

Hope this helps,
Gordon

 

[brian]

Thank you for your response. Sorry the description wasn't quite clear
enough. I was able to find a solution by using an Append Query and and
Update Query an importing the Excel spreadsheet into a new table. This is
what each looks like:

INSERT INTO Attendees ( AttendeeLastName, AttendeeFirstName, PhysID,
CernerID )
SELECT PHYS_IMPORT.LAST_NAME, PHYS_IMPORT.FIRST_NAME,
PHYS_IMPORT.COMMUNITYDRNBR, PHYS_IMPORT.USERNAME
FROM PHYS_IMPORT LEFT JOIN Attendees ON PHYS_IMPORT.USERNAME =
Attendees.CernerID
WHERE (((Attendees.CernerID) Is Null));

************ AND ***********

UPDATE PHYS_IMPORT INNER JOIN Attendees ON
PHYS_IMPORT.USERNAME=Attendees.CernerID SET Attendees.AttendeeLastName =
phys_import.last_name, Attendees.AttendeeFirstName = phys_import.first_name,
Attendees.PhysID = phys_import.communitydrnbr, Attendees.CernerID =
phys_import.username;

 

[Stuart]

Gordon - OK so I am totaly new at all this, and for the first time asking for
help. I wanted to try this out I'm missing the first part to this code. How
do I start this example out?