Auto fill one field in one table after data is entered in a different table in same database?

[synses]

Is there a way that when I enter data in one field in one table it will automatically update a field in a different table?

If so, are there options for update "if empty" and/or "update by adding to the field/not deleting the field, so there may be multiple values for that field?" and/or update by replacing existing value in that field on that table?

I have several tables in one database that I need to make it so that when you enter a new value (name, for example) in one field in one table, it populates that name in one field in another table in the same database, automatically, sometimes I want it to replace the existing values, sometimes I want it to add to the existing data and that may mean multiple values in that field, and sometimes it will just be populating an empty field.



Thanks for any help you can offer!
Synthia

[rpeare]

I would suggest you have a bad data structure. Storing text values across tables instead of foreign keys is a really good way to screw up your data.

For instance let's say your customer table is:

tblCustomer
C_ID  C_LastName  C_FirstName
1     Smith       John
2     Jane        Doe

Now what you're suggesting is that you have other tables in your database structure where the last name of the customer may be stored and you want to update all those tables. I don't really care if you are only replacing the last name if it's blank or not, it is still the same problem. If your updates fail, you will have orphaned your data which is a big no-no. If instead you store the c_id in all your foreign tables (customer orders, customer email addresses, etc) whenever you update the client information in the client table, the next time you run a query against it you will see the most updated information.

[synses]

My Goal was to have one large table, "Clients," that shows everything the (home)company has/maintains/needs on that client (client buyers, engineers, facilities; company sales people, engineers, PMs, etc.), but to have the data entry done on a separate basis, in the smaller tables, for the various client Facilities, client Buyers, client Engineers, client QAs, and the (home)company PMs, (home)company OutSalespersons, etc., and lessons learned.

I started with a table for Client names, entered all client (company) names. All client info tables (buyers, engineers, facilities, etc.) start with the data entry person selecting the client name, which comes from the Client Names table.

Do you have a suggestion of how that could be done more efficiently?
Thanks,
Synthia

[HiTechCoach]

Synthia,

First, I agree with rpeare's comments. Good advice.

Is there a way that when I enter data in one field in one table it will automatically update a field in a different table?

Yes, but is rarely done and should be avoided.

What you re describe is how a spreadsheet would work not a relational database, like Access.

If you design your tables as a relational database then you should not have to do what you are asking.

If so, are there options for update "if empty" and/or "update by adding to the field/not deleting the field, so there may be multiple values for that field?" and/or update by replacing existing value in that field on that table?

I have several tables in one database that I need to make it so that when you enter a new value (name, for example) in one field in one table, it populates that name in one field in another table in the same database, automatically, sometimes I want it to replace the existing values, sometimes I want it to add to the existing data and that may mean multiple values in that field, and sometimes it will just be populating an empty field.

It is generally considered bad practice to store multiple values in the same field. I have never had to so what you are asking in any of the many databases I have created in over 35 years.

A concept that is had to grasp when first stating out in Relational Database design is:

1) That data is stored in a normalized design. The user will normally never see it in this format.
2) Forms enter the data in the way that matches the workflow.
3) A Query pulls the data from multiple tables together (similar to a spreadsheet) for Reports. This often does not look like anything the the tables since multiple tables are combined.


With a spreadsheet, you set it up the way you want it to print. In Access that would be a report, not the way you enter the data or how it is stored.

[rpeare]

I would organize this a bit differently.

tblClient
c_id  c_name c_address ---> other client (company) related fields

tblClientFacilities
cf_id  c_id  ----->  other client (company) related facilitiy fields

tblRole
r_id  r_description
1     Engineer
2     Buyer
3     Process Manager
4     Quality Assurance

tblPeople
p_id  p_lastname  p_firstname r_id 

tblClientPeople
cp_id  c_id  p_id ---> other client people related fields

In essence, you do not need a separate table for each 'type' of person, you just need one table storing all contacts and their role. Then a client person table to store which company they're associated with (a person could be active at multiple companies but you'd only have to store information about that person one time). Then you could limit what your users see (employee types) in your data entry from based on their selections. Finally, the method I'd use for this, if you want the users to see everything related to the company in a single data entry form, would be to use a tabbed form where the main client information is on the first tab and all the relevant information is on subsequent tabs (client facilities on a tab, client people on a tab and so on)

I do not use bound forms at all so someone might be able to respond better if you are using a form/subform combination, even if you are a tabbed form is still appropriate.

[synses]

Thanks for the reply, I agree. I see that I need a course or two in database development and ACCESS usage. Problem is, my sister asked me to create this database for her in ACCESS- long story -anyway, I have to "wing it" now.

Any suggestions of how to change it to do it better, or of how to learn how to change things to do it better, would be appreciated.

It is supposed to be a database to hold
(a)client names (1 per client) with related
(b)facility names (several per client) with
(c) several buyers (several per client, may be same or different per facility),
(d) engineers (several per client, may be same or different per facility),
(e) Procurement Managers (several per client, may be same or different per facility),
(f) QAs (several per client, may be same or different per facility)
(g) long text field for "lessons learned"

then, for each of these clients/facilities, there are internal company (the company is the vendor to the clients)
(h) inside sales people (one or two per client, may be same or different per facility)
(i) outside salespeople (one or two per client, may be same or different per facility)
(j) Project Managers (one or two per client, may be same or different per facility)
(k) Engineers (one or two per client, may be same or different per facility)

Forms need to be made for data entry to enter and maintain/update all this info on over 600 clients.
Historical info needs to be accessible showing who were past PMs, Sales, Buyers, etc.

Queries need to be able to be made, and reports that may need to be produced to address/include things such as:
1. The lessons learned for a certain client for all and/or some of their facilities and who (name, title, contact info) submitted the lessons learned
2. Quick access to answer: Who are/were the company outside and/or inside salespersons (or engineers or PMs) or client buyers (or procurement managers or engineers QAs) for a certain client and/or their facility and all their contact info now or at a certain point in time.

Contact information for client main offices and facilities and all client personnel need to include e-mail addresses, physical addresses, and at least two phone numbers for every buyer, engineer, procurement manager, QA, etc., in case of being difficult to reach;
Contact information for company personnel need to include e-mail addresses, at least two phone numbers.
Names need to show full name and title.

So, I made up these tables and started on a form.

Can you suggest how to alter or learn to do it better quickly?
Thanks for your help!
Synthia

[synses]

I understand (and that is more than half the battle!!)!
Thank you so much I will rework it now.

Thank you exponentially.
Synthia

[rpeare]

Based on your description I don't think anything changes with my suggestion, the only thing you'd likely do is on the tab relating to tblClientPerson you might have a sub form (if you're using a bound form) holding the facilities for which the person is responsible.

With regard to wanting to be able to know who was at a particular facility on a particular date, some times years in arrears, you would have to have a start date and end date for each person at each facility, and I seriously doubt your users would have access to that information. I would think it extremely unlikely you users would want to constantly keep this information updated for 600+ clients. Rather, I would suggest that on a particular date (let's say when a sale is made) that the individuals involved in the sale are recorded on the sale structure.

i.e.

tblProducts
Prod_ID  Prod_Name  -----> other product related fields

tblProductCost
PC_ID  Prod_ID Prod_Cost  Prod_StartDate  Prod_EndDate ----->  other product cost related fields

tblPO
PO_ID  C_ID PO_Date CF_ID ----> other purchase order information
(CF_ID is a foreign key to the client facility table mentioned in first post)

tblPOPeople
POP_ID  PO_ID  P_ID ---->  other people related to the purchase order fields
(P_ID is a foreign key to the person table mentioned in first post)

tblPODetail
POD_ID  PO_ID  Prod_ID  PC_ID  Quantity

Finally with regard to learning access, I think most of the people here have stumbled through years of learning things bit by bit and I'm no different, there is nowhere I'm aware of where you're going to get a crash course in MS access that's going to make youy great in a short amount of time. The best thing you can do is keep plugging along and ask questions where appropriate or, alternately, if you have access to the code/design of something someone else has written you can learn pretty quickly by tearing apart their database and understanding how it works.

[synses]

I modified the database tables following the excellent suggestions, but get a message asking to enter the parameter for one of the look up fields when I try to open the client table..
.
any help would be greatly appreciated!
Synthia

[HiTechCoach]

I modified the database tables following the excellent suggestions, but get a message asking to enter the parameter for one of the look up fields when I try to open the client table..
.
any help would be greatly appreciated!
Synthia

I would delete the Lookup stuff at the table level.

I highly recommend NEVER using the Lookup properties at the the table level. It is the first thing I remove when helping people solve issues with their database.

Also see:

The Evils of Lookup Fields in Tables

[synses]

How do you create a table with these if not look up tables? I assumed, for example, in

tblClientFacilities
cf_id c_id -----> other client (company) related facility fields
I assumed c_id is the look up field of the c_id...


tblPeople
p_id p_lastname p_firstname r_id

I assumed r>id looks up choices to select from the autonumber role and description in the tblRole...cp_id c_id p_id ---> other client people related fields

I would organize this a bit differently.
I assumed c_id is the look up field of the c_id...p_id "looks p" the p_id and returns choices to select from...

Is there any way you might be able to suggest that I can learn what I don't understand about building the database the way you suggest...?

tblClient
c_id  c_name c_address ---> other client (company) related fields

tblClientFacilities
cf_id  c_id  ----->  other client (company) related facilitiy fields

tblRole
r_id  r_description
1     Engineer
2     Buyer
3     Process Manager
4     Quality Assurance

tblPeople
p_id  p_lastname  p_firstname r_id 

tblClientPeople
cp_id  c_id  p_id ---> other client people related fields

In essence, you do not need a separate table for each 'type' of person, you just need one table storing all contacts and their role. Then a client person table to store which company they're associated with (a person could be active at multiple companies but you'd only have to store information about that person one time). Then you could limit what your users see (employee types) in your data entry from based on their selections. Finally, the method I'd use for this, if you want the users to see everything related to the company in a single data entry form, would be to use a tabbed form where the main client information is on the first tab and all the relevant information is on subsequent tabs (client facilities on a tab, client people on a tab and so on)

I do not use bound forms at all so someone might be able to respond better if you are using a form/subform combination, even if you are a tabbed form is still appropriate.

[synses]

I have to build a database now for my sister, and need (obviously) to learn quickly how to do it. Do you have a suggestion for the best way to learn fast for a fast learner who needs to start at the beginning but has already read a few "How to" books and obviously still does not see how to build a database with these elements:
1. The Vending Company staff members, (about 20) (this is my sister's place of employment)
2. The Vending Company staff members connected to their roles (4: PM, Inside sales, outside sales, engineers, (multiple for each role per client and/or facility)and contact information assigned to the different clients and different client facilities
3. The Client Companies/Organizations (contact info) about 600 of them (some private companies, some government)
4. The Client Companies/Organizations' facilities - name and location (some client companies/orgs have only one location, some have main offices and facilities, of those with different locations, some have key staff unique to each facility, some have one set of purchasing/engineer staff for all facilties)
5. The client companies' staff members (2 to 6 for each company) and contact info including two phone numbers and an e-mail address
6. The client companies' staff members' roles (4: buyers, engineers, procurement managers, QAs) and their contact information (usually several per client and client facility)
7, A lessons learned long text field for passing along lessons learned for each client and or client facility

It will be used by people in the vending company to
1. Look up people in the vending company and in the client companies (salespersons, buyers, PMs, QAs, engineers, etc.) and how to contact them for each client/facility, as in ...
Record, store, and retrieve all the contact info for all staff related to certain clients and client facilities (in house and client staff, main offices and facilities)
(such as "I need a quick way to see all the buyers for Client A and all the phone numbers to reach them" or "I need to quickly see which in house engineer handles that client" or "I need a quick way to get the names and e-mails for all the in house salespeople and client buyers for Client A" these might be quick looks or for printouts...
3. Record, store, and retrieve all lessons learned for one certain client or groups of clients...or all lessons learned.....for quick review...and for training handouts..


I am not being paid. I offered to do it for my sister because she needs it , has not time to learn to do it, and I owe her big time and I thought I could do it. (she gave up her life to nurse me back from a near death crash and paralysis)

Any help appreciated.
Synthia

Synthia,

First, I agree with rpeare's comments. Good advice.




Yes, but is rarely done and should be avoided.

What you re describe is how a spreadsheet would work not a relational database, like Access.

If you design your tables as a relational database then you should not have to do what you are asking.



It is generally considered bad practice to store multiple values in the same field. I have never had to so what you are asking in any of the many databases I have created in over 35 years.

A concept that is had to grasp when first stating out in Relational Database design is:

1) That data is stored in a normalized design. The user will normally never see it in this format.
2) Forms enter the data in the way that matches the workflow.
3) A Query pulls the data from multiple tables together (similar to a spreadsheet) for Reports. This often does not look like anything the the tables since multiple tables are combined.


With a spreadsheet, you set it up the way you want it to print. In Access that would be a report, not the way you enter the data or how it is stored.

[rpeare]

The point is that you don't need a lookup field, and they cause more problems than they solve. The only utility is that they display the foreign key value when you open the table. I don't use them. I create queries that link the appropriate tables when necessary (producing reports, etc)

[synses]

How do queries know/determine which salesperson is assigned to which company? How is the connection between staff members, their roles, and the clients they work for (if client staff) or the clients they handle (if company staff)...made in the database if all these things (clients, facilities, client staff, client staff roles, company staff, company staff roles, clients that company staff handle, and the related contact info, and lessons learned) are separate tables?

Thanks again,
Synthia

I know it is a lot to ask, if you choose not to reply I understand and empathize..

[rpeare]

an example

tblCategory
C_ID  C_Description
1     Sporting Equipment
2     Construction

tblToys
T_ID  T_Name  C_ID
1     Ball    1
2     Bat     1
3     Lego    2

the query to show the toy category would be something like

SELECT tblToys.T_ID, tblToys.T_Name, tblToys.C_ID, tblCategory.C_Description
FROM tblToys LEFT JOIN tblCategory ON tblToys.C_ID = tblCategory.C_ID

Cut and past the SQL into a query window then look at the design, then look at the results