How would you setup these tables?

[jefflab1]

Brief Description: Fire Dispatch Center personal have to keep record of a 100 Milliamp Circuit System. Its a power system that operates on a circuit from our building to buildings within our city to power their alarm system box.

Guidelines:
1. We have 21 Circuits that we monitor.
2. All circuits are tested 4 times a day in this manner:
a. Press a button on the panel for each circuit that will give us the VOLTS, Positive Volts, Negative Volts, and Milliamps.
b. We "read" the power readings everyday at 0000 hours, 0600 hours, 1200 hours and 1800 hours.
3. Each circuit has to relate to one another. The only idea I have is they relate by Date and Time. Their is only one date and one out of the 4 times something can be entered. So theirs no duplicates ever. Example: 01/01/2017 00:00, etc.
4. Readings are numeric only, no alpha or weird characters.



I would appreciate all the help that you can give. Thanks for your time!

 

[MarkK]

What does this mean...

3. Each circuit has to relate to one another. The only idea I have is they relate by Date and Time.

Circuits, from what I can see, exist independently, regardless of any test, regardless of the passage of time. It doesn't make sense to me therefore that circuits could be related by Date and Time.

I would expect to see a Circuit table, and a Test table. Test should contain a link to the circuit tested, and the data that are the results of that test.

 

[The_Doc_Man]

And to amplify MarkK's comments, there should be a one-to-many relation between Circuits and Test events joined on the circuit ID as the prime key of the circuit table to a foreign key of the test table. Further, the circuit ID and the test time would be the basis for a composite prime key of the test table.

 

[jefflab1]

Thank you so much for your reply's guys!

From what I take I should create them as follows:

Table 1: Circuit Date and Time
Field 1: Auto Number
(Primary Key) Field 2: Circuit Date and Time Format

Table 2-21:
Field 1: Auto Number
(Primary Key) Field 2: Circuit Date and Time
Field 3: Circuit 1 Milliamps
Field 4: Circuit 1 Volts
Field 5: Circuit 1 Positive Volts
Field 6: Circuit 1 Negative Volts

Question:
1. Should I choose 1 to 1 relationship?
2. Should I choose Enforce Referential Integrity?
3. Should I choose Cascade update related fields?
4. Should I choose Cascade Delete Related Records?

Thanks again!

 

[The_Doc_Man]

No. That is not the correct layout based on your initial description. You need only TWO tables - not 21 tables, one for each circuit.

Table 1 - Circuits - describe the circuits, with a Circuit ID number from 1-21 as prime key (PK). Includes name, location data, other things that apply ONLY to the circuit in an overall state regardless of any measurements at all. I.e. static description of the circuit regardless of its powered/non-powered state.

Table 2 - Observations - Field 1 is the Circuit ID number, here as a foreign key (FK).
Field 2 is the date/time of the observation.
The combination of field 1 and field 2 becomes the (compound) PK of the observation table. This will guarantee that you have one and only one reading at a given time for a given circuit (though it won't stop you from having gaps in your tables if no measurements are made.)
Field 3 is the milliamp observation
Field 4 is the volts observation
Field 5 is the positive volts (?)
Field 6 is the negative volts (?)

Not sure of the difference of 4, 5, & 6 but they are listed in your post #4 for this thread so I won't make a judgment. If they are just different readings that you take, then they belong in that table.

Relationship: One-to-many, Circuit table on the "one" side, Observation table on the "many" side.

Enforce Referential Integrity? I would do so for the case you named, since you don't want to take a reading for a circuit that doesn't exist.

Cascade Update/Delete? Can't answer that without knowing a lot more about what you intend to do with these values later, but my gut reaction says "No" to the "Update" and no stronger than a loose "Maybe" to the "Delete."

 

[jefflab1]

Thanks for all your help:

1. I was able to create the 2 tables as stated.
2. It will not allow me to create a relationship one to many.
3. I tried to attach a WORD document with pictures of what has been created and the data entry form but it wont let me.

Is their a way I could send someone an email with the WORD doc and/or database?

I did create a Access Database that we are currently using. But I've been having big issues when creating reports and other issues that I think are associated with the way I set up my tables.

Thanks - Jeff

 

[jdraw]

Zip the file and attach the zip