If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Please Help?? Auto Update Field in table
Good Morning,
I have a table 'Work Stations' which inlcudes the fields 'Work Station ID & Work Station'. These fields are both refered to in another table, what I would like to happen is have them update each other if one of them is changed in this table. How would I go about this? Thanx in advance.... -- Regards Ashley Smart |
#2
|
|||
|
|||
Please Help?? Auto Update Field in table
Ashley
Are you saying you have the same data in two different tables? And that you want the (same) data in Table2 to update when the data in Table1 changes? If so, DON'T! Access is a relational database -- it's neither a good idea nor necessary to record the same fact (e.g., WorkStation) more than once (say, in a WorkStation table). From then on, you can use the WorkStationID to point back to the information about the WorkStation. Use a query to do this. -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Good Morning, I have a table 'Work Stations' which inlcudes the fields 'Work Station ID & Work Station'. These fields are both refered to in another table, what I would like to happen is have them update each other if one of them is changed in this table. How would I go about this? Thanx in advance.... -- Regards Ashley Smart |
#3
|
|||
|
|||
Please Help?? Auto Update Field in table
The Table 'WorkStation' is a list of Work Stations (11 in Total) each with
there 'Work Station ID' number. The other table is a table of all the jobs performed in our workshop which, therefore the table 'WorkStation' and 'WorkStation ID' are referred to here in a combo box manor. I need them to match up with each other though???? i.e. 'Work Station ID' 1 = 'Work Station' GU010 Body Fit 1 Cheers -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley Are you saying you have the same data in two different tables? And that you want the (same) data in Table2 to update when the data in Table1 changes? If so, DON'T! Access is a relational database -- it's neither a good idea nor necessary to record the same fact (e.g., WorkStation) more than once (say, in a WorkStation table). From then on, you can use the WorkStationID to point back to the information about the WorkStation. Use a query to do this. -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Good Morning, I have a table 'Work Stations' which inlcudes the fields 'Work Station ID & Work Station'. These fields are both refered to in another table, what I would like to happen is have them update each other if one of them is changed in this table. How would I go about this? Thanx in advance.... -- Regards Ashley Smart |
#4
|
|||
|
|||
Please Help?? Auto Update Field in table
Ashley
I don't understand what you mean by having the tables related in a combo box manner. Does this mean your JobsPerformed table uses a lookup field for WorkStation? If so, DON'T! A quick scan of the tablesdbdesign newsgroup will reveal a strong consensus against using lookup data types in table definitions. Suffice it to say that having a field that stores one thing (the ID) but displays something else (?your WorkStation?) leads to considerable confusion in working with it. Your JobsPerformed table can easily (and relationally) get by with something like: JobsPerformed JobsPerformedID WorkStationID DatePerformed Description ... (plus any other fields you use to describe jobs performed) -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... The Table 'WorkStation' is a list of Work Stations (11 in Total) each with there 'Work Station ID' number. The other table is a table of all the jobs performed in our workshop which, therefore the table 'WorkStation' and 'WorkStation ID' are referred to here in a combo box manor. I need them to match up with each other though???? i.e. 'Work Station ID' 1 = 'Work Station' GU010 Body Fit 1 Cheers -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley Are you saying you have the same data in two different tables? And that you want the (same) data in Table2 to update when the data in Table1 changes? If so, DON'T! Access is a relational database -- it's neither a good idea nor necessary to record the same fact (e.g., WorkStation) more than once (say, in a WorkStation table). From then on, you can use the WorkStationID to point back to the information about the WorkStation. Use a query to do this. -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Good Morning, I have a table 'Work Stations' which inlcudes the fields 'Work Station ID & Work Station'. These fields are both refered to in another table, what I would like to happen is have them update each other if one of them is changed in this table. How would I go about this? Thanx in advance.... -- Regards Ashley Smart |
#5
|
|||
|
|||
Please Help?? Auto Update Field in table
Then I think we have a problem......
Originally I had just the table 'Work Stations' with only the field 'Work Station'. The other'JobPerformed table would lookup the work stations from that table. I then introduced 'Work Station ID' because I wanted to control a combo box using another combo box in a form.... see example at http://www.databasedev.co.uk/filter_combo_boxes.html Not sure what to do now???? -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley I don't understand what you mean by having the tables related in a combo box manner. Does this mean your JobsPerformed table uses a lookup field for WorkStation? If so, DON'T! A quick scan of the tablesdbdesign newsgroup will reveal a strong consensus against using lookup data types in table definitions. Suffice it to say that having a field that stores one thing (the ID) but displays something else (?your WorkStation?) leads to considerable confusion in working with it. Your JobsPerformed table can easily (and relationally) get by with something like: JobsPerformed JobsPerformedID WorkStationID DatePerformed Description ... (plus any other fields you use to describe jobs performed) -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... The Table 'WorkStation' is a list of Work Stations (11 in Total) each with there 'Work Station ID' number. The other table is a table of all the jobs performed in our workshop which, therefore the table 'WorkStation' and 'WorkStation ID' are referred to here in a combo box manor. I need them to match up with each other though???? i.e. 'Work Station ID' 1 = 'Work Station' GU010 Body Fit 1 Cheers -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley Are you saying you have the same data in two different tables? And that you want the (same) data in Table2 to update when the data in Table1 changes? If so, DON'T! Access is a relational database -- it's neither a good idea nor necessary to record the same fact (e.g., WorkStation) more than once (say, in a WorkStation table). From then on, you can use the WorkStationID to point back to the information about the WorkStation. Use a query to do this. -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Good Morning, I have a table 'Work Stations' which inlcudes the fields 'Work Station ID & Work Station'. These fields are both refered to in another table, what I would like to happen is have them update each other if one of them is changed in this table. How would I go about this? Thanx in advance.... -- Regards Ashley Smart |
#6
|
|||
|
|||
Please Help?? Auto Update Field in table
I'm having trouble visualizing what you have.
Would you be willing to describe your table structure, along the lines of what my previous post used? It might help me get a clearer picture of what you have (and what you are trying to do). -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Then I think we have a problem...... Originally I had just the table 'Work Stations' with only the field 'Work Station'. The other'JobPerformed table would lookup the work stations from that table. I then introduced 'Work Station ID' because I wanted to control a combo box using another combo box in a form.... see example at http://www.databasedev.co.uk/filter_combo_boxes.html Not sure what to do now???? -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley I don't understand what you mean by having the tables related in a combo box manner. Does this mean your JobsPerformed table uses a lookup field for WorkStation? If so, DON'T! A quick scan of the tablesdbdesign newsgroup will reveal a strong consensus against using lookup data types in table definitions. Suffice it to say that having a field that stores one thing (the ID) but displays something else (?your WorkStation?) leads to considerable confusion in working with it. Your JobsPerformed table can easily (and relationally) get by with something like: JobsPerformed JobsPerformedID WorkStationID DatePerformed Description ... (plus any other fields you use to describe jobs performed) -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... The Table 'WorkStation' is a list of Work Stations (11 in Total) each with there 'Work Station ID' number. The other table is a table of all the jobs performed in our workshop which, therefore the table 'WorkStation' and 'WorkStation ID' are referred to here in a combo box manor. I need them to match up with each other though???? i.e. 'Work Station ID' 1 = 'Work Station' GU010 Body Fit 1 Cheers -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley Are you saying you have the same data in two different tables? And that you want the (same) data in Table2 to update when the data in Table1 changes? If so, DON'T! Access is a relational database -- it's neither a good idea nor necessary to record the same fact (e.g., WorkStation) more than once (say, in a WorkStation table). From then on, you can use the WorkStationID to point back to the information about the WorkStation. Use a query to do this. -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Good Morning, I have a table 'Work Stations' which inlcudes the fields 'Work Station ID & Work Station'. These fields are both refered to in another table, what I would like to happen is have them update each other if one of them is changed in this table. How would I go about this? Thanx in advance.... -- Regards Ashley Smart |
#7
|
|||
|
|||
Please Help?? Auto Update Field in table
Tables
Job Number Job No ID Job No Job Title ??? Work Station ??? Work Station ID Issue No Issue Date Work Instruction (Word Document) Notes Work Station Work Station ID Work Station A form Job Number is used to enter all the data (except Work Station ID) into the table job number, the work station is selected from a combo box. Another form is then used to view the work instruction, the user selects a Work Station from a combo box and then all the Work Instructions are displayed in a list box for that Work Station. This is why I introduced Work Station ID. Perhaps I should just have Work Station ID in the table job number??? With a bit of SQL in the form job number instead??? Thanx -- Regards Ashley Smart "Jeff Boyce" wrote: I'm having trouble visualizing what you have. Would you be willing to describe your table structure, along the lines of what my previous post used? It might help me get a clearer picture of what you have (and what you are trying to do). -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Then I think we have a problem...... Originally I had just the table 'Work Stations' with only the field 'Work Station'. The other'JobPerformed table would lookup the work stations from that table. I then introduced 'Work Station ID' because I wanted to control a combo box using another combo box in a form.... see example at http://www.databasedev.co.uk/filter_combo_boxes.html Not sure what to do now???? -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley I don't understand what you mean by having the tables related in a combo box manner. Does this mean your JobsPerformed table uses a lookup field for WorkStation? If so, DON'T! A quick scan of the tablesdbdesign newsgroup will reveal a strong consensus against using lookup data types in table definitions. Suffice it to say that having a field that stores one thing (the ID) but displays something else (?your WorkStation?) leads to considerable confusion in working with it. Your JobsPerformed table can easily (and relationally) get by with something like: JobsPerformed JobsPerformedID WorkStationID DatePerformed Description ... (plus any other fields you use to describe jobs performed) -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... The Table 'WorkStation' is a list of Work Stations (11 in Total) each with there 'Work Station ID' number. The other table is a table of all the jobs performed in our workshop which, therefore the table 'WorkStation' and 'WorkStation ID' are referred to here in a combo box manor. I need them to match up with each other though???? i.e. 'Work Station ID' 1 = 'Work Station' GU010 Body Fit 1 Cheers -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley Are you saying you have the same data in two different tables? And that you want the (same) data in Table2 to update when the data in Table1 changes? If so, DON'T! Access is a relational database -- it's neither a good idea nor necessary to record the same fact (e.g., WorkStation) more than once (say, in a WorkStation table). From then on, you can use the WorkStationID to point back to the information about the WorkStation. Use a query to do this. -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Good Morning, I have a table 'Work Stations' which inlcudes the fields 'Work Station ID & Work Station'. These fields are both refered to in another table, what I would like to happen is have them update each other if one of them is changed in this table. How would I go about this? Thanx in advance.... -- Regards Ashley Smart |
#8
|
|||
|
|||
Please Help?? Auto Update Field in table
Ashley
Yes, I think that's what I was sensing, that you had "duplicated" the WorkStation field in your JobNumber table. No need to, since you already know [WorkStation] if you have [WorkStationID]. -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Tables Job Number Job No ID Job No Job Title ??? Work Station ??? Work Station ID Issue No Issue Date Work Instruction (Word Document) Notes Work Station Work Station ID Work Station A form Job Number is used to enter all the data (except Work Station ID) into the table job number, the work station is selected from a combo box. Another form is then used to view the work instruction, the user selects a Work Station from a combo box and then all the Work Instructions are displayed in a list box for that Work Station. This is why I introduced Work Station ID. Perhaps I should just have Work Station ID in the table job number??? With a bit of SQL in the form job number instead??? Thanx -- Regards Ashley Smart "Jeff Boyce" wrote: I'm having trouble visualizing what you have. Would you be willing to describe your table structure, along the lines of what my previous post used? It might help me get a clearer picture of what you have (and what you are trying to do). -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Then I think we have a problem...... Originally I had just the table 'Work Stations' with only the field 'Work Station'. The other'JobPerformed table would lookup the work stations from that table. I then introduced 'Work Station ID' because I wanted to control a combo box using another combo box in a form.... see example at http://www.databasedev.co.uk/filter_combo_boxes.html Not sure what to do now???? -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley I don't understand what you mean by having the tables related in a combo box manner. Does this mean your JobsPerformed table uses a lookup field for WorkStation? If so, DON'T! A quick scan of the tablesdbdesign newsgroup will reveal a strong consensus against using lookup data types in table definitions. Suffice it to say that having a field that stores one thing (the ID) but displays something else (?your WorkStation?) leads to considerable confusion in working with it. Your JobsPerformed table can easily (and relationally) get by with something like: JobsPerformed JobsPerformedID WorkStationID DatePerformed Description ... (plus any other fields you use to describe jobs performed) -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... The Table 'WorkStation' is a list of Work Stations (11 in Total) each with there 'Work Station ID' number. The other table is a table of all the jobs performed in our workshop which, therefore the table 'WorkStation' and 'WorkStation ID' are referred to here in a combo box manor. I need them to match up with each other though???? i.e. 'Work Station ID' 1 = 'Work Station' GU010 Body Fit 1 Cheers -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley Are you saying you have the same data in two different tables? And that you want the (same) data in Table2 to update when the data in Table1 changes? If so, DON'T! Access is a relational database -- it's neither a good idea nor necessary to record the same fact (e.g., WorkStation) more than once (say, in a WorkStation table). From then on, you can use the WorkStationID to point back to the information about the WorkStation. Use a query to do this. -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Good Morning, I have a table 'Work Stations' which inlcudes the fields 'Work Station ID & Work Station'. These fields are both refered to in another table, what I would like to happen is have them update each other if one of them is changed in this table. How would I go about this? Thanx in advance.... -- Regards Ashley Smart |
#9
|
|||
|
|||
Please Help?? Auto Update Field in table
I've sorted it I removed 'Work Station' from table 'Job Number'. had a lot of
changes to make but all works fine now..... I'll bear that in mind next time i'm designing a database. Thanx for all your help Jeff. -- Regards Ashley Smart "Jeff Boyce" wrote: I'm having trouble visualizing what you have. Would you be willing to describe your table structure, along the lines of what my previous post used? It might help me get a clearer picture of what you have (and what you are trying to do). -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Then I think we have a problem...... Originally I had just the table 'Work Stations' with only the field 'Work Station'. The other'JobPerformed table would lookup the work stations from that table. I then introduced 'Work Station ID' because I wanted to control a combo box using another combo box in a form.... see example at http://www.databasedev.co.uk/filter_combo_boxes.html Not sure what to do now???? -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley I don't understand what you mean by having the tables related in a combo box manner. Does this mean your JobsPerformed table uses a lookup field for WorkStation? If so, DON'T! A quick scan of the tablesdbdesign newsgroup will reveal a strong consensus against using lookup data types in table definitions. Suffice it to say that having a field that stores one thing (the ID) but displays something else (?your WorkStation?) leads to considerable confusion in working with it. Your JobsPerformed table can easily (and relationally) get by with something like: JobsPerformed JobsPerformedID WorkStationID DatePerformed Description ... (plus any other fields you use to describe jobs performed) -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... The Table 'WorkStation' is a list of Work Stations (11 in Total) each with there 'Work Station ID' number. The other table is a table of all the jobs performed in our workshop which, therefore the table 'WorkStation' and 'WorkStation ID' are referred to here in a combo box manor. I need them to match up with each other though???? i.e. 'Work Station ID' 1 = 'Work Station' GU010 Body Fit 1 Cheers -- Regards Ashley Smart "Jeff Boyce" wrote: Ashley Are you saying you have the same data in two different tables? And that you want the (same) data in Table2 to update when the data in Table1 changes? If so, DON'T! Access is a relational database -- it's neither a good idea nor necessary to record the same fact (e.g., WorkStation) more than once (say, in a WorkStation table). From then on, you can use the WorkStationID to point back to the information about the WorkStation. Use a query to do this. -- Regards Jeff Boyce Office/Access MVP "A. Smart" wrote in message ... Good Morning, I have a table 'Work Stations' which inlcudes the fields 'Work Station ID & Work Station'. These fields are both refered to in another table, what I would like to happen is have them update each other if one of them is changed in this table. How would I go about this? Thanx in advance.... -- Regards Ashley Smart |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to update these figures? | Yorkie118 | Running & Setting Up Queries | 11 | October 22nd, 2005 05:56 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |