For example, if we had a main table that was say a list of staff members. There was a field in it called Department and you wanted the field to become a lookup to the table called tbl_Department
In order to get both tables exported to SharePoint when you export the main staff table, here is what I had to follow for it to work, else I found it either:
1. Would not export and link the lookup table
2. I would loose the data in the Department column in the main staff table.
Firstly, make all of the values in the Department column matches a lookup record in the tbl_department table, Else this can cause a lost of data. This includes trailing spaces in the data.
Secondly, on the lookup table there needs to be a primary key ID auto increment field if there is one. This is crucial for a link to be created so that it will export the table also to SharePoint with the main table. However, if there is already data in the table, this seems to always cause a loss of data on the column in the main table after the link is created. In order to cater for this, copy the table into say Excel, after the link is created and removed the data, use the Excel copy and re-paste the data back into the table.
Then, in the main staff table's design view choose "Lookup Wizard..." on the field that will become a lookup
Then choose first option click Next
Select the correct table. In this case Departments. Press Next.
Select the Department field and click Next
Select the Department field to list out for the field in the main staff table. Hit Next.
The values are listed. Hit Next.
Hit Finish and the link gets created.
Click Yes to save.
Click Yes as it will now link the table. It is highly likely there will then be some data lost while the link is created. This is why it is recommended a copy of the table be made in Excel before this operation.
Save the staff table.
Now open the staff table again you should see the Department field now has a drop down box that can be invoked and is linked to the tbl_department table. Although most data may be gone and left blank.
Time to copy the column that has been saved in Excel back into the Access table. If all the values in the lookup table match properly, it should just paste back in without any problems.
Export the staff table to SharePoint and voila you will find its exported and the tbl_Department table gets taken with it and you have both lists created with a lookup field made.
