Calculated fields

[wiklendt]

the only reason I have created a link between fields that are identical is simply because one of the tables is the source of a lookup for a field in the other.

I hope this sheds some light on why I have the relationships window looking like this...

Hi Insomnai,

I understand why you have created relationships between these fields, what i am trying to say, however, is that some of these fields should not be there in the first place to have a link made.

In other words, yes, it is normal to create a relationship between foreign and primary keys but it is NOT normal to store the same data in multiple places. You only need to store the data once - you need to decide where is the best SINGLE place is to store it.

If we knew more how your data comes together and how it is used - that is, what is your business model or process - then we'd be able to suggest the best place for the data. From what i can see, i'm wondering why there is a MaterialCategory table but no Material table (i.e., the way a Gender table might be related to a Customer table - you can't fulfill orders just on gender info without customer info)? When you compose your process explanation, try not to use field names and computer speak but what you are actually doing in plain english.... for example:

"We buy materials (metal alloys) from suppliers. We need to track our purchase order number and details of each order so we know where each material batch came from in case there is a problem with the materials.

When these materials enter our laboratory, we test the materials for quality and purity on 3 different machines (1 machine test temperature limits, another tests pressure and malleability, and the last measures resistance to chemicals) and 5 other chemical reactions we have to do manually in test tubes because they aren't covered by machine 3. we need to store all this quality data then calculate the quality of each material before we issue our own certificate based on the purity of these materials.

Additionally, we require a report which groups each of these testing panels and shows us which materials do not pass quality testing, and we need to know the supplier details of these quality failed materials so we can issue a warning for suppliers."

Then we can help you decide what data goes into what tables and which fields. As it stands, having read all your descriptions, i have no idea where your data is generated/collected and even more confused about how your data is used.

Agnieszka.