Preventing Repeated Infromation in a Database.......

[Guest]

I have created a table "tblParts" with the following fields...
ID, Model Number, Reference, Description, Type, Part Number, Quantity,
Document.
I have created a form "frmInputPartDetails" to put the information into the
table.
The Model Numbers can be repeated randomly for each record put in.
Different models can use the same Part Number.

My Questions:-
1. How do I prevent the repetiton of Model Numbers and Part Numbers in the
table to maintain the database as small as possible.
2. Am I correct to say I should only need to enter each Model Number and
Part number once in the data base. If other models use the same part number,
it should access the entry that is already present?.....I just can't seem to
work this out?

I have read several books explaining splitting the tables and using a lookup
table, but this isn't suitable because when the data is input via the form if
a Model is entered that is not on the table and error is generated stating
"Enter information that is on the list" (or something like that).

Please save me from myself....and let me know how to do this before it drive
me mad!!!!

Thanks
Dermot

 

[Brian]

I have created a table "tblParts" with the following fields...
ID, Model Number, Reference, Description, Type, Part Number, Quantity,
Document.
I have created a form "frmInputPartDetails" to put the information into the
table.
The Model Numbers can be repeated randomly for each record put in.
Different models can use the same Part Number.

My Questions:-
1. How do I prevent the repetiton of Model Numbers and Part Numbers in the
table to maintain the database as small as possible.
2. Am I correct to say I should only need to enter each Model Number and
Part number once in the data base. If other models use the same part number,
it should access the entry that is already present?.....I just can't seem to
work this out?

I have read several books explaining splitting the tables and using a lookup
table, but this isn't suitable because when the data is input via the form if
a Model is entered that is not on the table and error is generated stating
"Enter information that is on the list" (or something like that).

Please save me from myself....and let me know how to do this before it drive
me mad!!!!

Thanks
Dermot

I'm not entirely sure I understand you, but I'm thinking that, for a start,
you actually need three tables:

tblModels
=======
Model Number (Primary Key)

tblParts
=====
Part Number (Primary Key)

tblPartsInModels
============
Model Number (Primary Key)
Part Number (Primary Key)
Quantity

To deal with the "The text you entered isn't an item in the list" error, you
have two options: (1) train your users to enter a new record in tblModels or
tblParts as appropriate or (2) code the OnNotInList event to get the details
of the new model or part. There's an example here:

http://www.mvps.org/access/forms/frm0015.htm

I'm afraid that what you seem to want i.e. entering pre-existing model or
part details WITHOUT using lookup tables, is a corner that simply can't be
cut.

 

[Guest]

Hi Brian,
Thanks for your reply....and great link.
I am trying to create a database for parts that I use.
At the moment I have one table, to which I enter the fields Model Number
etc....
I input the information from a form I created from the table.
I enter the information after every job that I do.......
When I look at the table now, I find that I have many records for the same
models and different part numbers.
I also have many models that can use the same part numbers.
I realise that this one table database is not efficient..... I created it as
a starting point to developing a useful database.
I have read a few books explaining splitting the table and using
relationships, but am finding this tricky to understand. I thought using
relationships meant that everytime a model number or part number was entered
it would not be seen in the table more than once....
As I am a beginner to access.....can you clarify my understanding?

 

[Brian]

Hi Brian,
Thanks for your reply....and great link.
I am trying to create a database for parts that I use.
At the moment I have one table, to which I enter the fields Model Number
etc....
I input the information from a form I created from the table.
I enter the information after every job that I do.......
When I look at the table now, I find that I have many records for the same
models and different part numbers.
I also have many models that can use the same part numbers.
I realise that this one table database is not efficient..... I created it as
a starting point to developing a useful database.
I have read a few books explaining splitting the table and using
relationships, but am finding this tricky to understand. I thought using
relationships meant that everytime a model number or part number was entered
it would not be seen in the table more than once....
As I am a beginner to access.....can you clarify my understanding?

Your question isn't really about Access, it's a general question about data
design and would be the same with any database engine.

If we talk about it in non-technical terms, what you need, in an ideal
world, is three things:

- a list of all the models
- a list of all the parts
- a list which parts are actually used in which models.

The list of models would include, not only the model number (the "primary
key") for each model, but also a name/description for it, and any other
information characteristic of each model.

The list of parts would include, not only the part number (again, the
primary key), but a name/description etc. for each part.

The list of parts actually being used would include the model number (a
"foreign key"), the part number (another foreign key), and any information
specific to the actual use of the part (e.g. the quantity used).

Doing it this way gives you several massive advantages over having a single
table, such as:

- Ancillary information for each part and model (e.g. names/descriptions)
is stored only once. If you have just one table, you will have to repeat
this information on every record.
- When a part number or model number is entered on a "part being used"
record, you have a list against which to look it up. Thus, you will know
whether you have entered a valid part or model number. Even if it's a
genuinely new part and needs to be added to the list, you will at least get
the opportunity to understand that and to be sure that you haven't merely
mis-typed the part number. With just one table, you could mis-type a part
number and you would never know.

It might help you to study the Northwind sample database that ships with
Access. It isn't the greatest system ever, but there is value in it. It
would be a good idea to look at the relationships window (from the "Tools"
menu), and try to understand how those relationships affect the way the
system actually works.

Hope that helps.

 

[Guest]

Thanks again for a great reply,
One last question.....
I understand , 1 to 1, one to many and many to many relationships fro a book
I have purchased.
I have looked at a few data bases as you suggested "Northwind".
Question:-
What sort of relationship is meant when neither a 1 or infinity symbol is
indicated on the link....the link just being left blank at one end?
thanks
Dermot

 

[Brian]

Thanks again for a great reply,
One last question.....
I understand , 1 to 1, one to many and many to many relationships fro a book
I have purchased.
I have looked at a few data bases as you suggested "Northwind".
Question:-
What sort of relationship is meant when neither a 1 or infinity symbol is
indicated on the link....the link just being left blank at one end?
thanks
Dermot

Hi Dermot,

In a one-to-many relationship, the field on the "one" side is normally the
primary key on it's table. If you create a relationship where neither field
is the primary key, Access cannot determine which table is on the "one"
side, and which is on the "many" side, hence it just displays a plain line.
This probably means that there is something wrong with your database design,
most likely you've simply forgottent to identify the primary key on one of
the tables.

 

[Guest]

Hi Brian,
The database I was thinking about may be an unfinished database that someone
gave me, so your explanation make sense to me now.
Thanks for your help
Dermot