How to correct an incorrectly-setup db

[InDFW]

I am working with a database that was created weird and I am trying to fix it.

2 Main Issues:

1. Many tables, all of which contain some similar information (i.e. Product, Lot, StartDate). How can I create one separate, "live" table or query to list these 3 field from all tables? (By live, I mean I would like it to always be updated/accurate).

2. There is a main Product table that most of the tables link to, but some don't. The Product Table consists of Product (text) and ProductID (number). Since some tables are linked and some are not, trying to think of the best way to make this consistent. Any suggestions?

 

[InDFW]

I found how to create a Union Query, so first problem is solved.

As for the 2nd issue, I may just have to do it manually?

 

[gemma-the-husky]

i dont really understand

the inter-relation between tables is what makes a database work. The tables just need to be related in a sensible/logical manner - not necessarily every table to a single "master" table

 

[The_Doc_Man]

When designing a database that relates to your business, you are in effect (if not consciously) building a data-flow model. The relationships in your model will define whether a relationship should exist between tables. This is because when building a database for a business, the actual business model is always right. If two things are linked in the business model, they should be linked in the database. If they are not linked in the business model, they should be totally independent in the database.

Understanding your data comes first. Building your database comes second. Always. Therefore, we probably cannot answer your question directly. But if I got my point across, I just gave you a strong hint as to how to answer the question for yourself.