| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > New Project added to old
Expert: Scottgem - 11/6/2009
Question
QUESTION: Good morning Scott. I have a database that is complete in Access 2003. My boss wants to add a new category to it. This new project if you will, has a lot of the same information as the already existing database. Since I have not had a lot of training in Access, I'm not sure how to go about this. My initial thought is this. 1. Add to existing tables, making new tables as needed. 2. create a form to add the data. 3. Add this category to my main forms index. Or Should I create a brand new database with all new tables ect. If I do it this way, how would I get the same information from both databases on the same report?
We will be adding other products also, this is just the second one but I want to do this correctly. does this make sense?
ANSWER: You should never use tablenames (or even fieldnames) to define data. If your database had been designed using best practices initially, you wouldn't be asking this question. I'm not putting you down, by the way, just trying to explain the best way to handle this.
You should have a Projects table that contains information specific to a project. Each project record is then assigned a primary key. You then include that primary key as a foreign key in the related tables. If there is information that is unique to a specific type of project you can then add another table for each type of project just to store that specific info. For example, I built a database for a client that is an insurance broker. There is general data applicable to all policies, but there is also info specific to types of policies. So I have a Policy table and then tables for each type (i.e. Health, Dental, Diability etc.) that is related on a one to one relationship with the Policy table on PolicyID.
So, you see if you had created a Project table in the beginning adding a new project would be as simple as adding a new record in the Project table.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Ok, let me get this straight. I hope its not to late to add the project table. Add a table called EProject. Only project specific data that does not have a table already created goes into this project table. Example. Customer Name. I already have a Customer table so I can add this customer to that table. If I need to create another table or lut's for this project, they connect to the project table. If I Add another project down the road say to keep track of equipment for this customer. I create another project table and do the same things. Is this correct?
Answer
I suggest you read up on Normalization. Customer name should exist in ONE table and ONE table only, the Customer table. The customer table should have a primary key (not name) that would be used as a Foreign key in the Project table.
NO, you have ONE project table. it might look like this:
tblProject
ProjectID (PK autonumber)
CustomerID (FK)
BeginDate
CompletionDate
Description
There may be other fields necessary. Next if you add a project that tracks equipment and you have never tracked equipment before for another project, then you would add a new table like this:
tblEquipment
EquipmentID (PK Autonumber)
ProjectID (FK)
EquipmentTypeID (FK to lookup table for equipment types)
PurchaseDate
etc.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Add to this Answer Ask a Question
|
|
