Table Structure...

[kmkland]

I am at a loss on how to develop tables pertaining to a procedure.

In my table (we'll call it tblProcedure), I have a Date field, which is never duplicated so it is the primary key. Other fields include: Time, Shift, Auditor. On a particular date, I could have a non-compliance on a Form, which has different non-compliances (i.e. NoForm,NoName,NoList). I could also have a noncompliance on an Order, which also has different non-compliances (i.e. NoDate, NoTime, NoLoad).

In my form, I want to be able to choose which noncompliances have occurred for the Form and/or for the Order. There could be zero noncompliances; there could be 3 noncompliances.

In my report, I want to be able to group on Date, then Order or Form (grouping each separately if both have noncompliances). The noncompliances will be listed under each group, whether it be Order or Form or both. I might need to do a Union query to combine data from all of the noncompliances into one field on the report.

I am so confused how to set up my tables!! I think I have thought about this so much that I have now hit a brick wall with my ideas...

Please, help!!!

Rgds,
Kmkland

 

[lespaul]

what are you trying to do? What is a procedure? I guess it is done at a certain date and time (By the way, those are both reserved words and shouldn't be used as field names), why are you storing the date and time separately? Does the Auditor perform the procedure? Is the outcome of the procedure Compliant and Non-compliant? Are NoForm, NoName and NoList the reasons for non-compliance?

Have you read the document linked below?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[kmkland]

My field names are actually similar to the ones I listed, but not exactly the same for simplicity purposes. Field names are not my problem; constructing proper relations and number of tables are the problems that I am having.

The date and time must be stored differently; they are unrelated to one another.

The procedure actually has a proper name; the Auditor does the audits of the procedure.

The outcome is a list of noncompliances for each date, as stated in my original thread.

NoForm, NoName, etc...are the noncompliances.

Rgds,
Kmkland

 

[lespaul]

You still haven't explained (at least enough to help me help you) what you are trying to do. Did you get around to reading the document I suggested?

So you have a procedure...it has a name, a date, a time, ....the Auditor audits the procedure..

What are ORDER and FORM and how do they relate to the procedure? What is a SHIFT and how does that relate to the procedure?

You really need to define the objects and their attributes, which is what I am trying to help you do by asking these questions.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[kmkland]

Thanks LesPaul for the document. It actually helped quite a bit.

I just needed to step back and approach the design a bit differently. I have been so wrapped up in constructing the tables properly and focusing on the end result that I got tunnel vision and couldn't see how to do it.

Thanks for your help!!

Rgds,
Kmkland