Historization of Data

[alive84]

Hi folks,

I have (maybe) a challanging question. First, how does evertything look like:

I have a DB (of course) with relations like:

____________                      _____________ 
| ContactsID   |                     | WorkID         | 
|     Name      |  1 --------> N |   Workstream | ----->  Workstream
|     Phone      |                  |   CostCenter  | ------> CostCenter
|       etc         |                 |    Vendor       |  -------> Vendor
 
 

What I would like to implement is:

That a Contact A can have two WorkID. However, first say yesterday I have added Contact A and today he switches Workstreams. So, I would like to "click" a button, which opens a new Work_subForm, but without removing Contact Information. Basically, one ContactID with several WorkID. I think it should be possible to just make on my navigation bar a button, where "create new work Information". Right? But how? With an update/insert SQL, but then how do I tell the SQL-statement to "flush" the form?

More clarifications: I have a form, where you first type in all Contact information, then you switch pages to Work_sub.

I know what I have to do, but I do not know how. I would highly appreciate if somebody gives me some hints, keywords, so I could do some research and give my best shoot to solve the challange.

Thanks a lot.

Alive

[FishVal]

Hi folks,

I have (maybe) a challanging question. First, how does evertything look like:

I have a DB (of course) with relations like:

____________                      _____________ 
| ContactsID   |                     | WorkID         | 
|     Name      |  1 --------> N |   Workstream | ----->  Workstream
|     Phone      |                  |   CostCenter  | ------> CostCenter
|       etc         |                 |    Vendor       |  -------> Vendor
 
 

What I would like to implement is:

That a Contact A can have two WorkID. However, first say yesterday I have added Contact A and today he switches Workstreams. So, I would like to "click" a button, which opens a new Work_subForm, but without removing Contact Information. Basically, one ContactID with several WorkID. I think it should be possible to just make on my navigation bar a button, where "create new work Information". Right? But how? With an update/insert SQL, but then how do I tell the SQL-statement to "flush" the form?

More clarifications: I have a form, where you first type in all Contact information, then you switch pages to Work_sub.

I know what I have to do, but I do not know how. I would highly appreciate if somebody gives me some hints, keywords, so I could do some research and give my best shoot to solve the challange.

Thanks a lot.

Alive

Nice drawing but unfortunately doesn't make sense. For example I can only guess what field in tblWork is ForeignKey to tblContacts, as well as what are field types.

Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[alive84]

Nice drawing but unfortunately doesn't make sense. For example I can only guess what field in tblWork is ForeignKey to tblContacts, as well as what are field types.

Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

I see, sorry, I hoped this would make sense :-).

tblContacts

 Field; Type; IndexInfo
ContactID, AutoNumber, PK
FirstName, Text, 
etc.
 
  tblContact 1---> N tblWork 
WorkID, AutoNumber, PK
ContactID, Number, FK
StartDate, Date, 
EndDate, Date,
WorkstreamID, Number, FK
OrgUnit, Text, FK
CostCenter, Text,
Region, Text,
VendorID, Number, FK
 

tblWorkstream, tblCostCenter and tblVendor have a simple relationship with tblWork. In Access the option 3.

Hope this makes more sense.

Thanks,

Alive

[FishVal]

I see, sorry, I hoped this would make sense :-).

tblContacts

 Field; Type; IndexInfo
ContactID, AutoNumber, PK
FirstName, Text, 
etc.
 

  tblContact 1---> N tblWork 
WorkID, AutoNumber, PK
ContactID, Number, FK
StartDate, Date, 
EndDate, Date,
WorkstreamID, Number, FK
OrgUnit, Text, FK
CostCenter, Text,
Region, Text,
VendorID, Number, FK
 

tblWorkstream, tblCostCenter and tblVendor have a simple relationship with tblWork. In Access the option 3.

Hope this makes more sense.

Thanks,

Alive

Ok. Thanks. Now it is almost absolutely clear.

Do you mean adding record with the same WorkID and different WorkstreamID.
If so you need to make one more step for db normalization and split tblWork in the manner like this.

tblWork 1-->N tblWorkWorkstreams N<--1 tblWorkstream
(creative! no? :) )

 tblWork
WorkID, AutoNumber, PK
ContactID, Number, FK
StartDate, Date, 
EndDate, Date,
OrgUnit, Text, FK
CostCenter, Text,
Region, Text,
VendorID, Number, FK
 

 tblWorkWorkstreams
WorkWorkstreamID, Autonumber, PK
WorkID, Number, FK
WorkstreamID, Number, FK
SwitchedOn, Date
 

Now you see you've got a table where history of WorkstreamID changing within particular WorkID is stored.

[alive84]

Ok. Thanks. Now it is almost absolutely clear.

Do you mean adding record with the same WorkID and different WorkstreamID.
If so you need to make one more step for db normalization and split tblWork in the manner like this.

tblWork 1-->N tblWorkWorkstreams N<--1 tblWorkstream
(creative! no? :) )

 tblWork
WorkID, AutoNumber, PK
ContactID, Number, FK
StartDate, Date, 
EndDate, Date,
OrgUnit, Text, FK
CostCenter, Text,
Region, Text,
VendorID, Number, FK
 

 tblWorkWorkstreams
WorkWorkstreamID, Autonumber, PK
WorkID, Number, FK
WorkstreamID, Number, FK
SwitchedOn, Date
 

Now you see you've got a table where history of WorkstreamID changing within particular WorkID is stored.

hahahha...so, I actually need:

Add to Contact a second WorkID

For Example: Alive Miller, TimeSquare, CostCenter1, Workstream IT
End of the Month changes CostCenter. However, I want to track this in the future, so I want to know where he was working previously. Historization.
So he has two WorkID's but only one ContactID:
Alive Miller, TimeSquare, StartDate, EndDate, CostCenter1, WorkstreamIT
.....................................StartDate, EndDate, CostCenter2, WorkstreamIT

Does that make any sense? The "trigger or switch" is EndDate.

Thanks

[FishVal]

hahahha...so, I actually need:

Add to Contact a second WorkID

For Example: Alive Miller, TimeSquare, CostCenter1, Workstream IT
End of the Month changes CostCenter. However, I want to track this in the future, so I want to know where he was working previously. Historization.
So he has two WorkID's but only one ContactID:
Alive Miller, TimeSquare, StartDate, EndDate, CostCenter1, WorkstreamIT
.....................................StartDate, EndDate, CostCenter2, WorkstreamIT

Does that make any sense? The "trigger or switch" is EndDate.

Thanks

My anderstend yorself little-little.

Simply add an new record.
There are at least two approaches:

• Normaly this is implemented with Subform bound to MainForm via Master(PK)/Child(FK) fields allowing you to add FK value automatically. BTW if you select table which is on 1-side of relationship and click AutoForm button you'll get a form with properly bound subform (N-side table).
• If you have some very special reason not to use subform you can simulate subform automation by setting DefaultValues of the controls linked to 1-side table to there current values. Actually this is the same that subform does. You surely can use a button. Code smthng like this. For each control you want to keep value in set Tag property to "Drink a poison".
  

  Private Sub Command8_Click()
   
      For Each ctrl In Me.Controls
          If ctrl.Tag = "Drink a poison" Then
              If TypeName(ctrl.Value) = "Date" Then
                  ctrl.DefaultValue = "#" & ctrl.Value & "#"
              Else
                  ctrl.DefaultValue = ctrl.Value
              End If
          End If
      Next
      DoCmd.GoToRecord , , acNewRec
   
  End Sub
   

In any case I would suggest you to use subform as more simple, natural and reliable solution.

[alive84]

My anderstend yorself little-little.

Simply add an new record.
There are at least two approaches:

• Normaly this is implemented with Subform bound to MainForm via Master(PK)/Child(FK) fields allowing you to add FK value automatically. BTW if you select table which is on 1-side of relationship and click AutoForm button you'll get a form with properly bound subform (N-side table).
• If you have some very special reason not to use subform you can simulate subform automation by setting DefaultValues of the controls linked to 1-side table to there current values. Actually this is the same that subform does. You surely can use a button. Code smthng like this. For each control you want to keep value in set Tag property to "Drink a poison".
  

  Private Sub Command8_Click()
   
      For Each ctrl In Me.Controls
          If ctrl.Tag = "Drink a poison" Then
              If TypeName(ctrl.Value) = "Date" Then
                  ctrl.DefaultValue = "#" & ctrl.Value & "#"
              Else
                  ctrl.DefaultValue = ctrl.Value
              End If
          End If
      Next
      DoCmd.GoToRecord , , acNewRec
   
  End Sub
   

In any case I would suggest you to use subform as more simple, natural and reliable solution.

Fish,

I have to appologize. Today in the morning I finally got, what you meant with subforms etc.

I am so sorry for my stupid questions. Thanks a lot for your patience.

Regards,

Sacha

[FishVal]

Fish,

I have to appologize. Today in the morning I finally got, what you meant with subforms etc.

I am so sorry for my stupid questions. Thanks a lot for your patience.

Regards,

Sacha

Nevermind

You are still welcome. :)
If you consider your previous questions stupid, then you've made a big step forward in DB desighning. And I'm glad having helped you on this.

Regards,

Valentine