Question Howdy: sub tables

[Robroy]

I have what seemed to be a simple database with three tables i.e. Customers, Fabric and Tests.
Each customer has more than one fabric, and each fabric has more than one test carried out on it.
I have set it up with customers having fabric as a sub table (the customer FK in the fabric table), and fabric having tests as its sub table (the fabric FK is in the tests table.)
There are two one to many joins between Customer and Fabric, and Fabric and Tests.
When I try to make a query incorporating all the tables the data doesn't seem to represent what data is in the individual tables.
Can any one please help.

 

[JHB]

... the data doesn't seem to represent what data is in the individual tables.

Could you please explain what exactly you mean?
What do you get then and what do you expect to get?

 

[Minty]

Can you post up some sample data - an excel picture of some table data and then your current query output, along with the output you are trying to get.

Give us enough data to cover all possible outcomes.

 

[Frothingslosh]

Some other questions we'll have:

You say customers can have more than one fabric, but is the reverse true? Can one fabric go to multiple customers?

The same with fabrics and tests: One fabric can have multiple tests done, but can the same test/test type be performed on multiple fabrics? (Example: you're testing a series of rocks. Possible tests may include spectroscopic analysis, a chemical analysis, and carbon-14 dating. A sample could undergo any or all of them, and obviously the tests will be applied to multiple samples.)

Quite honestly, the best thing you can do to start out with is, along with the sample data Minty requested, give us a write-up of what you're trying to do in plain English, with zero database terminology included. Assume we're your average high school student trying to learn what you want - that helps avoid accidental assumptions about what we may know about your process/business but don't.

 

[Robroy]

Thanks all for your comments.
When I think about it a little more it is conceivable that one customer has more than one fabric, and one fabric has more than one customer. Although this scenario is unlikely. What is more likely is that one fabric will have more than one test and one test can have more than one fabric.
Best
R

 

[Robroy]

What I am trying to do is keep a record of customers that require their fabrics treating and testing.
I cannot think of a situation where it is not the customers product that will be processed, as far as I can see it will always be one of the customers products.
After treatment each fabric will have to be tested and a record of the test kept. The tests are standard tests eg. flame retardency, and the same test will apply to different fabrics, plus the same fabric will require more than one test e.g. flame retardency and abrasion.

 

[Frothingslosh]

What you're talking about is called a many-to-many relationship. This page should help you there:

https://support.office.com/en-us/ar...ionships-e65bcc53-8e1c-444a-b4fb-1c0b8c1f5653