So my current assignment is to transfer an excel spreadsheet that was being used as a database in to an actual database using Access. I do know Access but have been trying to learn as much as possible. Originally I just transfered the excel spreadsheet in to Access and was going to keep everything on one table but that is proving to not be efficient. I am trying to seperate the information in to multiple tables but my knowledge base is not quite there. I am just looking for some suggestions in designing my database, below I put was is on the current table and how I am thinking of splitting it up as well restrictions and so on.
The database is going to be used to keep track of companies who are submitting corrective letters to us and it often takes multiple submissions before they are acceptable. Also I would like to use this database for mail merges.
On the original table I have the default ID field, company name, doing business as, their company number, the received date of their letter, the date their letter was scanned in to the computer, whether or not it fits in to a two different catgories, the revision date on their letter, the date we mailed a letter back to them, which submission they are on and the status of their submission, address of company.
I have now split this up in to 4 tables.
1st table: Company information- name, doing business as, company number, address
2nd table: 1st submission- received date, date scanned, whether or not it fits in to two different catergories, revision date, date we mailed letter back, status of submission
3rd table(only for companies who have an unacceptable submission): 2nd submission- received date, date scanned, whether or not it fits in two different categories, revision date, date we mailed letter back, status of submission
This can continue for up to four submissions so 5 tables.
In addition, in the first table I obviously do not want any duplicated but the problem is that in the second, third, fourth and so on tables there can be duplicated because sometimes companies will have to go through this submission process again in the future.
My biggest goal is ease of data entry and look up. Just looking for some tips, and pointers.
Also I am still having a trouble grasping the idea of primary keys/secondary keys/relationships
THANKS!!!!!!!!!!!!!