Linking Imported Table to Table already in Database

[Keekee]

First I just want to say that I have been working in Access for a while, but not good with the VBA code part. With that being said, I have tried several ways to connect an imported table (imported from excel) to the Main table without any luck.

My Current database has a many to many relationship. One main table is "Personnel". One main table is "Projects". The junction table is "Details".

My Main form is the "Projects" form with the subform being "Details". In the details subform I have an "Employee" field as a lookup to the "Personnel" table.



The data I import is hours worked for each person and each project. After I import it I call that table "Actuals".

I can't figure out how to link the Actuals table to any other table. The only common field is "Employee", but I get a data mismatch since I am using the Personnel.Employee as a lookup.

I thought about adding a the Personnel foreign key to the actuals table, but could not figure out how to populate that field for each record in that table. Is this even doable?

What is the best way to link the imported table of actuals so I can get how hours each person worked for each project?

[June7]

So Employee field in Actuals has what data - employee name? Names are very poor unique identifiers.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Keekee]

Yes, employee name. That is the only unique identifier in the imported table unless I could add a field and assign it the EmployeeID from the Employee table so I could link the two, but I don't know how to do that other then manually. Do you know if that is a possibility? I will look at your link. Thanks.

[June7]

Yes, build query that joins Actuals to Personnel on name. Switch to UPDATE. Set field in Actuals to update to personnel ID field.

[Keekee]

I can't join Actuals to Personnel on name because the junction table is using the name in the Personnel table as a lookup, therefore the there ends up being a data mismatch when I try to join Actions to Personnel with name. Any other ways to do it? I was just thinking about copying the Personnel list and Primary Key into the Excel sheet on a different worksheet and doing a lookup and having he Personnel Key populate the table before I import it. But I would have to do that every time as the both tables grow.

[June7]

Sorry, but that does not make sense. Personnel table should have at least one field for person name. And if Actuals has person names, joining on the two should be simple.

You need to provide data for analysis.

[orange]

Show us your table relationships (relationship window)--extend all tables so readers can see all fields.
As June suggested, a copy of your database for review would be helpful --only enough records to show your requirements in context.