primary key problem

[computerfreaks]

I posted a message the other day about my non conformance database, I was told I need to make two tables. One for Non-conformances, another for the costing of the non-conformance where I could then sort the costings into the type of cost e.g admin cost, machining cost e.t.c.

The problem is, all non-conformances have an "NC number" (Non Conformance number), this I thought could be the primary key as it is specific to only one non-conformance, if I try to define this primary key forthe costing data however I have a problem as a single non-conformance can have many costs e.g. admin, machining, inspection, I am then told I can't duplicate entries for the primary key.....

What should I do????

[This message has been edited by computerfreaks (edited 01-30-2002).]

 

[computerfreaks]

If I were to create seperate tables, how would i ensure data could be entered from a form into these seperate tables, for example, if I had seperate tables for each cost, and currently in the form I have a drop down menu to select which type of costing you wish to enter data for, how would I then make sure by chossing a different choice in the menu that it would enter the data into the correct table??

 

[computerfreaks]

Also if I have multiple tables for each different type of costing, sorted by their NC number, how would I then be able to query all of the data? I have tried this before, but because you don't always have different costings for a non conformance some tables have no information for that particular NC number and so the query returns nothing for the whole set.

[This message has been edited by computerfreaks (edited 01-30-2002).]

 

[computerfreaks]

Will it allow me to have multiple entries for the same NC Numberif NC number is the primary key? for example.....

NC Number | Cost type | Amount
--------------------------------
456456 | Admin | £50
--------------------------------
456456 | Inspection| £20
--------------------------------
123123 | Machine | £121

?? many thanks for the help, much appreciated, deadline is drawing close!

[This message has been edited by computerfreaks (edited 01-30-2002).]

 

[computerfreaks]

Pat I'm being really dumb here but how do you have more than one primary key?? I thought that was the idea of it being primary?

cheers

 

[KKilfoil]

cf: I will elaborate on my response to your prior post.

I think I would use 3 tables as follows:

tbl_NC
NCId (primary key)
NCDescription
LogDate
etc.

Tbl_CostTypes
CTId (primary Key)
CostDesc
CostRate
etc

Tbl_CostNCLink
LinkID (primary key)
FK_NC (foreign key to non-conformance table PK)
FK_Cost(FK to Cost table PK)
ElaspedTime
etc

Define one-to-many relationships (enforced RI) between the Link table and both other tables.

For a given NC, you can have several records in the link table, each one pointing to the ID of the cost type table.

Create a form/subform for NC and Link, and use the Cost table in a combobox lookup from your Link subform.

If you need more detail, let me know.

 

[KKilfoil]

Pat H:

How do you 'link' to another forum posting?

I noticed the forum regulars do this regularly, but I haven't found a way in the FAQ or other posts that explains this.

 

[Fizzio]

KKilfoil,

just type (or paste) in the full address into the message. It will automatically be converted to a Hyperlink. Look at the UBB Code link for others.

 

[computerfreaks]

What exactly is a foreign key? I have searched through the help and can't see anything, is it just an expression you use meaning they are linked via realtionships?

Many many thanks for the help guys

 

[KKilfoil]

Its a term used in relational database theory. It refers to a field in a table that is constrained to contain the same value as a PK in another table, and serves as the 'link' or 'key' to the other 'foreign' table.

In Access parlance, it's the field that stores the 'many' end of a one-to-many relationship, for example.

 

[computerfreaks]

I understand all the theory of that, and it sounds great. How exactly do I create a combo lookup on the form?

So far, I have made the three tables, defined the foreign keys, made two forms, one for CostNClink, one for NC, dragged the CostNCLink table onto the NC to create it as a subform (thats correct isn't it?), and now I'm unsure what to do........

so far so good

 

[KKilfoil]

I would create my NC form / Link subform using the form wizard. Make the subform a datasheet view.

Review MS Help (or any text) on using form wizard to set up forms and subforms.

Add ALL of the fields for both of your tables, even if you don't need them. You can easily throw away the controls you do not need later.

Once you have this set up, go to the SUBFORM in design view. Use the Combobox wizard to assist you in setting up a control to populate the CostFK field. Make sure you store the value in the Link table!

Once all this is done, you can query values based on the PK/FK links, etc., and set up reports in a similar fashion.

 

[computerfreaks]

I have made the combo box as you have said, does this allow for different costins though...

What I mean is that if theres a machining cost it could have all different forms of costings for it, rate per hour may be different, time, set costs e.t.c. The combo box seems to only give a choice of already existing records.

Should I first create a form where all the costings are entered, followed with the form you have described so the user can then choose the costing (s)he has entered? or am I totally off on this one?

Damn I hate being a newbie, hope I'm not annoying anyone

 

[computerfreaks]

If anyone could create a simple demo of how they'd do it and e-mail it I'd be very grateful..... I can offer web design help in return?? or troubleshooting of computers

 

[KKilfoil]

Ditto what Pat said.

Be sure you are storing data in the appropriate table.

For example, if the time spent on a particular NC will always apply to every costing record, the 'time' data belongs in the NC table.

If the 'time' varies between NC's for a given costing, then 'time' belongs in your 'link' table.

If 'time' is truly constant for each costing type, then by all means put it in your 'costings table.

Don't fall into the trap of creating a unique costing record for different values of 'time'.

If a particular 'costing' can be performed by more than one department, you may want to set up a separate 'department' table and relate it to 'link'.


Most problems in db design (or at least most of MY problems) can be avoided if you can carefully plan your tables and relationships design to handle EVERY possible configuration, BEFORE you actually construct them!

[This message has been edited by KKilfoil (edited 01-31-2002).]

 

[computerfreaks]

Cheers guys, I shall get to work asap, you never know maybe one day i'll be answering peoples questions on this forum!

I'm off to build a new server now, will let ya know if I run into anymore probs. Cheers