Combo Box not showing the correct Field

[jzwp11]

Would you suggest to just add new Fields to the LOTO Table to eleminate the look up fields or to manually change the display control to text box?

Since you do not have a lot of records to update, I would just change the field's display control to a text box.

I created the tblEnergySources and the tblEnergySourcesCategories and added the relationships. In the previous lookup field I was able to have more than one value for each record for that field, will I be able to do that again? Is that Recommended or is there a better way to achieve this.

What you are describing is a one-to-many relationship. One LOTO record can have many energy sources. Additonally, an energy source can apply to many LOTO records (I assume). If that is the case, you have another one-to-many relationship. When you have two one-to-many relationships between the same two entities (LOTO and energy source) you have a many-to-many relationship which has to be captured via a junction table



Your current tables:
LOTO
-pkLOTOID primary key autonumber
other fields

tblEnergySources
-pkEnergySourceID primary key, autonumber

The junction table:
tblLOTOEnergySources
-pkLOTOESID primary key, autonumber
-fkLOTOID foreign key to LOTO table
-fkEnergySourceID foreign key to tblEnergySources

[Atlascycle]

I have followed the steps in your previous Post.

I am Attaching where I am at now.

I have removed the attachment fields From the LOTO Table to allow the whole database to be uploaded.

Going to update the fkETMachine_ID Field the LOTO Table and Post my next Question when I run into the next problem.

Thanks again for your time.

Jason

[jzwp11]

I'm a little confused. You still show a field in the LOTO table for energy sources. If many energy sources are possible then you need to put the related ones in tblLOTOEnergySources. You still have several other lookups in the LOTO table. Should I assume that you will be working on those as well?

[Atlascycle]

I'm a little confused. You still show a field in the LOTO table for energy sources. If many energy sources are possible then you need to put the related ones in tblLOTOEnergySources. You still have several other lookups in the LOTO table. Should I assume that you will be working on those as well?

Before I Remove the EnergySources Field from the LOTO Table I would Like to Create a Form to be able to Aid in the transfer of all of the already complied information.

I am looking at slowly working my way through the fields in this manner to hopefully not lose and have to manually reenter to much data from scratch.

So for the Energry sources I will need to create a form with the following fields:

LOTO Primary Key
Equipment Table Machine_ID
LOTO EnergySources

and Then a Subform for the Junction Table tblLOTOOperatorControls containing:

-pkLOTOOperatorControlsID primary key, autonumber
-fkLOTOID foreign key to LOTO table
-fkEnergySourceID foreign key to tblEnergySources

Does that make any sense?

Jason

[Atlascycle]

I created the above mentioned form and i am populating the tblEnergySources.

It is Working Well.

thanks Again.

Jason

[jzwp11]

Good to hear; post back if you run into issues or have additional questions.

[Atlascycle]

I will, thanks again for the Help

Jason

[Atlascycle]

Next Lookup to remove would be the Operator Controls. I already have the Operator Controls table, So my Guess would be to Follow the Energy Sources Direction that you outlined above and do the same with the Operator Controls. One LOTO Record can have many Operator Controls and one particular Operator Control can be Found on Many LOTO Records. So I would use another Junction Table Again to do the Following:

The junction table:
tblLOTOOperatorControls
-pkLOTOOperatorControlsID primary key, autonumber
-fkLOTOID foreign key to LOTO table
-fkOperarorControlsID foreign key to tblOperatorControls

[Atlascycle]

Ok I have restructured the LOTO Table, If there is no longer any major design problems with the Database I would like to start recreating the report. I have attached the current state of the Database. I am looking to recreate the current LOTOSurveyrpt But I am assuming that I will need to create a Query to isolate a specific record in the table. Is this Correct? If so I have created the qryLOTOET to create a form to help populate some of the fields that i have restructured. If I base the Report from this query I am having trouble getting it to group the records the way that i want it to.

Also what is the best way to display the information that is aquirred thought a Junction table?

I would like the Report to be similar to the attached PDF. This is the report that is in the database before restructuring the table. but now it is no longer working.

thank you again for any assistance.

Jason

[jzwp11]

I see you had separate tables for startup, shutdown and verify procedures. The rule is that like data should be in 1 table. Since the are all procedures just of different types, we can put them in a table and we would add a field to distinguish each by type. I have done the consolidation in the attached DB for you. I also replaced the 3 linking tables to the LOTO with 1 linking table between LOTO and procedures (tblLOTOProcedures). I included a sequence number (longSeqNo) field in that table so as to preserve the order of the procedures in each procedure type group.

As to whether the table structure is complete, let me ask a couple questions.

In the LOTO table, you have a field called panellocation. I see a lot of repeated location names. You could dump the names into a table and then reference a foreign key in the LOTO table. This will save you the trouble of having to type the location each time. Also, the breakerNo field has multiple values, that would suggest that you have a one-to-many relationship between the panel of the LOTO and the breakers, so technically each breaker should be a record in a related table. Now that might be taking normalization a little far, so I will leave that part to you. Does/will a piece of equipment have only one panel?

I assume that you will be removing the ElectrialEnergyControlMeans field since you now reference the same info via the fkElectCntrlID field, correct?

BTW, I took a quick look at your report and I think you will be able to come up with something similar although not exact. You still have a few table structure issues to decide on as indicated above.

[Atlascycle]

I see you had separate tables for startup, shutdown and verify procedures. The rule is that like data should be in 1 table. Since the are all procedures just of different types, we can put them in a table and we would add a field to distinguish each by type. I have done the consolidation in the attached DB for you. Thank you Very Much, that is above and beyond. I also replaced the 3 linking tables to the LOTO with 1 linking table between LOTO and procedures (tblLOTOProcedures). I included a sequence number (longSeqNo) field in that table so as to preserve the order of the procedures in each procedure type group.

As to whether the table structure is complete, let me ask a couple questions.

In the LOTO table, you have a field called panellocation. I see a lot of repeated location names. You could dump the names into a table and then reference a foreign key in the LOTO table. This will save you the trouble of having to type the location each time. Also, the breakerNo field has multiple values, that would suggest that you have a one-to-many relationship between the panel of the LOTO and the breakers, so technically each breaker should be a record in a related table. Now that might be taking normalization a little far, so I will leave that part to you. I think that I would be just a little bit of over kill, I have considered it in order to save the location of the panels with the Panel number however. Does/will a piece of equipment have only one panel? Yes they will only have one panel. I have a small format change that I need to make here. My facility does not have a central Buss Power system, we have a system of Panel Boards similar to what you would have in your home. the Power is sourced from an individual Panel Board to the machine, I have a separate Disconnect Switch at most of the Machines, Some will have to have the Power Isolated at the Originating Panel Board and then some will also Be connected by Cord and Plug so this type of Equipment will not have a Panel or a disconnect. g

I assume that you will be removing the ElectrialEnergyControlMeans field since you now reference the same info via the fkElectCntrlID field, correct? Yes that will now be removed

BTW, I took a quick look at your report and I think you will be able to come up with something similar although not exact. You still have a few table structure issues to decide on as indicated above.

Let me do a little work on this over the weekend and I will post back with any more questions.

Thanks again for your help.

[Atlascycle]

Attached is my Current Progress on the Table Structure.

[jzwp11]

Also, regarding the yes/no field in the LOTO table: DisconnectAtMach, I do not really think it is necessary since the panel location is specified, unless you have it there for some other reason. Also, were you going to move the disconnect locations to their own table and just reference a foreign key in the LOTO table?

[Atlascycle]

Also, regarding the yes/no field in the LOTO table: DisconnectAtMach, I do not really think it is necessary since the panel location is specified, unless you have it there for some other reason. Also, were you going to move the disconnect locations to their own table and just reference a foreign key in the LOTO table?

I Removed the Yes/no field DisconnectArMach, I also created the Table tblDisconnectLocation and referenced a foreign key in the LOTO Table.

Would the next step be redesigning the report or would it be advisable to work on a form to help enter the information as I add more equipment to the Database?

Jason

[jzwp11]

The next step typically would be to work on forms for data entry, but since you already have the database populated with some data you can work on your reports as well.