Showing data from linked table and local table

[soccerkingpilot]

Hey guys,
I'm having trouble with a new project I'm working on. The application is mainly going to be used to display data, which comes from a linked table. It has to be a linked table (in my opinion) because it's replaced once per week from a fresh data dump. For each of those records, though, there will be notes made in a local table named "Custom-Data". My trouble is displaying a mix of information from the linked table, "Roster", and "Custom-Data" because linked tables can't be assigned a primary key.

Essentially, when a record is pulled up, a bunch of data from "Roster" will be shown in addition to the comments from "Custom-Data".

Any ideas are greatly appreciated...I've tried a few methods with no success.

 

[David R]

You need to build a query that combines the two tables into one 'virtual' table. Access does not in general give a single fig about where the data is coming from, local or linked (though some linked will be nonupdateable, such as Excel spreadsheets).

What unique field in [Roster] links those entries to the notes in [Custom-Data]? I know you said there couldn't be a linked table PK, but something has to link the two, right? That's where you want to join the two tables in query view... in additional I'll bet you want an OUTER JOIN. Double-click the line you draw between the two tables, and change it to the second or third option that says "Include all records from [Roster] and any records from [Custom-Data] that match it."

Your SQL will end up looking something like:

SELECT [Roster].[OBJECTID], [Roster].[OBJNAME], [Custom-Data].[Notes]
FROM [Custom-Data] RIGHT JOIN [Roster] ON [Custom-Data].[OBJECTID] = [Roster].[OBJECTID];

The only thing important here is whether you can rename your linked table to the same name when it gets updated (in other words, the old one gets blown away or renamed/moved, and the new one gets moved into \\unc\Data\Roster.xlsx). If so, your linked table in Access will work in perpetuity. If not (the data comes in each week as \\unc\Data\Roster20130522.xlsx, etc), then you'll have to have some way of telling Access to relink to the new file. That can be done manually or via code, but we'll need more information.

 

[soccerkingpilot]

Hi David,
Thanks for the help. I ended up making it work a different way...I converted the Excel linked files into local tables and created a macro that updates them from the database dump files.