One more thought. If you want to pull records from multiple tables to one
Query, you can do so by creating a UNION query. You would not save the
results to yet another table. That would further unnormalize your data.
Anything you can do with a table can also be done with a query.
Just curious, how are you creating forms, queries, and reports since you
have four tables? Are you having to create four reports each time, four
forms, four queries, etc? That is why you normalize. If you normalize,
then you can create ONE report, and ask the user to pick the "type" of data
to include.
You'd have to let us know what makes the four tables different.
If one table is "widget" vendors, and one is "gizmo" vendors, and one is
"thingamajig" vendors, then you need to have one table with a new "Product"
field. In the "Product" field you'd select "widget, gizmo, or thingamajig
for each vendor. Then, when you open a form, you can select from a
drop-down that you only want to see "widget" vendors. When you run reports,
you can tell it what kind of vendors you want.
When you build the append queries I mentioned, you need to have that
"Product" table built first. In the "widget" table, update the Product
field for all the records to "widget". In the gizmo table, update the
Product field for all the records to "gizmo", then build your append queries
to move all the records to the new consolidated tables (including the
product field). Once you have done all that, you can delete the other four
tables.
This is true if you have separate tables for each "representative" or a
separate table for each "office" or a separate table for each "company",
etc. In short, you do not need multiple identical tables.
Hope that helps, as I am heading out for the day.
