Inventory Control Template Problem

I created an Inventory Control Database using the template in Access 2000. My
only problem is that the products table does not meet my needs. I need to
replace the Products table with about five new tables. I want to start small
so for now I have replaced The Products table with two tables, one called
Drills, and one called Inserts.

First, how do I properly relate the two new tables to the Inventory
Transactions table?

If I can get that far I should be able to handle the rest.

Thanks

On Sun, 2 Dec 2007 14:14:01 -0800, rsm169
wrote:

I created an Inventory Control Database using the template in Access 2000. My
only problem is that the products table does not meet my needs. I need to
replace the Products table with about five new tables. I want to start small
so for now I have replaced The Products table with two tables, one called
Drills, and one called Inserts.

First, how do I properly relate the two new tables to the Inventory
Transactions table?

If I can get that far I should be able to handle the rest.

Thanks

Are the *structures* - fieldnames and field definitions - of these five tables
identical? If so, you're on the wrong track: you should indeed have only one
parts table, with perhaps another field to indicate PartType (drills, end
mills, inserts, ...)

If the five tables are in fact of different structure, you may have some
additional complexity. You cannot enforce relational integrity from one
Inventory Transactions table to (one of these five tables). What you may need
to do is have a single Parts table related one to many to Inventory
Transactions, and containing those fields that are in common among the five
types of parts; this table would be related one-to-one to each of the five
specific tables, each of which would contain fields particular to drills, or
inserts, or whatever. This technique is called "Subclassing" and is one of the
few cases where one to one relationships are appropriate.


John W. Vinson [MVP]

John,

Thank you for your help.

My file structures are very much different. I have never heard of
subclassing; it is now my new buzzword! I am going to research this topic and
see if I can make any head way.

Regards

"John W. Vinson" wrote:

On Sun, 2 Dec 2007 14:14:01 -0800, rsm169
wrote:

I created an Inventory Control Database using the template in Access 2000. My
only problem is that the products table does not meet my needs. I need to
replace the Products table with about five new tables. I want to start small
so for now I have replaced The Products table with two tables, one called
Drills, and one called Inserts.

First, how do I properly relate the two new tables to the Inventory
Transactions table?

If I can get that far I should be able to handle the rest.

Thanks

Are the *structures* - fieldnames and field definitions - of these five tables
identical? If so, you're on the wrong track: you should indeed have only one
parts table, with perhaps another field to indicate PartType (drills, end
mills, inserts, ...)

If the five tables are in fact of different structure, you may have some
additional complexity. You cannot enforce relational integrity from one
Inventory Transactions table to (one of these five tables). What you may need
to do is have a single Parts table related one to many to Inventory
Transactions, and containing those fields that are in common among the five
types of parts; this table would be related one-to-one to each of the five
specific tables, each of which would contain fields particular to drills, or
inserts, or whatever. This technique is called "Subclassing" and is one of the
few cases where one to one relationships are appropriate.


John W. Vinson [MVP]

John,
I hope you are still with me here. I tried to do some research on
subclassing and came up pretty dry. I typed it into Access's help and came up
with nothing. I got some info from the net but it was not very helpful to me.

From reading your post I created an Intermediate table called "Parts", it
will have a one to many relationship to the table Inventory Transactions and
a one to one relationship with the two subclass tables. Have I understood
this correctly?

InventoryTransActionsTBL
InventoryTransActionsID
PartsID

PartsTBL
PartsID
PartsName
DrillsID
InsertsID

DrillsTBL
DrillsID
Ect.

InsertsTBL
InsertsID
Ect.





"John W. Vinson" wrote:

On Sun, 2 Dec 2007 14:14:01 -0800, rsm169
wrote:

I created an Inventory Control Database using the template in Access 2000. My
only problem is that the products table does not meet my needs. I need to
replace the Products table with about five new tables. I want to start small
so for now I have replaced The Products table with two tables, one called
Drills, and one called Inserts.

First, how do I properly relate the two new tables to the Inventory
Transactions table?

If I can get that far I should be able to handle the rest.

Thanks

Are the *structures* - fieldnames and field definitions - of these five tables
identical? If so, you're on the wrong track: you should indeed have only one
parts table, with perhaps another field to indicate PartType (drills, end
mills, inserts, ...)

If the five tables are in fact of different structure, you may have some
additional complexity. You cannot enforce relational integrity from one
Inventory Transactions table to (one of these five tables). What you may need
to do is have a single Parts table related one to many to Inventory
Transactions, and containing those fields that are in common among the five
types of parts; this table would be related one-to-one to each of the five
specific tables, each of which would contain fields particular to drills, or
inserts, or whatever. This technique is called "Subclassing" and is one of the
few cases where one to one relationships are appropriate.


John W. Vinson [MVP]

On Tue, 4 Dec 2007 17:06:00 -0800, rsm169
wrote:

From reading your post I created an Intermediate table called "Parts", it
will have a one to many relationship to the table Inventory Transactions and
a one to one relationship with the two subclass tables. Have I understood
this correctly?

InventoryTransActionsTBL
InventoryTransActionsID
PartsID

PartsTBL
PartsID
PartsName
DrillsID
InsertsID

DrillsTBL
DrillsID
Ect.

InsertsTBL
InsertsID
Ect.


Not quite. A one to one relationship still has a parent and child. The parent
in this case is PartsTBL - its primary key should be PartsID, and it should
NOT contain a DrillsID or an InsertsID.

Instead, the "child" tables, DrillsTBL and InsertsTBL, should have the PartsID
as their primary key (and also as a foreign key to PartsTBL.

After all, every Drill is a Part; but not every Part is a Drill. So the Parts
table shouldn't contain any information specific to drills - only that
information which is pertinant to all kinds of parts.

The one exception might be to consider having a (formally redundant) PartType
field in the Parts table, with values "Drill", "Insert", etc. This would let
you add VBA code to your form to select the proper subform based on the
PartType and validate that you're not (say) assigning Insert information to a
part which is actually a Drill.

This has been a drill. If this had been a real emergency, ...

oops. sorry.

John W. Vinson [MVP]

LOL, pun appreciated.

So the tables should be more like this?

InventoryTransActionsTBL
InventoryTransActionsID
PartsID

PartsTBL
PartsID
PartsName

DrillsTBL
DrillsID
PartsID
Ect.

InsertsTBL
InsertsID
PartsID
Ect.

When I create the one to many relationship for the parts and inventory
transaction tables I think should I check all three referental integrity
boxes and select join type #1 in join properties. Is this correct?

How about the one to one relationships, I have no idea what to do there.

Thanks, Mike

"John W. Vinson" wrote:

On Tue, 4 Dec 2007 17:06:00 -0800, rsm169
wrote:

From reading your post I created an Intermediate table called "Parts", it
will have a one to many relationship to the table Inventory Transactions and
a one to one relationship with the two subclass tables. Have I understood
this correctly?

InventoryTransActionsTBL
InventoryTransActionsID
PartsID

PartsTBL
PartsID
PartsName
DrillsID
InsertsID

DrillsTBL
DrillsID
Ect.

InsertsTBL
InsertsID
Ect.


Not quite. A one to one relationship still has a parent and child. The parent
in this case is PartsTBL - its primary key should be PartsID, and it should
NOT contain a DrillsID or an InsertsID.

Instead, the "child" tables, DrillsTBL and InsertsTBL, should have the PartsID
as their primary key (and also as a foreign key to PartsTBL.

After all, every Drill is a Part; but not every Part is a Drill. So the Parts
table shouldn't contain any information specific to drills - only that
information which is pertinant to all kinds of parts.

The one exception might be to consider having a (formally redundant) PartType
field in the Parts table, with values "Drill", "Insert", etc. This would let
you add VBA code to your form to select the proper subform based on the
PartType and validate that you're not (say) assigning Insert information to a
part which is actually a Drill.

This has been a drill. If this had been a real emergency, ...

oops. sorry.

John W. Vinson [MVP]

On Thu, 6 Dec 2007 18:21:00 -0800, rsm169
wrote:

LOL, pun appreciated.

So the tables should be more like this?

InventoryTransActionsTBL
InventoryTransActionsID
PartsID

PartsTBL
PartsID
PartsName

DrillsTBL
DrillsID
PartsID
Ect.

InsertsTBL
InsertsID
PartsID
Ect.

It's probably not necessary to have separate DrillsID and InsertsID fields.
Instead, make PartsID the Primary Key of PartsTBL, DrillsTBL and InsertsTBL.
You already HAVE a unique ID - a given PartsID can apply to only one part, so
you don't need another field.

When I create the one to many relationship for the parts and inventory
transaction tables I think should I check all three referental integrity
boxes and select join type #1 in join properties. Is this correct?

If PartsID is an Autonumber, then check only the Enforce Referential Integrity
field. The Cascade Updates feature will never be needed with an autonumber;
you can't edit an autonumber value so there's nothing to cascade. You can
check Cascade Deletes if you wish... it's a bit dangerous because it will
delete records from two (or more) tables when you delete from the Parts table;
it might be safer to have the user warned by saying "you can't delete this
record from PartsTBL because there's a matching record in DrillsTBL", and then
have them explicitly choose to delete the Drills record before deleting the
Parts. That's really up to you.

How about the one to one relationships, I have no idea what to do there.

Drag the PartsID from PartsTBL to the PartsID (primary key!!) in DrillsTBL;
and from PartsTBL to the PartsID in InsertsTBL; and so on through all the
child tables. This will ensure that you must first create a Parts record, and
then specify what type of part it is by creating a record in one of the child
tables (using a Subform, or by basing your parts entry forms on queries
joining PartsTBL to the specific table).

John W. Vinson [MVP]

John,

Things have been working well for me after following your great advice. I am
able to enter and delete data and I believe everything is working properly.

I have an additional problem with my database and I am unsure if it is a
design problem or a form problem.

I created a form that shows parent table records in the header using text
boxes and child table records in the footer by using tabs.
When I enter data in the parent table, it is possible to enter data into all
of the child tables creating one large, wrong, record.

How can I restrict the user so they can enter data in the parent table and
only one child table per record?

Thanks, Mike

"John W. Vinson" wrote:

On Thu, 6 Dec 2007 18:21:00 -0800, rsm169
wrote:

LOL, pun appreciated.

So the tables should be more like this?

InventoryTransActionsTBL
InventoryTransActionsID
PartsID

PartsTBL
PartsID
PartsName

DrillsTBL
DrillsID
PartsID
Ect.

InsertsTBL
InsertsID
PartsID
Ect.

It's probably not necessary to have separate DrillsID and InsertsID fields.
Instead, make PartsID the Primary Key of PartsTBL, DrillsTBL and InsertsTBL.
You already HAVE a unique ID - a given PartsID can apply to only one part, so
you don't need another field.

When I create the one to many relationship for the parts and inventory
transaction tables I think should I check all three referental integrity
boxes and select join type #1 in join properties. Is this correct?

If PartsID is an Autonumber, then check only the Enforce Referential Integrity
field. The Cascade Updates feature will never be needed with an autonumber;
you can't edit an autonumber value so there's nothing to cascade. You can
check Cascade Deletes if you wish... it's a bit dangerous because it will
delete records from two (or more) tables when you delete from the Parts table;
it might be safer to have the user warned by saying "you can't delete this
record from PartsTBL because there's a matching record in DrillsTBL", and then
have them explicitly choose to delete the Drills record before deleting the
Parts. That's really up to you.

How about the one to one relationships, I have no idea what to do there.

Drag the PartsID from PartsTBL to the PartsID (primary key!!) in DrillsTBL;
and from PartsTBL to the PartsID in InsertsTBL; and so on through all the
child tables. This will ensure that you must first create a Parts record, and
then specify what type of part it is by creating a record in one of the child
tables (using a Subform, or by basing your parts entry forms on queries
joining PartsTBL to the specific table).

John W. Vinson [MVP]

Ok here is what I have been able to do..

I have Staff 1:m Staff/Radio m:1 Radio.

Staff/radio has Date/Time Out: and Date/Time In.. I still haven't gotten it
working past that. And unfortunately my instructor is not very helpful in
this regard. Any more help you would be willing to give would be appricated.

Jason


"rsm169" wrote:

I created an Inventory Control Database using the template in Access 2000. My
only problem is that the products table does not meet my needs. I need to
replace the Products table with about five new tables. I want to start small
so for now I have replaced The Products table with two tables, one called
Drills, and one called Inserts.

First, how do I properly relate the two new tables to the Inventory
Transactions table?

If I can get that far I should be able to handle the rest.

Thanks