Please Help?? Auto Update Field in table

[Guest]

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....

 

[Jeff Boyce]

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.

 

[Guest]

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

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.

 

[Jeff Boyce]

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>

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

 

[Guest]

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????

 

[Jeff Boyce]

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).

 

[Guest]

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

 

[Jeff Boyce]

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].

 

[Guest]

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.