Assign unique identier

[dslaugh]

I have two tables linked into a new access database from Excel spreadsheets residing in network folders. They are, for all practical purposes, updated with a new report with a different file name every day -- obviously with the new information.


One has HR data with names. The other has items for which people take ownership (e.g. property).
The tables use different formats to identify the people


One table has the property as the unique item, and the other has the people as the unique item.


I think creating a separate table that links the person's unique identifier from the property table with the person name HR record is probably the best way to move forward. Some people own several items, some own none. I but am unsure how to proceed.




Any help you can provide would be greatly appreciated.

[ranman256]

not a great idea. you should keep everything in access, then all names of owners are not spelled differently in different spreadsheets.

Autonum is a great auto identifier.
You can import unique people once then look them up if you still use XL files.
you can also select a file to import, click import,
this would save that file to the same generic file name everytime, c:\temp\File2Import.xls
which would be linked, and then imported via append query.

[dslaugh]

Thank you, unfortunately, I don't have much say so as to how the reports originate. Also, the names come the way the come. I also don't have much control over that.
The only thing I can do is manipulate the data as it comes.

I can import the tables. I just want to match up the identifier in sheet two with the person in sheet 1, and they are given to me in different formats.

[GinaWhipp]

Doesn't the one with Property have the employee's unique identifier? That would be the way to link these two files.

[dslaugh]

No, they don't originate from the same data base. Sometimes it's "Smith, Robert C." in HR an "SMITH DEPARTMENTNAME ROB RSMITH" in the other.

[GinaWhipp]

Geez, that is a shame. When then I would have a table that stores the names both ways and use said table to join those two files.