Preventing Repeated Information In Database Tables.......

[Guest]

I have created a table "tblParts" with the followin fields...
ID, Model Number, Reference, Description, Type, Part Number, Quantity,
Document.
I have created a form "frmInputPartsDetails"....to enter the information
into the table.
The Model Numbers can be repeated with each record.
Some Part Numbers can be used with Model Numbers.

My Question
1. Am I correct to say that I should be able to enter each Model Number and
Part Number once and if this this information is entered again it will be
referenced rather than duplicated in the table?
2. How do I do this....I have read about splitting the table and
relationships but I can't get the results I expected?
Lookup tables don't seem correct either as not all the models and part
numbers are known at the time the database is being created.

Please Help
Thanks
Dermot

 

[Jack MacDonald]

Not quite correct. In order to see why, you must step back and look at
the bigger picture.

What are the parts to be used for? Perhaps you are tracking their
sales, perhaps you are tracking their use in mechanical repairs, etc.
From your description, I can't tell... I am going to presume they are
being used for mechanical repair.

Therefore, each repair job would be recorded in a separate table, say,
tblRepairJob. Then you would have a *third* table (say,
tblPartsUsedForJob) that recorded the parts used for each repair job.
It is in *that* table where you reap the benefit of not duplicating
information. Simply by entering the partID, you would gain the benefit
of all the information stored about the part in your original table.

So, as far as you have gone with the single table, you won't see any
particular benefit, EXCEPT after you apply a "unique index" to PREVENT
duplicate part numbers from being entered into the table.

BTW - as a matter of practice, I never include spaces with field
names. Although Access *does* allow spaces, omitting them will make
your life easier as your database develops.

Lookup tables (or more accurately, tables defined with lookup fields)
are best to be avoided. Instead, use separate, but related tables. I
suggest you purchase a beginner's Access book to learn about the
concepts behind relational database design.



HTH

I have created a table "tblParts" with the followin fields...
ID, Model Number, Reference, Description, Type, Part Number, Quantity,
Document.
I have created a form "frmInputPartsDetails"....to enter the information
into the table.
The Model Numbers can be repeated with each record.
Some Part Numbers can be used with Model Numbers.

My Question
1. Am I correct to say that I should be able to enter each Model Number and
Part Number once and if this this information is entered again it will be
referenced rather than duplicated in the table?
2. How do I do this....I have read about splitting the table and
relationships but I can't get the results I expected?
Lookup tables don't seem correct either as not all the models and part
numbers are known at the time the database is being created.

Please Help
Thanks
Dermot

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security