Splitting the table(s): I can't tell from a screenshot where your subform(s) start and end unless it's by the border around each section but in terms of a data structure, you don't need a separate table for people you can contain them all in a single table with a simple indicator field for employee or customer contact, then use criteria in your combo boxes to appropriately show the group of people you want.
Similarly, it looks like you have four subforms (if I go by the borders) for stage 1, 2, 3 and Other. If these are all on separate tables you can use the same method. Put all the 'stages' in the same table, just put a stage number in the table. If you then intend to keep this data entry format you can divide the subforms by the 'stage' number.
Data Input: Removing duplicates is a tricky thing. First, you have to identify which fields will constitute a 'duplicate', is it one field, is it two more? Second, you have to have a way to select which value, when a duplicate is detected, is the 'correct' one before deleting anything. You specifically mention the project number being a problem so I am going to guess that your project is being stored as a text description rather than the foreign key to the project table which is a huge no-no (look at normalization articles), theoretically if your data is clean (the descriptions have not been manually altered, nor has any project been deleted) you could match the text value to the project table and substitute in the project ID primary key
for instance
Code:
tblProject
Proj_ID Proj_Name
1 Project A
2 Project B
tblProjectDetails
PD_ID Proj_Name Stage
1 Project A 1
2 Project A 2
3 Project A 3
In this example you could create another field in tblProjectDetails named Proj_ID then run an update query by looking up the project ID from tblProject by linking the project_name field in tblprojectdetails to the proj_name field in tblproject. Once you've verified the results then you can consider deleting the proj_name field or just leaving it there for posterity
Organization:
Depends on your goals
If you do business with many organizations and each organization has many locations, and each location has many employees, and employees can work at many locations this would be the most complex to organize but it would typically be with something like
Code:
tblPeopleType
PT_ID PT_Description
1 Employee
2 Contact
tblPeople
P_ID PT_ID P_FirstName P_LastName ---> other person related fields
1 2 Mickey Mouse
tblOrganization
O_ID O_Name ----> other organization related fields
1 Company A
2 Company B
tblOrganizationLocation
OL_ID O_ID OL_Address OL_PostalCode ----> other organization location related fields
1 1 1 First St
2 1 2 Second St
3 1 4 Thrid St
tblOrganizationLocationPeople
OLP_ID OL_ID P_ID ---> other oganization/person related fields
1 1 1
Windows 10 
Access 2016
Redesigning a very old, very inefficient database
Reply With Quote
Originally Posted by Ajax
