Per request, here is a followup to a previous post.
To reiterate, I’m thinking through the best way to present a uniform UI atop varying data tables. I am reluctant to invest the time and effort into developing import routines for the data in order to apply full normalization to the data tables for reasons which are sufficient and not under discussion.
Therefore the choices are:
Can I present data in an Access UI where the display fields draw from more than one data datatable, using if-thens and string concatenation to present the data as appropriate to the record?
Must I create a supertable which unifies the schema without any attempt at normalization in order to do the same thing for each record?
The answer is partially embedded in Access’ limitations on tying tables to the UI, it looks like. When I experimented with relating two tables to one “Form” (Access’ term for the UI) Access required the tables to contain a join; the data is not inherently joinable. Therefore if I want to do if-thens and concatenation to conduct normalization as each record is displayed (which is conceptually what I’m talking about) I will need to create supertables. Yuck.
One of the issues with doing this is Access is that Access does not permit dynamic column population at import. Therefore I can’t test the inbound data for specific characteristics to record the data source in the new record. That’s part of the MS upgrade-path strategy which is so irritating – leaving out simple-to-implement features as the consumer-grade app is upgraded forces advanced users to look at MS-SQL, in this instance.
So what it looks like I will be doing is recoding Access applications for each datasource and requiring the user to switch between them as they work with the data, which kinda sucks. Oh well.