Field issues

[Switchwork]

Hello people. I have designed a database and have been using it since before xmas. However as it is my first database design I have noticed the odd thing that I could make better or things that I did not think about putting in to the database. I now want to make the database better but don't know if I should start designing a brand new database or try and change the old one. However I have been told that it is hard work to try and change a database that is already in use.

One of the main things I would like to change in this database is the Reference number fields. I have to enter a reference number given to me by the customer who has provided the job (Courier company). However alot of the time I get provided with numerous Reference numbers and have no way of entering then all in. What I want to know is can I just have one Reference number field in the main table and then use it a few times to set up boxes in a form. Or do i have to have Ref No 2 Ref No 3 Ref No 4 and so on to be able to do this.

AS my main table has over 25 fields in it I was thinking of having the main information need on a main form and then other relevant info like CODs PODs on a subform in a TAB style. Thanx


Switch

 

[Pat Hartman]

You have a 1-many relationship between customers and reference numbers. The proper way to store the reference numbers is with a separate table. This will allow you to associate as many reference numbers as you need to without further change. The reference talbe would look like:

tblReference:
CustomerID (primary key field 1)
ReferenceNum (primary key field 2)
any other fields associated with an individual reference number.

You would use a subform on the main form to enter/display references.