|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hi all, I have a question that's been bugging me for some time now.
Basically I have a form for work orders. I have to be able to create new
orders and have some drop down lists that the user can use to choose the
information to be used for the new work order. This info is pulled from some
tables I have already storing a basic "inventory" in which the fields data
may change.
The hard part I don't know how to do is how do I show the previous work
orders and show the data that was in the tables at the time the work order
was created. I know I need to have some "history logging" tables but how do
I get the same work order form to function 2 ways? in present and past data?
Or do I use 2 seperate forms, one for showing previous data and other for
creating new order from current data??
|
|
Not really all that difficult.
Instead of using the dropdowns (combo boxes) as bound controls, make them
unbound and only use them for searching for values. Create regular text
boxes to display the data in your Work Order table.
You can get the value you look up in the combo boxes into the text boxes and
thus into your table by using the After Update event of the combos.
If Not IsNull(Me.cboSomeField) Then
Me.txtSomeField = me.cboSomeField
End If
"Jebuz" wrote:
[Quoted Text] > Hi all, I have a question that's been bugging me for some time now.
>
> Basically I have a form for work orders. I have to be able to create new
> orders and have some drop down lists that the user can use to choose the
> information to be used for the new work order. This info is pulled from some
> tables I have already storing a basic "inventory" in which the fields data
> may change.
>
> The hard part I don't know how to do is how do I show the previous work
> orders and show the data that was in the tables at the time the work order
> was created. I know I need to have some "history logging" tables but how do
> I get the same work order form to function 2 ways? in present and past data?
> Or do I use 2 seperate forms, one for showing previous data and other for
> creating new order from current data??
|
|
That part makes sense but I guess my question is more of table design than
form design. Say I have tables 'Employees' , 'Company', and 'Work Orders'.
I go to create a new work order and the user selects person A, it fills in
the company they work for automatically. I click OK , submit or whatever
after I fill out the details of the work order. Sometime after person A goes
to work for a different company, and now when I look up the work order it
shows the new company for person A, not the old one. However I still need to
be able to then create a new one and have person A fill in the company with
the new one he went to work for. If that description makes any sense.
So I guess it's how do I store previous relations between 'Employees' and
'Company' and show work orders created with those relations as well as when
the user selects "new" work order it lists the current relations?
it's like the work order form has to be bound to 2 different sets of tables
depending on if the user is on a new record or not. *shrugs*
"Klatuu" wrote:
[Quoted Text] > Not really all that difficult.
> Instead of using the dropdowns (combo boxes) as bound controls, make them
> unbound and only use them for searching for values. Create regular text
> boxes to display the data in your Work Order table.
> You can get the value you look up in the combo boxes into the text boxes and
> thus into your table by using the After Update event of the combos.
>
> If Not IsNull(Me.cboSomeField) Then
> Me.txtSomeField = me.cboSomeField
> End If
>
> "Jebuz" wrote:
>
> > Hi all, I have a question that's been bugging me for some time now.
> >
> > Basically I have a form for work orders. I have to be able to create new
> > orders and have some drop down lists that the user can use to choose the
> > information to be used for the new work order. This info is pulled from some
> > tables I have already storing a basic "inventory" in which the fields data
> > may change.
> >
> > The hard part I don't know how to do is how do I show the previous work
> > orders and show the data that was in the tables at the time the work order
> > was created. I know I need to have some "history logging" tables but how do
> > I get the same work order form to function 2 ways? in present and past data?
> > Or do I use 2 seperate forms, one for showing previous data and other for
> > creating new order from current data??
|
|
A work order record should contain the relationship to the employee table and
to the company table to keep historical accuracy. If Person A works for
company 1 and you create a work order, the work order record should contain
the primary key of the employee as a foreign key and the primary key of the
company as a foreign key. That work order record now reflects person A and
company 1. Person A goes to work for company 3. The new work order would
relate to person 1 (same person) and company 3 (different company). The
first work order would still show person A and company 1. It is a matter of
proper relationships.
How you are describing it makes me think you have the work order related to
a person and the person related to the company. That is why you are having
the issue.
The basic table lay out should be
Work Order
WO_ID - Autonumber Primary key
EMP_ID - Long - Foreign key to employee table
COMP_ID Long - Foreign key to company table
Employee
EMP_ID - Autonumber Primary key
COMP_ID - Long foreign key to company table
Company
COMP_ID Autonumber Primary key
"Jebuz" wrote:
[Quoted Text] > That part makes sense but I guess my question is more of table design than
> form design. Say I have tables 'Employees' , 'Company', and 'Work Orders'.
> I go to create a new work order and the user selects person A, it fills in
> the company they work for automatically. I click OK , submit or whatever
> after I fill out the details of the work order. Sometime after person A goes
> to work for a different company, and now when I look up the work order it
> shows the new company for person A, not the old one. However I still need to
> be able to then create a new one and have person A fill in the company with
> the new one he went to work for. If that description makes any sense.
>
> So I guess it's how do I store previous relations between 'Employees' and
> 'Company' and show work orders created with those relations as well as when
> the user selects "new" work order it lists the current relations?
>
> it's like the work order form has to be bound to 2 different sets of tables
> depending on if the user is on a new record or not. *shrugs*
>
>
> "Klatuu" wrote:
>
> > Not really all that difficult.
> > Instead of using the dropdowns (combo boxes) as bound controls, make them
> > unbound and only use them for searching for values. Create regular text
> > boxes to display the data in your Work Order table.
> > You can get the value you look up in the combo boxes into the text boxes and
> > thus into your table by using the After Update event of the combos.
> >
> > If Not IsNull(Me.cboSomeField) Then
> > Me.txtSomeField = me.cboSomeField
> > End If
> >
> > "Jebuz" wrote:
> >
> > > Hi all, I have a question that's been bugging me for some time now.
> > >
> > > Basically I have a form for work orders. I have to be able to create new
> > > orders and have some drop down lists that the user can use to choose the
> > > information to be used for the new work order. This info is pulled from some
> > > tables I have already storing a basic "inventory" in which the fields data
> > > may change.
> > >
> > > The hard part I don't know how to do is how do I show the previous work
> > > orders and show the data that was in the tables at the time the work order
> > > was created. I know I need to have some "history logging" tables but how do
> > > I get the same work order form to function 2 ways? in present and past data?
> > > Or do I use 2 seperate forms, one for showing previous data and other for
> > > creating new order from current data??
|
|
That is actually how I have it set up now. I figured that must be the way to
store the information in the tables. Here is the catch though. Say on the
work order I want to display some of the 'employee' and 'company' fields,
like person's name, phone # and company name and phone #. This all works
great till I make a change to one or both of the tables. Say changing the
company's name or employees phone #. Now I want the work order to display
the "current" fields at the time of creation not the updated fields. I know
this means I need some type of tables to track histories of 'employees' and
'companies' right? So how do I link that into the work order as well? So the
work order table and forms work with previous data in history tables and
current data in other tables when I want new work orders made?
Do I make a history table for each 'employeehistory' and 'companyhistory'
and in those have also the current records, but when the data changes mark
the correspnding records to have a 'true' retired field?
"Klatuu" wrote:
[Quoted Text] > A work order record should contain the relationship to the employee table and
> to the company table to keep historical accuracy. If Person A works for
> company 1 and you create a work order, the work order record should contain
> the primary key of the employee as a foreign key and the primary key of the
> company as a foreign key. That work order record now reflects person A and
> company 1. Person A goes to work for company 3. The new work order would
> relate to person 1 (same person) and company 3 (different company). The
> first work order would still show person A and company 1. It is a matter of
> proper relationships.
>
> How you are describing it makes me think you have the work order related to
> a person and the person related to the company. That is why you are having
> the issue.
>
> The basic table lay out should be
>
> Work Order
> WO_ID - Autonumber Primary key
> EMP_ID - Long - Foreign key to employee table
> COMP_ID Long - Foreign key to company table
>
> Employee
> EMP_ID - Autonumber Primary key
> COMP_ID - Long foreign key to company table
>
> Company
> COMP_ID Autonumber Primary key
>
> "Jebuz" wrote:
>
> > That part makes sense but I guess my question is more of table design than
> > form design. Say I have tables 'Employees' , 'Company', and 'Work Orders'.
> > I go to create a new work order and the user selects person A, it fills in
> > the company they work for automatically. I click OK , submit or whatever
> > after I fill out the details of the work order. Sometime after person A goes
> > to work for a different company, and now when I look up the work order it
> > shows the new company for person A, not the old one. However I still need to
> > be able to then create a new one and have person A fill in the company with
> > the new one he went to work for. If that description makes any sense.
> >
> > So I guess it's how do I store previous relations between 'Employees' and
> > 'Company' and show work orders created with those relations as well as when
> > the user selects "new" work order it lists the current relations?
> >
> > it's like the work order form has to be bound to 2 different sets of tables
> > depending on if the user is on a new record or not. *shrugs*
> >
> >
> > "Klatuu" wrote:
> >
> > > Not really all that difficult.
> > > Instead of using the dropdowns (combo boxes) as bound controls, make them
> > > unbound and only use them for searching for values. Create regular text
> > > boxes to display the data in your Work Order table.
> > > You can get the value you look up in the combo boxes into the text boxes and
> > > thus into your table by using the After Update event of the combos.
> > >
> > > If Not IsNull(Me.cboSomeField) Then
> > > Me.txtSomeField = me.cboSomeField
> > > End If
> > >
> > > "Jebuz" wrote:
> > >
> > > > Hi all, I have a question that's been bugging me for some time now.
> > > >
> > > > Basically I have a form for work orders. I have to be able to create new
> > > > orders and have some drop down lists that the user can use to choose the
> > > > information to be used for the new work order. This info is pulled from some
> > > > tables I have already storing a basic "inventory" in which the fields data
> > > > may change.
> > > >
> > > > The hard part I don't know how to do is how do I show the previous work
> > > > orders and show the data that was in the tables at the time the work order
> > > > was created. I know I need to have some "history logging" tables but how do
> > > > I get the same work order form to function 2 ways? in present and past data?
> > > > Or do I use 2 seperate forms, one for showing previous data and other for
> > > > creating new order from current data??
|
|
Well, it seems that some of the examples given don't make complete sense.
For example, if an employee's phone number changes, what good is having an
out of date phone number?
I think I would consider doing the changes in the employee and company
tables. First, I would add a Boolean field to each table that would be used
to designate the current record. Then when I had to make a change, rather
than update the table, I would create a new "current" record and mark the
other as history. If you are using Autonumbers for your relationships, this
will work well, because no matter how many changes are made to an employee or
a company record, the work order would still point the the record the way it
was when the work order was created.
That would mean that in the forms you use for maintaining the employee and
company tables, you would need to use a query filtering on the "current"
field so you would get only the current record. It would also mean that the
forms would become more complicated because you would have to know a change
has been made, append the table with the old record data and populate a new
record for the current record.
"Jebuz" wrote:
[Quoted Text] > That is actually how I have it set up now. I figured that must be the way to
> store the information in the tables. Here is the catch though. Say on the
> work order I want to display some of the 'employee' and 'company' fields,
> like person's name, phone # and company name and phone #. This all works
> great till I make a change to one or both of the tables. Say changing the
> company's name or employees phone #. Now I want the work order to display
> the "current" fields at the time of creation not the updated fields. I know
> this means I need some type of tables to track histories of 'employees' and
> 'companies' right? So how do I link that into the work order as well? So the
> work order table and forms work with previous data in history tables and
> current data in other tables when I want new work orders made?
>
> Do I make a history table for each 'employeehistory' and 'companyhistory'
> and in those have also the current records, but when the data changes mark
> the correspnding records to have a 'true' retired field?
>
> "Klatuu" wrote:
>
> > A work order record should contain the relationship to the employee table and
> > to the company table to keep historical accuracy. If Person A works for
> > company 1 and you create a work order, the work order record should contain
> > the primary key of the employee as a foreign key and the primary key of the
> > company as a foreign key. That work order record now reflects person A and
> > company 1. Person A goes to work for company 3. The new work order would
> > relate to person 1 (same person) and company 3 (different company). The
> > first work order would still show person A and company 1. It is a matter of
> > proper relationships.
> >
> > How you are describing it makes me think you have the work order related to
> > a person and the person related to the company. That is why you are having
> > the issue.
> >
> > The basic table lay out should be
> >
> > Work Order
> > WO_ID - Autonumber Primary key
> > EMP_ID - Long - Foreign key to employee table
> > COMP_ID Long - Foreign key to company table
> >
> > Employee
> > EMP_ID - Autonumber Primary key
> > COMP_ID - Long foreign key to company table
> >
> > Company
> > COMP_ID Autonumber Primary key
> >
> > "Jebuz" wrote:
> >
> > > That part makes sense but I guess my question is more of table design than
> > > form design. Say I have tables 'Employees' , 'Company', and 'Work Orders'.
> > > I go to create a new work order and the user selects person A, it fills in
> > > the company they work for automatically. I click OK , submit or whatever
> > > after I fill out the details of the work order. Sometime after person A goes
> > > to work for a different company, and now when I look up the work order it
> > > shows the new company for person A, not the old one. However I still need to
> > > be able to then create a new one and have person A fill in the company with
> > > the new one he went to work for. If that description makes any sense.
> > >
> > > So I guess it's how do I store previous relations between 'Employees' and
> > > 'Company' and show work orders created with those relations as well as when
> > > the user selects "new" work order it lists the current relations?
> > >
> > > it's like the work order form has to be bound to 2 different sets of tables
> > > depending on if the user is on a new record or not. *shrugs*
> > >
> > >
> > > "Klatuu" wrote:
> > >
> > > > Not really all that difficult.
> > > > Instead of using the dropdowns (combo boxes) as bound controls, make them
> > > > unbound and only use them for searching for values. Create regular text
> > > > boxes to display the data in your Work Order table.
> > > > You can get the value you look up in the combo boxes into the text boxes and
> > > > thus into your table by using the After Update event of the combos.
> > > >
> > > > If Not IsNull(Me.cboSomeField) Then
> > > > Me.txtSomeField = me.cboSomeField
> > > > End If
> > > >
> > > > "Jebuz" wrote:
> > > >
> > > > > Hi all, I have a question that's been bugging me for some time now.
> > > > >
> > > > > Basically I have a form for work orders. I have to be able to create new
> > > > > orders and have some drop down lists that the user can use to choose the
> > > > > information to be used for the new work order. This info is pulled from some
> > > > > tables I have already storing a basic "inventory" in which the fields data
> > > > > may change.
> > > > >
> > > > > The hard part I don't know how to do is how do I show the previous work
> > > > > orders and show the data that was in the tables at the time the work order
> > > > > was created. I know I need to have some "history logging" tables but how do
> > > > > I get the same work order form to function 2 ways? in present and past data?
> > > > > Or do I use 2 seperate forms, one for showing previous data and other for
> > > > > creating new order from current data??
|
|
Yea the 'employee' and 'company' tables were examples. I really am working
with 'sites' that have 'monitors' installed at them. Also each 'monitor' has
many 'sensors' connected to it. So I also have a table called
'monitor_configs' that shows what monitors have what sensors connected to
them.
The work order should display the site with the corresponding monitor and
it's configuration at the time of the work order, as to maintain a type of
history of configurations and work done to those configurations. The
'employee' and 'company' example is just easier to explain hehe.
But I think now I am pointed in the right way. I think whatever the
solution ends up being it will be complex no matter what ! I am going to
stick with the 'work order' table examples with foreign keys you suggested.
So it will probably involve creating some "history" tables and some complex
queries on some work order form! Thanks again for all your help, very much
appreciated!!!
"Klatuu" wrote:
[Quoted Text] > Well, it seems that some of the examples given don't make complete sense.
> For example, if an employee's phone number changes, what good is having an
> out of date phone number?
>
> I think I would consider doing the changes in the employee and company
> tables. First, I would add a Boolean field to each table that would be used
> to designate the current record. Then when I had to make a change, rather
> than update the table, I would create a new "current" record and mark the
> other as history. If you are using Autonumbers for your relationships, this
> will work well, because no matter how many changes are made to an employee or
> a company record, the work order would still point the the record the way it
> was when the work order was created.
>
> That would mean that in the forms you use for maintaining the employee and
> company tables, you would need to use a query filtering on the "current"
> field so you would get only the current record. It would also mean that the
> forms would become more complicated because you would have to know a change
> has been made, append the table with the old record data and populate a new
> record for the current record.
>
> "Jebuz" wrote:
>
> > That is actually how I have it set up now. I figured that must be the way to
> > store the information in the tables. Here is the catch though. Say on the
> > work order I want to display some of the 'employee' and 'company' fields,
> > like person's name, phone # and company name and phone #. This all works
> > great till I make a change to one or both of the tables. Say changing the
> > company's name or employees phone #. Now I want the work order to display
> > the "current" fields at the time of creation not the updated fields. I know
> > this means I need some type of tables to track histories of 'employees' and
> > 'companies' right? So how do I link that into the work order as well? So the
> > work order table and forms work with previous data in history tables and
> > current data in other tables when I want new work orders made?
> >
> > Do I make a history table for each 'employeehistory' and 'companyhistory'
> > and in those have also the current records, but when the data changes mark
> > the correspnding records to have a 'true' retired field?
> >
> > "Klatuu" wrote:
> >
> > > A work order record should contain the relationship to the employee table and
> > > to the company table to keep historical accuracy. If Person A works for
> > > company 1 and you create a work order, the work order record should contain
> > > the primary key of the employee as a foreign key and the primary key of the
> > > company as a foreign key. That work order record now reflects person A and
> > > company 1. Person A goes to work for company 3. The new work order would
> > > relate to person 1 (same person) and company 3 (different company). The
> > > first work order would still show person A and company 1. It is a matter of
> > > proper relationships.
> > >
> > > How you are describing it makes me think you have the work order related to
> > > a person and the person related to the company. That is why you are having
> > > the issue.
> > >
> > > The basic table lay out should be
> > >
> > > Work Order
> > > WO_ID - Autonumber Primary key
> > > EMP_ID - Long - Foreign key to employee table
> > > COMP_ID Long - Foreign key to company table
> > >
> > > Employee
> > > EMP_ID - Autonumber Primary key
> > > COMP_ID - Long foreign key to company table
> > >
> > > Company
> > > COMP_ID Autonumber Primary key
> > >
> > > "Jebuz" wrote:
> > >
> > > > That part makes sense but I guess my question is more of table design than
> > > > form design. Say I have tables 'Employees' , 'Company', and 'Work Orders'.
> > > > I go to create a new work order and the user selects person A, it fills in
> > > > the company they work for automatically. I click OK , submit or whatever
> > > > after I fill out the details of the work order. Sometime after person A goes
> > > > to work for a different company, and now when I look up the work order it
> > > > shows the new company for person A, not the old one. However I still need to
> > > > be able to then create a new one and have person A fill in the company with
> > > > the new one he went to work for. If that description makes any sense.
> > > >
> > > > So I guess it's how do I store previous relations between 'Employees' and
> > > > 'Company' and show work orders created with those relations as well as when
> > > > the user selects "new" work order it lists the current relations?
> > > >
> > > > it's like the work order form has to be bound to 2 different sets of tables
> > > > depending on if the user is on a new record or not. *shrugs*
> > > >
> > > >
> > > > "Klatuu" wrote:
> > > >
> > > > > Not really all that difficult.
> > > > > Instead of using the dropdowns (combo boxes) as bound controls, make them
> > > > > unbound and only use them for searching for values. Create regular text
> > > > > boxes to display the data in your Work Order table.
> > > > > You can get the value you look up in the combo boxes into the text boxes and
> > > > > thus into your table by using the After Update event of the combos.
> > > > >
> > > > > If Not IsNull(Me.cboSomeField) Then
> > > > > Me.txtSomeField = me.cboSomeField
> > > > > End If
> > > > >
> > > > > "Jebuz" wrote:
> > > > >
> > > > > > Hi all, I have a question that's been bugging me for some time now.
> > > > > >
> > > > > > Basically I have a form for work orders. I have to be able to create new
> > > > > > orders and have some drop down lists that the user can use to choose the
> > > > > > information to be used for the new work order. This info is pulled from some
> > > > > > tables I have already storing a basic "inventory" in which the fields data
> > > > > > may change.
> > > > > >
> > > > > > The hard part I don't know how to do is how do I show the previous work
> > > > > > orders and show the data that was in the tables at the time the work order
> > > > > > was created. I know I need to have some "history logging" tables but how do
> > > > > > I get the same work order form to function 2 ways? in present and past data?
> > > > > > Or do I use 2 seperate forms, one for showing previous data and other for
> > > > > > creating new order from current data??
|
|
Okay, the real world make a whole lot of sense.
As to making additional tables, that would only make it more complex. If
you use the approach I suggested, you don't need additional tables, you only
need to modify your data entry forms for the site and monitor tables.
Don't confuse how you store data with how you view it. Using the Current
Record flag I suggested and filtering on it depending on the circumstances
will take care of the rest of it.
For example, don't apply any filter at all for the work order form. That
way, the existing work orders will show the data the way it was when it was
entered.
For creating new work orders, you only need to filter any combos, etc that
are based on data in the site or monitor tables so you are getting the
current data.
Having one table with the flag in it is really no different, logically, than
a current table and a history table. The two tables are harder to manage.
Would it not be easier to set a filter than to change recordsets?
"Jebuz" wrote:
[Quoted Text] > Yea the 'employee' and 'company' tables were examples. I really am working
> with 'sites' that have 'monitors' installed at them. Also each 'monitor' has
> many 'sensors' connected to it. So I also have a table called
> 'monitor_configs' that shows what monitors have what sensors connected to
> them.
>
> The work order should display the site with the corresponding monitor and
> it's configuration at the time of the work order, as to maintain a type of
> history of configurations and work done to those configurations. The
> 'employee' and 'company' example is just easier to explain hehe.
>
> But I think now I am pointed in the right way. I think whatever the
> solution ends up being it will be complex no matter what ! I am going to
> stick with the 'work order' table examples with foreign keys you suggested.
> So it will probably involve creating some "history" tables and some complex
> queries on some work order form! Thanks again for all your help, very much
> appreciated!!!
>
> "Klatuu" wrote:
>
> > Well, it seems that some of the examples given don't make complete sense.
> > For example, if an employee's phone number changes, what good is having an
> > out of date phone number?
> >
> > I think I would consider doing the changes in the employee and company
> > tables. First, I would add a Boolean field to each table that would be used
> > to designate the current record. Then when I had to make a change, rather
> > than update the table, I would create a new "current" record and mark the
> > other as history. If you are using Autonumbers for your relationships, this
> > will work well, because no matter how many changes are made to an employee or
> > a company record, the work order would still point the the record the way it
> > was when the work order was created.
> >
> > That would mean that in the forms you use for maintaining the employee and
> > company tables, you would need to use a query filtering on the "current"
> > field so you would get only the current record. It would also mean that the
> > forms would become more complicated because you would have to know a change
> > has been made, append the table with the old record data and populate a new
> > record for the current record.
> >
> > "Jebuz" wrote:
> >
> > > That is actually how I have it set up now. I figured that must be the way to
> > > store the information in the tables. Here is the catch though. Say on the
> > > work order I want to display some of the 'employee' and 'company' fields,
> > > like person's name, phone # and company name and phone #. This all works
> > > great till I make a change to one or both of the tables. Say changing the
> > > company's name or employees phone #. Now I want the work order to display
> > > the "current" fields at the time of creation not the updated fields. I know
> > > this means I need some type of tables to track histories of 'employees' and
> > > 'companies' right? So how do I link that into the work order as well? So the
> > > work order table and forms work with previous data in history tables and
> > > current data in other tables when I want new work orders made?
> > >
> > > Do I make a history table for each 'employeehistory' and 'companyhistory'
> > > and in those have also the current records, but when the data changes mark
> > > the correspnding records to have a 'true' retired field?
> > >
> > > "Klatuu" wrote:
> > >
> > > > A work order record should contain the relationship to the employee table and
> > > > to the company table to keep historical accuracy. If Person A works for
> > > > company 1 and you create a work order, the work order record should contain
> > > > the primary key of the employee as a foreign key and the primary key of the
> > > > company as a foreign key. That work order record now reflects person A and
> > > > company 1. Person A goes to work for company 3. The new work order would
> > > > relate to person 1 (same person) and company 3 (different company). The
> > > > first work order would still show person A and company 1. It is a matter of
> > > > proper relationships.
> > > >
> > > > How you are describing it makes me think you have the work order related to
> > > > a person and the person related to the company. That is why you are having
> > > > the issue.
> > > >
> > > > The basic table lay out should be
> > > >
> > > > Work Order
> > > > WO_ID - Autonumber Primary key
> > > > EMP_ID - Long - Foreign key to employee table
> > > > COMP_ID Long - Foreign key to company table
> > > >
> > > > Employee
> > > > EMP_ID - Autonumber Primary key
> > > > COMP_ID - Long foreign key to company table
> > > >
> > > > Company
> > > > COMP_ID Autonumber Primary key
> > > >
> > > > "Jebuz" wrote:
> > > >
> > > > > That part makes sense but I guess my question is more of table design than
> > > > > form design. Say I have tables 'Employees' , 'Company', and 'Work Orders'.
> > > > > I go to create a new work order and the user selects person A, it fills in
> > > > > the company they work for automatically. I click OK , submit or whatever
> > > > > after I fill out the details of the work order. Sometime after person A goes
> > > > > to work for a different company, and now when I look up the work order it
> > > > > shows the new company for person A, not the old one. However I still need to
> > > > > be able to then create a new one and have person A fill in the company with
> > > > > the new one he went to work for. If that description makes any sense.
> > > > >
> > > > > So I guess it's how do I store previous relations between 'Employees' and
> > > > > 'Company' and show work orders created with those relations as well as when
> > > > > the user selects "new" work order it lists the current relations?
> > > > >
> > > > > it's like the work order form has to be bound to 2 different sets of tables
> > > > > depending on if the user is on a new record or not. *shrugs*
> > > > >
> > > > >
> > > > > "Klatuu" wrote:
> > > > >
> > > > > > Not really all that difficult.
> > > > > > Instead of using the dropdowns (combo boxes) as bound controls, make them
> > > > > > unbound and only use them for searching for values. Create regular text
> > > > > > boxes to display the data in your Work Order table.
> > > > > > You can get the value you look up in the combo boxes into the text boxes and
> > > > > > thus into your table by using the After Update event of the combos.
> > > > > >
> > > > > > If Not IsNull(Me.cboSomeField) Then
> > > > > > Me.txtSomeField = me.cboSomeField
> > > > > > End If
> > > > > >
> > > > > > "Jebuz" wrote:
> > > > > >
> > > > > > > Hi all, I have a question that's been bugging me for some time now.
> > > > > > >
> > > > > > > Basically I have a form for work orders. I have to be able to create new
> > > > > > > orders and have some drop down lists that the user can use to choose the
> > > > > > > information to be used for the new work order. This info is pulled from some
> > > > > > > tables I have already storing a basic "inventory" in which the fields data
> > > > > > > may change.
> > > > > > >
> > > > > > > The hard part I don't know how to do is how do I show the previous work
> > > > > > > orders and show the data that was in the tables at the time the work order
> > > > > > > was created. I know I need to have some "history logging" tables but how do
> > > > > > > I get the same work order form to function 2 ways? in present and past data?
> > > > > > > Or do I use 2 seperate forms, one for showing previous data and other for
> > > > > > > creating new order from current data??
|
|
Yea I guess that is true, it would add to the complexity. I think I will do
as you suggested, which means I have to use an auto-number or multi-field key
and not the serial #'s of monitors for keys, since I will have multiple
entries for each monitor/site. But that sounds much easier of a fix then
adding more unneccessary tables. Thanks again!! Now just to implement that
*sigh* haha
"Klatuu" wrote:
[Quoted Text] > Okay, the real world make a whole lot of sense.
> As to making additional tables, that would only make it more complex. If
> you use the approach I suggested, you don't need additional tables, you only
> need to modify your data entry forms for the site and monitor tables.
>
> Don't confuse how you store data with how you view it. Using the Current
> Record flag I suggested and filtering on it depending on the circumstances
> will take care of the rest of it.
>
> For example, don't apply any filter at all for the work order form. That
> way, the existing work orders will show the data the way it was when it was
> entered.
>
> For creating new work orders, you only need to filter any combos, etc that
> are based on data in the site or monitor tables so you are getting the
> current data.
>
> Having one table with the flag in it is really no different, logically, than
> a current table and a history table. The two tables are harder to manage.
> Would it not be easier to set a filter than to change recordsets?
> "Jebuz" wrote:
>
> > Yea the 'employee' and 'company' tables were examples. I really am working
> > with 'sites' that have 'monitors' installed at them. Also each 'monitor' has
> > many 'sensors' connected to it. So I also have a table called
> > 'monitor_configs' that shows what monitors have what sensors connected to
> > them.
> >
> > The work order should display the site with the corresponding monitor and
> > it's configuration at the time of the work order, as to maintain a type of
> > history of configurations and work done to those configurations. The
> > 'employee' and 'company' example is just easier to explain hehe.
> >
> > But I think now I am pointed in the right way. I think whatever the
> > solution ends up being it will be complex no matter what ! I am going to
> > stick with the 'work order' table examples with foreign keys you suggested.
> > So it will probably involve creating some "history" tables and some complex
> > queries on some work order form! Thanks again for all your help, very much
> > appreciated!!!
> >
> > "Klatuu" wrote:
> >
> > > Well, it seems that some of the examples given don't make complete sense.
> > > For example, if an employee's phone number changes, what good is having an
> > > out of date phone number?
> > >
> > > I think I would consider doing the changes in the employee and company
> > > tables. First, I would add a Boolean field to each table that would be used
> > > to designate the current record. Then when I had to make a change, rather
> > > than update the table, I would create a new "current" record and mark the
> > > other as history. If you are using Autonumbers for your relationships, this
> > > will work well, because no matter how many changes are made to an employee or
> > > a company record, the work order would still point the the record the way it
> > > was when the work order was created.
> > >
> > > That would mean that in the forms you use for maintaining the employee and
> > > company tables, you would need to use a query filtering on the "current"
> > > field so you would get only the current record. It would also mean that the
> > > forms would become more complicated because you would have to know a change
> > > has been made, append the table with the old record data and populate a new
> > > record for the current record.
> > >
> > > "Jebuz" wrote:
> > >
> > > > That is actually how I have it set up now. I figured that must be the way to
> > > > store the information in the tables. Here is the catch though. Say on the
> > > > work order I want to display some of the 'employee' and 'company' fields,
> > > > like person's name, phone # and company name and phone #. This all works
> > > > great till I make a change to one or both of the tables. Say changing the
> > > > company's name or employees phone #. Now I want the work order to display
> > > > the "current" fields at the time of creation not the updated fields. I know
> > > > this means I need some type of tables to track histories of 'employees' and
> > > > 'companies' right? So how do I link that into the work order as well? So the
> > > > work order table and forms work with previous data in history tables and
> > > > current data in other tables when I want new work orders made?
> > > >
> > > > Do I make a history table for each 'employeehistory' and 'companyhistory'
> > > > and in those have also the current records, but when the data changes mark
> > > > the correspnding records to have a 'true' retired field?
> > > >
> > > > "Klatuu" wrote:
> > > >
> > > > > A work order record should contain the relationship to the employee table and
> > > > > to the company table to keep historical accuracy. If Person A works for
> > > > > company 1 and you create a work order, the work order record should contain
> > > > > the primary key of the employee as a foreign key and the primary key of the
> > > > > company as a foreign key. That work order record now reflects person A and
> > > > > company 1. Person A goes to work for company 3. The new work order would
> > > > > relate to person 1 (same person) and company 3 (different company). The
> > > > > first work order would still show person A and company 1. It is a matter of
> > > > > proper relationships.
> > > > >
> > > > > How you are describing it makes me think you have the work order related to
> > > > > a person and the person related to the company. That is why you are having
> > > > > the issue.
> > > > >
> > > > > The basic table lay out should be
> > > > >
> > > > > Work Order
> > > > > WO_ID - Autonumber Primary key
> > > > > EMP_ID - Long - Foreign key to employee table
> > > > > COMP_ID Long - Foreign key to company table
> > > > >
> > > > > Employee
> > > > > EMP_ID - Autonumber Primary key
> > > > > COMP_ID - Long foreign key to company table
> > > > >
> > > > > Company
> > > > > COMP_ID Autonumber Primary key
> > > > >
> > > > > "Jebuz" wrote:
> > > > >
> > > > > > That part makes sense but I guess my question is more of table design than
> > > > > > form design. Say I have tables 'Employees' , 'Company', and 'Work Orders'.
> > > > > > I go to create a new work order and the user selects person A, it fills in
> > > > > > the company they work for automatically. I click OK , submit or whatever
> > > > > > after I fill out the details of the work order. Sometime after person A goes
> > > > > > to work for a different company, and now when I look up the work order it
> > > > > > shows the new company for person A, not the old one. However I still need to
> > > > > > be able to then create a new one and have person A fill in the company with
> > > > > > the new one he went to work for. If that description makes any sense.
> > > > > >
> > > > > > So I guess it's how do I store previous relations between 'Employees' and
> > > > > > 'Company' and show work orders created with those relations as well as when
> > > > > > the user selects "new" work order it lists the current relations?
> > > > > >
> > > > > > it's like the work order form has to be bound to 2 different sets of tables
> > > > > > depending on if the user is on a new record or not. *shrugs*
> > > > > >
> > > > > >
> > > > > > "Klatuu" wrote:
> > > > > >
> > > > > > > Not really all that difficult.
> > > > > > > Instead of using the dropdowns (combo boxes) as bound controls, make them
> > > > > > > unbound and only use them for searching for values. Create regular text
> > > > > > > boxes to display the data in your Work Order table.
> > > > > > > You can get the value you look up in the combo boxes into the text boxes and
> > > > > > > thus into your table by using the After Update event of the combos.
> > > > > > >
> > > > > > > If Not IsNull(Me.cboSomeField) Then
> > > > > > > Me.txtSomeField = me.cboSomeField
> > > > > > > End If
> > > > > > >
> > > > > > > "Jebuz" wrote:
> > > > > > >
> > > > > > > > Hi all, I have a question that's been bugging me for some time now.
> > > > > > > >
> > > > > > > > Basically I have a form for work orders. I have to be able to create new
> > > > > > > > orders and have some drop down lists that the user can use to choose the
> > > > > > > > information to be used for the new work order. This info is pulled from some
> > > > > > > > tables I have already storing a basic "inventory" in which the fields data
> > > > > > > > may change.
> > > > > > > >
> > > > > > > > The hard part I don't know how to do is how do I show the previous work
> > > > > > > > orders and show the data that was in the tables at the time the work order
> > > > > > > > was created. I know I need to have some "history logging" tables but how do
> > > > > > > > I get the same work order form to function 2 ways? in present and past data?
> > > > > > > > Or do I use 2 seperate forms, one for showing previous data and other for
> > > > > > > > creating new order from current data??
|
|
You are on the right track, now.
The Autonumber is the way to go.
The only thing you will need to work out is aligning your existing data, but
that is no big deal.
Onward and Upward! :)
"Jebuz" wrote:
[Quoted Text] > Yea I guess that is true, it would add to the complexity. I think I will do
> as you suggested, which means I have to use an auto-number or multi-field key
> and not the serial #'s of monitors for keys, since I will have multiple
> entries for each monitor/site. But that sounds much easier of a fix then
> adding more unneccessary tables. Thanks again!! Now just to implement that
> *sigh* haha
>
> "Klatuu" wrote:
>
> > Okay, the real world make a whole lot of sense.
> > As to making additional tables, that would only make it more complex. If
> > you use the approach I suggested, you don't need additional tables, you only
> > need to modify your data entry forms for the site and monitor tables.
> >
> > Don't confuse how you store data with how you view it. Using the Current
> > Record flag I suggested and filtering on it depending on the circumstances
> > will take care of the rest of it.
> >
> > For example, don't apply any filter at all for the work order form. That
> > way, the existing work orders will show the data the way it was when it was
> > entered.
> >
> > For creating new work orders, you only need to filter any combos, etc that
> > are based on data in the site or monitor tables so you are getting the
> > current data.
> >
> > Having one table with the flag in it is really no different, logically, than
> > a current table and a history table. The two tables are harder to manage.
> > Would it not be easier to set a filter than to change recordsets?
> > "Jebuz" wrote:
> >
> > > Yea the 'employee' and 'company' tables were examples. I really am working
> > > with 'sites' that have 'monitors' installed at them. Also each 'monitor' has
> > > many 'sensors' connected to it. So I also have a table called
> > > 'monitor_configs' that shows what monitors have what sensors connected to
> > > them.
> > >
> > > The work order should display the site with the corresponding monitor and
> > > it's configuration at the time of the work order, as to maintain a type of
> > > history of configurations and work done to those configurations. The
> > > 'employee' and 'company' example is just easier to explain hehe.
> > >
> > > But I think now I am pointed in the right way. I think whatever the
> > > solution ends up being it will be complex no matter what ! I am going to
> > > stick with the 'work order' table examples with foreign keys you suggested.
> > > So it will probably involve creating some "history" tables and some complex
> > > queries on some work order form! Thanks again for all your help, very much
> > > appreciated!!!
> > >
> > > "Klatuu" wrote:
> > >
> > > > Well, it seems that some of the examples given don't make complete sense.
> > > > For example, if an employee's phone number changes, what good is having an
> > > > out of date phone number?
> > > >
> > > > I think I would consider doing the changes in the employee and company
> > > > tables. First, I would add a Boolean field to each table that would be used
> > > > to designate the current record. Then when I had to make a change, rather
> > > > than update the table, I would create a new "current" record and mark the
> > > > other as history. If you are using Autonumbers for your relationships, this
> > > > will work well, because no matter how many changes are made to an employee or
> > > > a company record, the work order would still point the the record the way it
> > > > was when the work order was created.
> > > >
> > > > That would mean that in the forms you use for maintaining the employee and
> > > > company tables, you would need to use a query filtering on the "current"
> > > > field so you would get only the current record. It would also mean that the
> > > > forms would become more complicated because you would have to know a change
> > > > has been made, append the table with the old record data and populate a new
> > > > record for the current record.
> > > >
> > > > "Jebuz" wrote:
> > > >
> > > > > That is actually how I have it set up now. I figured that must be the way to
> > > > > store the information in the tables. Here is the catch though. Say on the
> > > > > work order I want to display some of the 'employee' and 'company' fields,
> > > > > like person's name, phone # and company name and phone #. This all works
> > > > > great till I make a change to one or both of the tables. Say changing the
> > > > > company's name or employees phone #. Now I want the work order to display
> > > > > the "current" fields at the time of creation not the updated fields. I know
> > > > > this means I need some type of tables to track histories of 'employees' and
> > > > > 'companies' right? So how do I link that into the work order as well? So the
> > > > > work order table and forms work with previous data in history tables and
> > > > > current data in other tables when I want new work orders made?
> > > > >
> > > > > Do I make a history table for each 'employeehistory' and 'companyhistory'
> > > > > and in those have also the current records, but when the data changes mark
> > > > > the correspnding records to have a 'true' retired field?
> > > > >
> > > > > "Klatuu" wrote:
> > > > >
> > > > > > A work order record should contain the relationship to the employee table and
> > > > > > to the company table to keep historical accuracy. If Person A works for
> > > > > > company 1 and you create a work order, the work order record should contain
> > > > > > the primary key of the employee as a foreign key and the primary key of the
> > > > > > company as a foreign key. That work order record now reflects person A and
> > > > > > company 1. Person A goes to work for company 3. The new work order would
> > > > > > relate to person 1 (same person) and company 3 (different company). The
> > > > > > first work order would still show person A and company 1. It is a matter of
> > > > > > proper relationships.
> > > > > >
> > > > > > How you are describing it makes me think you have the work order related to
> > > > > > a person and the person related to the company. That is why you are having
> > > > > > the issue.
> > > > > >
> > > > > > The basic table lay out should be
> > > > > >
> > > > > > Work Order
> > > > > > WO_ID - Autonumber Primary key
> > > > > > EMP_ID - Long - Foreign key to employee table
> > > > > > COMP_ID Long - Foreign key to company table
> > > > > >
> > > > > > Employee
> > > > > > EMP_ID - Autonumber Primary key
> > > > > > COMP_ID - Long foreign key to company table
> > > > > >
> > > > > > Company
> > > > > > COMP_ID Autonumber Primary key
> > > > > >
> > > > > > "Jebuz" wrote:
> > > > > >
> > > > > > > That part makes sense but I guess my question is more of table design than
> > > > > > > form design. Say I have tables 'Employees' , 'Company', and 'Work Orders'.
> > > > > > > I go to create a new work order and the user selects person A, it fills in
> > > > > > > the company they work for automatically. I click OK , submit or whatever
> > > > > > > after I fill out the details of the work order. Sometime after person A goes
> > > > > > > to work for a different company, and now when I look up the work order it
> > > > > > > shows the new company for person A, not the old one. However I still need to
> > > > > > > be able to then create a new one and have person A fill in the company with
> > > > > > > the new one he went to work for. If that description makes any sense.
> > > > > > >
> > > > > > > So I guess it's how do I store previous relations between 'Employees' and
> > > > > > > 'Company' and show work orders created with those relations as well as when
> > > > > > > the user selects "new" work order it lists the current relations?
> > > > > > >
> > > > > > > it's like the work order form has to be bound to 2 different sets of tables
> > > > > > > depending on if the user is on a new record or not. *shrugs*
> > > > > > >
> > > > > > >
> > > > > > > "Klatuu" wrote:
> > > > > > >
> > > > > > > > Not really all that difficult.
> > > > > > > > Instead of using the dropdowns (combo boxes) as bound controls, make them
> > > > > > > > unbound and only use them for searching for values. Create regular text
> > > > > > > > boxes to display the data in your Work Order table.
> > > > > > > > You can get the value you look up in the combo boxes into the text boxes and
> > > > > > > > thus into your table by using the After Update event of the combos.
> > > > > > > >
> > > > > > > > If Not IsNull(Me.cboSomeField) Then
> > > > > > > > Me.txtSomeField = me.cboSomeField
> > > > > > > > End If
> > > > > > > >
> > > > > > > > "Jebuz" wrote:
> > > > > > > >
> > > > > > > > > Hi all, I have a question that's been bugging me for some time now.
> > > > > > > > >
> > > > > > > > > Basically I have a form for work orders. I have to be able to create new
> > > > > > > > > orders and have some drop down lists that the user can use to choose the
> > > > > > > > > information to be used for the new work order. This info is pulled from some
> > > > > > > > > tables I have already storing a basic "inventory" in which the fields data
> > > > > > > > > may change.
> > > > > > > > >
> > > > > > > > > The hard part I don't know how to do is how do I show the previous work
> > > > > > > > > orders and show the data that was in the tables at the time the work order
> > > > > > > > > was created. I know I need to have some "history logging" tables but how do
> > > > > > > > > I get the same work order form to function 2 ways? in present and past data?
> > > > > > > > > Or do I use 2 seperate forms, one for showing previous data and other for
> > > > > > > > > creating new order from current data??
|
|
|