I have a SharePoint 2010 list that contains a lookup column with multiple fields (not multi-value but rather when I select item1 it will display item1's name and category). I have this list linked to an Access 2007 table and everything seems to work fine.
However, when some users access the data within Access, they don't get the category from the lookup item, but rather the item ID. In addition, I sometimes get some weird combinations such that if the category is "GS" I'll usually show GS, but sometime the category duplicated as "GS;#GS" or combined with the ID as "25;#GS".
Regardless, the lookup name always returns correctly so I know it's properly processing the lookup. The primary list only has about 100 entries and the lookup field list only has about 25.
I've tried refreshing the data, turning cache mode on and off again, re-linking the tables but no luck. Any help would be much appreciated.
I know I can "fix" the problem by re-building all the queries to get the category from the original list rather than using the multi-column lookup but for obvious reasons I'd like to avoid that option if I can.
