Hello,

I have some trouble with creating the right relationships between my tables.
What do I have so far ...

I have a table called machine tha is linked to a table machineparts (machineonderdelen) that again is linked to a table parameters (kwaliteitsgegevens).

Now my problem is the next table. This table represent the product. So the intension is when I add a product that is made on one of the machines all the parameters that are active fore this machine in the table kwaliteitsgegevens should be obliged to fill in.

How can i do this.

I have also put an example to this message.

Thanks in advance,
Sven.

You have composite keys (multiple PK's) in two tables: tblMoederrollen and TblMachineonderdeel. From the way it looks, this should not be. I would drop the PK on MachineID in both of these tables, as this field should be on the many side of the relationship with the Machine field, and doesn't seem to serve much purpose for being a PK in more than one table (unless my interpretation is off here).

Also, if the table called "TblKwaliteitgegevens" is the product table, you also have too many PK's there. There could be more information offered to you, but to do that, could you maybe provide the table names in English (having trouble reading your language!), and an explanation of what data you are going to store in them?

Hello,

thanks for the information, but I still can't solve my problem.
As you asked the explanation about the tables:

there are 5 tables.

The first table (machine) is about machines that exists in the factory:
machineID = PK about the machine
machinenaam = machinename
actief = active (important for some query's)


The second table (machineonderdelen) is about machineparts:
MachineID = PK about the machine
CategorieID = PK about the machinepart
Categorienaam = name of the machinepart
actief = is the machinepart still active (important for some query's)

the third table (kwaliteitsgegevens) is about parameters. For each machinepart there are some parameters

kwaliteitID = PK about the parameter
CategorieID = PK about the machinepart
MachineID = PK about the machine
Kwaliteitsnaam = the parameter that belongs to a machinepart and machine
Actief = is this parameter still active


the fourth table (moederrollen) is about the product that is made on a machine.

Moederrolnummer = identification number of the product
machineID = PK about the machine
Papiersoort = information about hte product
papiergewicht = information about the product
datum = date
uur = hour


The fifth table is about the values for the product. For each moederrolnummer that is created all the parameters that are still active should be filled in in this table.

Thank in advance for the help.

Sven.

I just took a quick glance but I think you might want to think about foriegn keys : )

Hello,

thanks for the information, but I still can't solve my problem.
As you asked the explanation about the tables:

there are 5 tables.

The first table (machine) is about machines that exists in the factory:
machineID = PK about the machine
machinenaam = machinename
actief = active (important for some query's)


The second table (machineonderdelen) is about machineparts:
MachineID = PK about the machine
CategorieID = PK about the machinepart
Categorienaam = name of the machinepart
actief = is the machinepart still active (important for some query's)

the third table (kwaliteitsgegevens) is about parameters. For each machinepart there are some parameters

kwaliteitID = PK about the parameter
CategorieID = PK about the machinepart
MachineID = PK about the machine
Kwaliteitsnaam = the parameter that belongs to a machinepart and machine
Actief = is this parameter still active


the fourth table (moederrollen) is about the product that is made on a machine.

Moederrolnummer = identification number of the product
machineID = PK about the machine
Papiersoort = information about hte product
papiergewicht = information about the product
datum = date
uur = hour


The fifth table is about the values for the product. For each moederrolnummer that is created all the parameters that are still active should be filled in in this table.

Thank in advance for the help.

Sven.

Hello,

I'm sorry but I don't understand what you mean by foriegn keys ...

Best regards,
Sven.

Foreign Keys are the Primary Keys from Foreign tables. This is how you join tables together.

Sven,

I will read through your post and give you some feedback on it later today. To do it well though, can you offer an explanation of the term parameters?? I have seen that you use it a lot, and I think most programmers probably use the word in a different context from which you are using it. Can you tell me what you mean by that (when you say it)? Thanks! - (it sounds like you are just referring to some sort of "related" data in tables other than that to which you are speaking of).

Hello,

with parameters I mean figures that need to be stored in the database about the machine when making a product. For example the machinespeed is a parameter or the steampressure is a parameter.

I hope this is clear for you?

Thanks for the help,
Sven.

Yes it is. So, apparently the word parameter here describes the actual "fields" in a table. That's fine, just wanted to be clear. I'll get back to you in a while...

Hi Sven.
When you reference a table- ie. A parent table to a child table
You create a constraint called a foriegn key. This is how relationalship databases relate : ) The foriegn key has to reference a unique field or set of fields in the child table, the primary key. In the parent table you can have a foriegn key that help make the primary key. When you have more then one key that creates a primary key it is known as a composite key.

For your DB to work you will have to drop the PK's that from other tables and create foriegn keys. :)

Hello,

with paramaters I mean figures that need to be stored in the database about the machine when making a product. For example the machinespeed is a parameter or the steampressure is a parameter.

I hope this is clear for you?

Thanks for the help,
Sven.

Shouldn't you be working on your own problem Harley? ;) ;) ;) ;) ;) Yours is a bit more complicated than this one! :)

I am stuck on my problem, I am waiting on help, so I am helping others to up my karma hahah

Shouldn't you be working on your own problem Harley? ;) ;) ;) ;) ;) Yours is a bit more complicated than this one! :)

Hello,

I have changed my relationships but it is still not working. When I run a query with a conection between tblkwaliteitswaarde - tbl machine - tbl kwaliteitsgegevens the records are multiplied with a factor x.

So there is still something wrong with my relationships and I haven't got any idea what is wrong ...

Who can help??

Best regards,
Sven.

which table is not relating properly?
what relationship do you want it to have : ) the foriegn words really throw me off in your tables, it makes it harder to understand the realworld relationships : ) so tell me how you want them to relate and i will attach it for you and tell you what I think the problem was.
Hello,

I have changed my relationships but it is still not working. When I run a query with a conection between tblkwaliteitswaarde - tbl machine - tbl kwaliteitsgegevens the records are multiplied with a factor x.

So there is still something wrong with my relationships and I haven't got any idea what is wrong ...

Who can help??

Best regards,
Sven.

Hello,

there are T tables:

TblMachine --> table with all the machines there are in the factory
TblKwaliteitsgegevens --> measurements that are to be registrated on the product
TblMoederrollen --> product that is made on these machines
TblMachineonderdeel --> machineparts
TblFreqentie --> the frequentie that the measurements needs to be done
TblKwaliteitswaarde --> values about the measurements


So as I see it:
machine has machineparts and machineparts has related measurements.
for each product that is made on a machine the mesurements has to be registrated according the frequentie.

If it is helpfull, i have a database filled with data and forms, maybe than it is more clear but i can't send it as attachement in the forum because of the size.


Thanks in advance,
Sven.

I'll look at the tables post my remarks as I finish.

First, in the "TblKwaliteigegevens" table, the Field "CatagoriesID" serves no purpose. It isn't tied to another table that defines it or limits the possible entries. As it currently stands now, a user will be able to enter any number they want and the database will accept it. I've always held that a Key field should have a defined entry limited by a table when possible. In my opinion, this make for cleaner data and reduces problems in the future.

You either need to create another table and set the relationship between it and this field or remove this field. (Also, the same field is used in the "TbleMachineonderdeel" and it is auto generated. This could lead to a little confusion down the road so consider changing the name in one of the tables.) The table would have two fields, "CatagoriesID" - Autogenerated Number and "CatagoriesName" - Indexed - Yes (No Duplicates). Additions to this table would be limited to key personnedl to ensure quality of the entries. Plus, it is easier to understand a "Name" than just a "Number".

Also, the "KwaliteitID" field should be changed to Indexed - Yes (No Duplicates)

Finally, what exactly makes a record in this table unique? To me it would seem that these fields would make each record unique are the MachineID, Kwaliteitsnaam, and CategorieID. The "KwaliteitID" field number isn't unique it's autogenerated and really has no meaning and could really be dropped.

I would change the Key from the "FrequentieID" field to the "Frequentie" field and make the "Frequentie" field unique and required. It's not the number of the "FrequentieID" field that is unique (Indexed - Yes (No Duplicates)), its the "Name" defining the "Frequentie" that is unique. The "FrequentieID" is random and holds no meaning except to tie the two tables together. (Again, number have little meaning, words do!) However, you need to change the 'FrequentieID" field to be a Indexed - Yes (No Duplicates) field.

Again, because it is a "Defining" table, I would limit who can enter data into this table.

Also in the "TblKwaliteigegevens" table, change the "FrequentieID" field to Indexed - Yes (Duplicates OK).

Is the "TblKwaliteitgegevens" the table that defines the "CatagoriesID" field in the "TblKwaliteigegevens" table? Are these Definitions unique to a Machine? Or are they definitions for the "CatagoriesID" for all machines? If they are for all machines, you need to remove the "MachineID" from this table and then set the relationship between it and the "TblKwaliteitgegevens" table.

If this is true, you also need to change the fields as follows: CategorieID - Indexed - Yes (No Duplicates) and the Categorie - Indexed - Yes (No Duplicates). Then set the "Categorie" field as the Key field.

I have changed my relationships but it is still not working. When I run a query with a conection between tblkwaliteitswaarde - tbl machine - tbl kwaliteitsgegevens the records are multiplied with a factor x.What kind of query were you running? What did it look like? Were there calculations involved? That might be the only reason you got that multiplication factor in there.

Here is my change to the table structure

Hello,

i have made the changes in my relationships like cpremo did, but when I use this relations in my subfoms I get a multiplicationfactor on the "kwaliteitsnaam".

As attachment there is an example of this form.

In this form, the user has to be able to check, change and add values for each (moederrol) product. I hope the example makes everything clear.

So the problem I have now is:

1) the multiplicationfactor
2) when I add a new product, how is it possible to get all the parameters that are defined for this machine and only save those parameters that filled in?


Thanks in advance for the help.
Sven.

P.S. now the subforms are using old relationships and then there is no multiplicationfactor but these relations are non defigned.

I have changed my relationships but it is still not working. When I run a query with a conection between tblkwaliteitswaarde - tbl machine - tbl kwaliteitsgegevens the records are multiplied with a factor x.What kind of query were you running? What did it look like? Were there calculations involved? That might be the only reason you got that multiplication factor in there.there are T tables:

TblMachine --> table with all the machines there are in the factory
TblKwaliteitsgegevens --> measurements that are to be registrated on the product
TblMoederrollen --> product that is made on these machines
TblMachineonderdeel --> machineparts
TblFreqentie --> the frequentie that the measurements needs to be done
TblKwaliteitswaarde --> values about the measurements


So as I see it:
machine has machineparts and machineparts has related measurements.
for each product that is made on a machine the mesurements has to be registrated according the frequentie.The way you have it set up right now, I don't think compliments the different types of data that you have to sort out. Here are the reasonings I would use if I was doing what you're doing:

1) Machine Table - Obviously you have to have a PK in this field (Machine ID), because if you had no machines, you wouldn't be making products and you would have no need for a database. So there is nothing wrong here. A good thing to remember though is that you should look at this table as the source and reason for all other data in the database.
2) Machine Parts Table - I don't see the need for a composite key here. The relationship to the Machines table should be a classic 1-many, on MachineID.
3) Products Table - Again, this table has the same issue as in #2 above. There is no need for a composite, as the products themselves relate to the machine they are produced on. Each product is unique, but one machine can make many products (I am assuming this, but it doesn't matter anyway), thus another classic 1-many relationship should be set up on MachineID.
4) Measurements Table - I would assume this information serves as an extension of the "product" information, from your description of this table's purpose. If the measurements pertain to a certain product, then this is another 1-many situation. No triple composite key is needed here either. In fact, I don't think you even need a PK in this table, although it's always good to have one (but it's not always necessary).
5) Fequency (frequentie?) and Values - I think these two sets of data are still the same as everything else!

So, basically what I see here is just a bunch of one-to-many relationships. I know you have those all set up now, but all of the composite keys you have set up need to be dropped! Looking at your relationships window just tells me that you're scared to have just one linking field. It's almost like you think that won't be good enough. Well, trust me, it's plenty good, and most of the time, anything more than that can cause you headaches that you don't want!

I apologize for taking so long to get back to you. I hope some of the information I've given can help you too.

I don't see any forms in the sample database you provided. Seeing you data entry from may help in solving your problem.

Hello,

I apologize about the wrong attachement.
This is a new version.

Thanks,
Sven.