Not sure where to start.

[New_Guy]

Hello all,

I am new to Access and I could really use some help to point me in the right direction. I want to organize my tables properly from the start so I do not run into problems down the road. If anyone can give me some advice on topics to read it would be a big help. Here is what I am doing so far.

My database must record warranty claims. A customer could have multiple warranty claims with multiple parts that failed. Each part can be linked to multiple failures but in a claim one part and one failure must be selected.

Example: A motor can fail in many ways but I need to know how it failed for this customer.

I would like to be able cascade the combo boxes so when a part is selected the failures related to that part are updated.

TblClients
ClientID AutoNum PK (1-many with tblMain)
CustomerName text
Address text
(rest of the customer data fields)

TblParts
PartID AutoNum Pk (1-Many with tblMain)
Part text
PartDescription text

TblFailure
FailureID AutoNum Pk (1-many with tblmain)
Failure text
FailureDescription text

TblMain
ClientID
PartID
FailureID


I guess my question is when I make a form based off TblMain, do I need a partfailure table to let me cascade my combo boxes?

Thanks for any help,
Mark

 

[gemma-the-husky]

I think you are doing the right thing thinking carefully about your data design in the first place

i think you need a client table, as you have, and a warrantyclaim table, linked to this, as you may have multiple claims per client

then because you may have multiple items on each claim, you need the warrantyitems table to be linked to the warrantyclaim table, not directly to the client.


tblClient (ClientID, etc)

tblWarrantyClaim (claimId, clientId, DateofClaim, etc)

tblWarrantyDetails (claimId, PartId, failyretype, etc)

tblfailuretypes (possibly - a lookup table)

tblParts(the parts data)

--------------------------
this chain of relationships is subtly different to your original schema, as the warrantyparts in this one are independent of the client directly although the client can be identified via the claim. I hope this makes sense.

although you say in a claim, you must have a part - you don't have to to - in practice you may perhaps log a warranty claim from a customer, and until you have investigated you may not be able to add any parts.This is not an Access issue and may be useful - you can easily identify claims with no parts yet allocated. In practice you must insert the claim before you can add items to it, and it would be quite difficult to enforce a rule that a claim MUST have associated cliam-parts

a 1-many relationship doesn't mean that you HAVE to have items on both sides, just that you HAVE to have an item on the 1-side. but i'm sure you know this anyway.

 

[New_Guy]

This is great stuff, thanks for the help.
Mark