How to get an overview of data of several colums

[Laura]

Hi !

I'm creating a microsoft database for the defects found on the products. It
is important to specify, what kind of defects (& how many) are found and on
what productgroups and on which batch. As it is impossible to specify all the
types of defect that are to be found beforehand I've created a tabel for the
definitons of defect (only one column: the definitons, primary key).

The found defects are selected from the "Defect definiton table" saved in
the "main table" in one field and the number of defects found in an other.
I've got this "definition field - number field" column pair a couple as there
may be several kinds of defects on products of one batch.

And now to be the present problem: I should somehow be able to get the
overview of the defects found in each month. The ideal solution would a table
of the defect definitions in one column and the number of found ones then in
another.

Is this somehow possible with present database stucture? If not what do I
need to change?

This seems to be well beyond my skills. Please help me!

Thank you already beforehand!

 

[Nikos Yannacopoulos]

Laura,

It seems there is a standard one-to-many relationship between product
batches and defects (since there can be several defects for a given
batch). The way to handle this situation is to:

1. Remove the defect field(s) from the main table;
2. Add a DefectDefID (PK) to your "Defect definiton table";
3. Make a new table (Defects_per_batch or something) with a BatchID and
a DefectDefID field, both foreign keyslinked to the main table and the
"Defect definiton table" PK fields respectively. This way, for each
defective batch in the main table, you should have as many records in
Defects_per_batch as the number of different defects for that table.

HTH,
Nikos