Question Value Updates from another table

[deejabram]

Please bear with me as I attempt to explain this issue. I know most of you are far more intelligent than I am, and am hoping I can find the right answer here, as many of you have been very helpful in educating me over the years....I feel I should be paying you tuition:

Background: I am self-taught in Access. I have very limited VBA abilities and design everything through design view and Macros. I have been using Access for 3 years and still learn something new every day. I do not consider myself an expert, but I am not a complete novice either.

My End state: To create a database in which a supervisor can input his/her daily attendance and the database computes daily work capabilities based off set performance standards and the assigned staffing minus all forms of “time off” (i.e. sick, vacation, workers comp, etc.)

Set Up

Table 1
Daily Attendance. This is where the actual STORED data will go. Has all information relative to the database. Primary Key is an Auto Number. This information is fed through a query which calculates On Hand Labor (permanent staffing minus all forms of “time off”)

Table 2
(PRIMARY KEY) Work Area: 1 Field with 87 unique Work Areas
Division: 1 Field with 7 different Divisions entered a total of 87 times
Performance Standards: Several fields with unique performance standards based on function and work area.

Table 3
(PRIMARY KEY) Supervisor: 1 Field 109 unique entries
Work Area: 1 Look up field (looks up the work area from Table 2)
Shift of supervisor: Value List of 6 different Shifts
# of Employees: 1 Field (numerical – Long Integer) which contains the number of employees that supervisor has on hand

Tables 2 & 3 contain values which are fixed (meaning rarely changed, but when they are changed I want the values to update everywhere. THIS is where I am having the problem. It seems so simple, yet my mind isn’t grasping the concept of how to accomplish this. I have tried queries, DLOOKUP, almost everything I can Google or think of. I would like this information stored on the form where the user-end has entered the data....which is easy enough, but how to I get these "set" values to transfer over from the other table?
Can someone please educate me on how to do this? Everything in my database is set and running smoothly except this one little problem..

 

[pbaldy]

I'm not clear on the question. Here's a way to get values from one table to display or save:

http://www.baldyweb.com/Autofill.htm

Based on "when they are changed I want the values to update everywhere", you shouldn't be storing related values anywhere but in their base table. In other words, you would only store the supervisor key field in other tables, not the name, etc.

 

[deejabram]

Thats what I am attempting to do, but I dont think I am doing it right. I have made the other tables into "Lookup" in which a user can select from a list (which is in the base table). However, when it comes to performance standards and number of employees assigned, I only want the relevant value from the base table for that work area on that work area's form. I have a different form for all 87 work areas.

 

[pbaldy]

87 forms? And I just noticed "1 Field with 7 different Divisions entered a total of 87 times". It doesn't sound like the db is normalized. Does each work area have different fields or something? Why 87 forms?

 

[deejabram]

I dont think it is either. The 87 forms are representative of the 87 different work areas. Each form records their Division, the four shift supervisors, staffing information, performance standards, work capabilities, etc. I wanted a seperate place for each supervisor to go to record their information

 

[pbaldy]

Are there 87 tables? I'd have one table/form, with a field for work area. There are all kinds of ways to restrict that form to a given work area. Here's one:

http://www.baldyweb.com/wherecondition.htm

 

[deejabram]

Just finished reading your article. I am not very good with VBA. In fact I just finished a 2 day SQL Querying course last week and my ears are still wet from it. With that said, I did do something of the sorts that you article suggested.......I used queries with the 'Like' criteria to sort out each Work Area.....even though that also created 87 different queries.