Data Organization -- Bad Idea?

[baldeagle]

My primary key for my database a WorkOrder Number. The issue is that sometimes problems span a selection of WorkOrders. For example, if we get a lot of bad parts, 3 different WorkOrders might be affected, and each may have different resolution to the effect.
To speed up data entry, I've had a request to make a button that copies the all the information from a current record into a new one.

If much of this information repeats, it this a poor way to handle this data?
Currently it is pretty straight forward with ostly one->many relationships, and this would make things pretty complicated with a bunch of many<->many relationships.

Just want to know ya'll opinions.

 

[yhgtbfk]

Maybe you can use VB coding attached to a button to get data automatically.

The dlookup function allows you to get a value from the table based on criteria.

The onclick code can be something like:

Private Sub myCommand_Click()
On Error GoTo Err_myCommand_Click

Dim myValue 'The value of the current Work Number

myValue = WorkOrder

'Create a new record
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec

myText = dlookup("myField", "myTable", "WorkOrder = '" & myValue & "'")
myText2 = dlookup("myField2", "myTable", "WorkOrder = '" & myValue & "'")
myText3 = dlookup("myField3", "myTable", "WorkOrder = '" & myValue & "'")

Exit_myCommand_Click:
Exit Sub
Err_myCommand_Click:
MsgBox Err.Description
Resume Exit_myCommand_Click
End Sub


Hope that helps.

 

[Pat Hartman]

Since Work orders are many-to-many with problems, you need to reflect that in your table schema. You need three tables. Work orders, problems, and WorkOrderProblems. The third table is a junction table that connects problems to workorders. With this structure, if the problem data is updated, that update is reflected in ALL related work orders without having to manage duplicate data.

Download my ManyToMany sample database from the samples forum to see how this structure is implemented.