Merging table with no IDs into table with IDs

[BigBro]

Greetings, n00b here.

I've inherited an Excel data system with innumerable record duplicates from one (monthly) file to another. To complete my transformation to an Access mdb, I need to grab attendance data for members from past months' Excel files and merge that data with the new Access file that contains all the current members but only the current attendance data. The problem is, the records in the Excel file have no record ID or inconsistent IDs, just FullName fields.

What I think I need is some SQL to merge records with FullName fields --- but no MID --- into records (tables) that have FullName fields AND MIDs.

TIA. And yes, I've done a cursory search of the archives before posting here.

 

[evanscamman]

Here's a thought:

Copy your Access table with Employee Names and IDs into your excel file.
Sort everything by Employee Name.
Copy and paste the IDs from the single record that has the ID to all the others.

Paste the results back into your attendance table.
This will probably be just as fast as any VBA solution you could code.

Evan

 

[BigBro]

Thanks for the suggestion, Sir. The problem with C&P is that the process has to be repeated almost 40 times --- once for each previous Excel monthly file that I'm trying to import. And each Excel file has a different list of members (50 or so new members added each month).

Here's the two file structures:

Access tblMembers
MemberID
Fullname (concatenated F & L names)
*

Excel file fields
Fullname
AttendanceWeek1
AttendanceWeek2
AttendanceWeek3
AttendanceWeek4

It seems so elementary. Maybe an update query with an IIf criteria...

 

[BigBro]

Shameless bump for the Monday crowd.

 

[evanscamman]

Why don't you copy all 40 excel files into 1 file. Then do the process 1 time?

I still think you could get it done much faster than coding a VBA automated solution.

Evan

 

[BigBro]

This is a membership organization that started small. It went from tracking member attendance on paper to tracking via Excel. Every week for over forty months new members were added to the spreadsheets (but never removed). The only unique field in each record was Member Fullnmame (no MemberID). At the start of a new month, each file was saved as May_06.xls, June_06.xls, ......

The latest XLS file contains records for all members but, like all the previous monthly files only contains attendance data for the current month. In other words, the attendance data that I need to populate my Access mdb table...

tblMemberAttend
MeetingID
MemberID
AttendanceWeek1
AttendanceWeek2
AttendanceWeek3
AttendanceWeek4

...comes from forty separate xls files where the Fullname fields never match one-to-one.

Instead of starting with the first xls file and incrementally adding attendance data for new members (starting from the oldest data and moving forward), I hoped I could start with the newest data and move backward so at least we have data to examine more quickly.

Like I said earlier, I was hoping to develop a simple tool like an update query that would assign the MemberIDs that my cumulative mdb file contains to each imported (no MID) xls file. So far, no joy.

Thanks for your attention. I know I'm going to be embarrassed by the simplicity of the obvious answer when I learn it, but as a newbie I can live with that.

 

[evanscamman]

OK, here's another approach:

Create a tempory table in Access with the same fields as your Excel files, and copy and paste all 40 files into your table.

Then create your permanent tables:

tblEmployee - EmployeeID, FirstName, LastName
tblMeeting - MeetingID, MeetingTime, etc...
tblAttendance - MeetingID, EmployeeID

tblAttendance has a field for MeetingID and EmployeeID with 1 record for each employee each time they attend a meeting.

Then populate your Employee Table With EVERY employee listed in your temp table - including First and Last Name (which should be stored in separate fields).

Populate your Meeting table with every meeting that took place.

Then build an update query to populate tblAttendance.

You will have to start with a separate query to build your Full Name:

Select EmployeeID,[FirstName] & " " & [LastName] as FullName From tblEmployee

Save this as qryFullName.
Then make your update query:

Join tblTemp to qryFullName on the FullName Field.
Update the EmployeeID from qryFullName into EmployeeID in tblAttendance.

This takes care of EmployeeID, but not MeetingID.
I don't know what field you have that uniquely identifies each meeting in your temp table - perhaps MeetingTime can be used the same way as the FullName to provide a link.

This is a rough sketch, but hopefully enough to work with.

Evan