Table Relationship Puzzle

[DD999]

Here's a generic description of a problem I'm facing.

I want to track support requirements for different items.

Each item requires different numbers of support widgets, gidgets, gadgets and baskets when used in different environments. There are 7 environment types but more could be added later.

What table structure (and keys) would best support tracking this kind of information?

New items will always be added. New environments may be added. The support requirements will always be for numbers of widgets, gidgets, gadgets and baskets in each environment, but these numbers could change so we need to keep record of each change.

I keep running into little problems in trying to set this up. Any other insights would be welcome.

Thanks!

 

[randysmid]

Hi,
You may want to look at the Northwind database. It contains three tables in particular that show a one-to-many relationship. When you enter the Northwind.MDB, click on Tools, then Relationships. You will see a graphical display of how these tables link together. In particular, look at Suppliers, Products, Order Details. The line connecting Suppliers to Products shows a one relationship (from Suppliers) to Products (the "many&quot. Of course, in real life, this means that a single supplier can provide many different products. You will also see the table called "Categories". This may match up with your "environments".

HTH,
Randy Smith
California Teachers Association

 

[DD999]

Hi Randy,

I've tried looking at Northwind but it has some of the problems in it that I'm trying to get past.

For instance, I want to be able to choose a value from a combo box and have it load the record and sub-records into a form and subform.

I noticed that the Northwinds doesn't do this properly.

If I go into the Order form and choose a customer, it loads order info. But if I then go and select a different customer it doesn't update the sub-form with the correct records.

Ages ago I used to do all of this stuff by coding in dBase and Foxpro and it was a lot easier then. The GUI interface MS uses seems to complicate matters.

 

[randysmid]

Hi,
Can you write to me at rsmith@cta.org? I have a CoreDatabase.MDB that contains some useful stuff, including a PersonnelMaintenance form where you can select a person either by SSN or Name, and once a name has been selected, it will load the appropriate record onto the form. I also have my own security login form, and a easy-to-use menu system you might like.
If you are interested, just send me your email address and I will send you my zipped database.


HTH,
Randy Smith
California Teachers Association