How to create one to many relationships for a 3 level selection process

I am designing a goods return database in Access 2003 and on the GRN form I want my users to select a product error TYPE then select a product FUALT based on the product error type, then I want my user to select a error CAUSE based on the product error and fault error how do I design the tables to reflect this selection process? So far I have managed to get a one to many relationship between TYPE and Fault but when I try to create a relationship on the next level between FAULT and Cause I keep getting a message saying it is an indeterminate being new at this I can not fathom why this is any help would be greatly appreciated.

Choose An Error TYPE      Choose A Fault      Choose a Cause
Each type can have many faults      Each fault can have many causes      
TYPE 1      FAULTS 1 TO 6                             CAUSES 1 TO 10

TYPE 2      FAULTS 7 TO 8M                          CAUSES 11 - 15

TYPE 3      FAULTS 9 TO 12                           CAUSES 16 - 25
SimonreproAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You need the following table structure at a minimum:

tblTypes
TypeID - AN - PK
TypeDescription - Text - CK1
...

tblTypeFaults
FaultID - AN - PK
TypeID - Long - CK1A - Also FK to tblTypes
FaultNum - Text - CK1B
FaultDescription - Text
...

tblFaultCauses
CauseID - AN - PK
FaultID - Long - CK1A - Also FK to tblTypeFaults
CauseNum - Text - CK1B
CauseDescription - Text
...

   'PK' is primary key, CK is 'canidate key' .     CK1A is 'canidate key 1, part A.   CK1B would be the second field as part of that key.

  The above assums that faults and causes are unique to the type and not the same for every type.

 Give that a once over and bounce back with questions.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SimonreproAuthor Commented:
Well I do not understand any of this, I just need to cut and paste something or a detailed explanation of click this click that etc?
0
SimonreproAuthor Commented:
Is there not just a way of typing something in where it says 'Where' that does this?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SimonreproAuthor Commented:
sorry I mean where it says criteria?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Well I'll start off by saying that Access is a relational database product and if you don't approach it from that aspect, you'll have problems using it.

Generally when you are having problems figuring out how to do a relationship, it is because the table design is incorrect.

So I gave you what your tables should be looking like.  If they looked like this, then you would find adding the relationship very straight forward, and would be able to it without issue or error.

What the above table design gives you is the ability for a type to have 1 or more fault, and each fault to have one or more causes, all independent of one another (cause 1 for a given fault can be different then cause 1 for another fault, and by the same token, fault 1 for one type does not need to be the same as fault 1 for another type).

 You need to make sure your table design is correct before you can move on.  If you can post the DB with the tables only, we can look it over.

 Jim.
0
SimonreproAuthor Commented:
With the layout you can see in the screen shot I can run this and it works by that I mean it shows how many printers by printer type the client has, so the relationships are all in place to do this already.  I just want to sum total by client.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I just want to sum total by client. >>

 I don't see any screen shot attached.   And you mentioned creating a relationship, not something with a query...

 A relationship and a join in a query are two different things.

Jim.
0
SimonreproAuthor Commented:
Im really sorry but here is the screen shot I forgot to add!!!
untitled.zip
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Click the "E" button on the toolbar, which will then give you a GROUP BY query.

You'll see a total row in the grid.   Group by should be by client_code.  Under CM_NumberOf, choose SUM.

All other columns can be removed or set to LAST.

Jim.
0
SimonreproAuthor Commented:
Perfect thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.