Automatic record creation and relationships?

[rikklaney1]

Ok, I have a table called constants. In this table are 10 records, operator-1 thru operator-10. Each of these have 30 fields called act1 thru act30.

Act1 thru act30 contain either a D for something the operator should do daily, an M for something the operator should do monthly or a 2 thru 6 for the day of the week the operator should do it.

Next I have a table for each operator, clntbl-1 thru clntbl-10. Each of these tables contain fields scdate, day(calculated by weekday(scdate)) and act1 thru act30 also but this time it will be daily records for what was scheduled and what was done by date. The records will be by date so on a date a field could have a value of "q" meaning it is scheduled on that date or a value of "s" meaning it was done on that date. This is done through a form. When the operator opens the form they see a list of items to be done on the date they opened the form and when they click it changes the value to show complete instead of scheduled.

Now what I would like to do is create a record a week in advance so I use this in the onopen event of the operators form

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("clntbl-rs1")
rst.AddNew
rst("Scdate") = Date + 7
rst.update

that works well but my question is what would be the best way to automatically set the field values for each activity when creating the record based on the values in the constants table? I have never tried learning relationships yet but this kinda seems like that type of thing.

 

[arnelgp]

each operators has its own table, i think you cannot establish a relationship there. although you can perform additional code to the one you have and insert the defaults for that particular operator.

 

[plog]

I have never tried learning relationships yet but this kinda seems like that type of thing.

Relationships are the heart of a database. The process of properly structuring your tables is called normalization (https://en.wikipedia.org/wiki/Database_normalization), its the first step in even thinking about a database. And that's where you need to focus--not on code, not on queries, not on forms.

You need to get your tables right. You've made a huge mistake in having a field for each act (act1...act30). Tables should grow vertically (with more rows) and not horizontally (with more columns). That means the actual structure of that Action table should be this:

Actions
ActionID, autonumber, primary key
OperatorID, number, foreign key to Operators table
ActionFrequency, text, will hold M/D/2-6
ActionNumber, number, will hold what is now the suffix of the field name (Act_1, Act_2,...)

That's it. That will be the structure of your action table. Then, when you want to input 17 actions for an operator, you create 17 rows of data.

Again read up on normalization and structure your data properly before moving on to other items.

 

[rikklaney1]

So I only create 4 records and 10 fields rather than 10 records of 4 fields. Won't that make it more difficult to add operators later? Right now I can just create a record and input the data without having to modify the table. For some reason it just seems backwards. Well, let me read that article and try the different table design and see what I can make of it. Thanks.

 

[plog]

So I only create 4 records and 10 fields

In the Action table you will have 1 record for every Operator/Action. If you have 4 operators each doing 10 actions, that would be 40 records in Actions. I never mentioned 10 fields in Actions, my table had 4 fields and captured the same amount of data your 40 field table did.

Using my setup, you could easily add an infinite number of operators without touching the structure. You could also easily add an infinite number of actions without touching the structure.

 

[rikklaney1]

I believe we are miscommunicating. Here is the structure of my table.

ID, autonumber
Station name, Short text
act1,short text (M/D/2-6)
act2,short text (M/D/2-6)
act3,short text (M/D/2-6)
act4,short text (M/D/2-6)
act5,short text (M/D/2-6)
act6,short text (M/D/2-6)
act7,short text (M/D/2-6)
act8,short text (M/D/2-6)
act9,short text (M/D/2-6)
act10,short text (M/D/2-6)


Then the records in the table look like this

1, Rops1 , d , d , d , 2 , m , d , d , 6 , m , 6
2, Rops2 , d , d , 3 , m , 4 , d , m , 4 , 3 , 4

or some variant thereof. Is that the correct structure? I have a form that will allow me to add operators by adding a new record and putting in the different values.

 

[rikklaney1]

That is my constants table.

 

[rikklaney1]

the operators tables look like this

scdate, date/time
day, calculated
act1,short text (q if scheduled s if completed)
act2,short text (q if scheduled s if completed)
act3,short text (q if scheduled s if completed)
act4,short text (q if scheduled s if completed)
act5,short text (q if scheduled s if completed)
act6,short text (q if scheduled s if completed)
act7,short text (q if scheduled s if completed)
act8,short text (q if scheduled s if completed)
act9,short text (q if scheduled s if completed)
act10,short text (q if scheduled s if completed)

then the record themselves are by date so


2/1/2016 , 3, , s ,s , s, s ,s ,s ,s ,s ,s ,s, s

or some variation thereof.

 

[plog]

First, that's not really an Operator table, that's an action table (it has actions in it, not operator data). You should have 2 tables:

Operators
OperatorID, OperatorFName, OperatorLName, ...
1, John, Smith, ...
2, Steve, Jones, ...
3, Sally, Johnson, ...

Actions (this is what I described in my first post)
ActionID, OperatorID, Action, ActionFrequency, ActionNumber
1, 1, Unlock Building, D, 1
2, 1, Turn On Lights, D, 2
3, 1, Turn On Computer, D, 3
4, 2, Turn On Computer, D, 1
5, 2, Send Morning Report, D, 2
6, 2, Send Monday Report, 2, 10
7, 3, Clean Bathroom, 3, 1

Again read up on normalization. Numerated fields are not correct.