Variable Table

I am working on designing a database that will be used to audit data through
a form. The data we receive will come from unique sources and will have
similar, but not always similar, field names and field types. I know I could
just design tables that are specifically designed to receive the data with
the field names already defined. However, I would like for the database to
be able to evolve and receive new types of data as we receive it. So my
question, what would be the best way to go about having a generic table with
each field defined as Var1, Var2, etc, and then, depending upon criteria
(perhaps a three letter code identifying the source of data), map field names
onto this generic table, so that I'm only pulling data from a single table
instead of multiple tables based on multiple data sources.

This sounds like a nightmare.

Assuming you can design one table to handle the diverse sources, you can
import into that table by setting up import specifications.

Or you can link to the data and construct SQL append queries to append
the data to the table.

However, you handle this could be a major project.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


Decembersonata wrote:
I am working on designing a database that will be used to audit data through
a form. The data we receive will come from unique sources and will have
similar, but not always similar, field names and field types. I know I could
just design tables that are specifically designed to receive the data with
the field names already defined. However, I would like for the database to
be able to evolve and receive new types of data as we receive it. So my
question, what would be the best way to go about having a generic table with
each field defined as Var1, Var2, etc, and then, depending upon criteria
(perhaps a three letter code identifying the source of data), map field names
onto this generic table, so that I'm only pulling data from a single table
instead of multiple tables based on multiple data sources.