Adding a Field Updates Multiple Tables


I am currently in the process of converting several (25+) Spreadsheet based lists into one Access Database for easier additon/removal of employees.

Each Employee record contains many columns of information, so many that I have had to continue the data on a second table, with a Unique ID Field the link between the two tables. I have formed a One-One Relationship between the tables using the ID Field.

For use by Supervisors that will be updating certain areas of data, I have created forms that look similar to our old Excel Spreadsheets. I was happy to see that adding a name to these forms would add the name to the original table, and the same with removing a name.

Now that I have created a second table, I am hoping there is a way that adding a name to any form will add the name to two or more tables. My goal is to have the Supervisors use only the forms to input data, while all of the tables that store the information remained hidden so that there are no accidental errors that corrupt the entire database.

Thank you for any assistance you can provide. My knowledge of Access is somewhat new, so bare with me. I have already been using the forums hear to solve several problems I have come across.

Regards,
Ryan Rowbottom


Sponsored Links:



How can I find a field in multiple tables (many) in an easy and correct way




I have two tables. Table 1 contains bio info, Table 2 contains giving info [2010 Offertory]

I know I need to use an update query.

I just can't seem to get it to work. I joined the two tables since not all people in Table 1 have Table 2 giving info.

I added a field in Bio one for 2010 Offertory
So how do I get those that have giving history in Table 2 to populate that field in Table 1

Any help is appreciated.

I have attached a copy of my attempt. Attached Files Doc1.pdf (117.9 KB, 3 views) Reply With Quote 03-21-2013, 07:14 AM #2 JoeM Experienced Access User Windows XP Access 2007 Join Date Jun 2012 Posts 776 I do not have the ability to download your database from my current location, but think I may be able to address your question.

Why do you need to store the 2010 offertory in a field in Table 1? Generally, it is not a good idea to physically store any value in a table that can be calculated in a query. One of the reasons is because if the data in Table 2 were to change, your database would lose integrity if you did not subsequently run the Update query again in order to also update Table 1.

Most of the time, it is totally unnecessary to store calculated fields in tables when they can be derived at any time using a query. You can use queries for pretty much anything that you tables for (i.e. basis of Forms, Reports, exports), so there is usually no reason to write the information back to a table (unless you were going to delete the table where the values were calculated from).




I am trying to create a query which contains fields from multiple tables. I've managed to create the query as I have with any other query i've built; however when I add the field from a secondary table, when I run the query, the results comes back missing a good majority of data. Can anyone help me with this, what am I doing wrong. What do I need to do to fix the problem. My end result intention is to build a report to show this specific data.

Thank you anyone in advance.

David




I hope someone can help!!! I have an either or problem. I need to add a field from another table onto my form and don't know how after the form has been created. I tried creating a new form using multiple tables but when I add all of the fields (there are about 60 fields)in one table plus 3 fields from another table I get a message that says the wizard can not create the table.

I can create the form with about 3/4 of the fields and the fields from the other table or I can create the form with all the fields in one table without bringing in any fields from the second table.

Either solution would work But I prefer to add to add the fields from a second table as everything in the form is already formatted. Any help would be greatly appreciated.




I am stuck with updating a field record with a field from another table.

I have two different forms.
Form 1:
DateId(AutoNumber & PrimaryKey)
SelectDate (Date/Time, Format "Short Date")
DateChanged (Date/Time, DefaultVaule= "Date()")

Form 2:
RemitDate (Date/Time, Format "Short Date")
RemitTime (Date/Time, DefaultValue="Time()")

I actually want that when user enters a new date in form 1 and update it then RemitDate on form 2 becomes equal to the latest updated date in form 1 only for the new records in form 2.

Previous record dates on form 2 should not be disturbed.

Please help me to get this problem solved.

Thanks




Hi,

I am using following code to update a field in another table from a form. When end user exit or click save following happens.

Can any please verify and let me know why the update query is not working.


Dim SQL As String, Conn As Connection
Set Conn = currentdb.CreateQueryDef.SQL
'Set Conn = CurrentProject.Connection
If (DLookup("[Participant_Number]", "tbl_groups", "[GA_Record_ID_2] = '" & [GA_Record_ID_2] & "'")) = 0 Or Null Then
SQL = "update [tbl_groups]([participant_number]) values ('" & Me.txtParticipant_Number & "') "
ElseIf (DLookup("[FeePerParticipant]", "tbl_groups", "[GA_Record_ID_2] = '" & [GA_Record_ID_2] & "'")) = 0 Or Null Then
SQL = "update [tbl_groups]([FeePerParticipant]) values ('" & Me.txtFeePerParticipant & "') "
End If
Conn.Execute (SQL)
Conn.close
Set Conn = Nothing




Does anyone know if there is a way to add a field to my Table that I can format do automatically prefill the current date as the date the user entered the record? If so, how??? Thanks




I have a databse with multiple tables and forms and would like to have information input in one form/table, auto update the dame feild type on another form/table. can this be done?

vericlese




If there was a forum for absolute Access beginners, I'd go there. Anyway I hope someone will answer a very basic question about adding a field to an existing table. This has to do with an existing database that I'd like to slightly modify. Let's say there are two tables. One is externally linked to another database and contains a number of fields, among them is a product id and its description. The second table contains a number of fields, among them a product id (keyed to first table) but no description. I want to bring the product description from the first table onto the second table.

Please help with this very simple question. Thanks.




I'm using Access 2003 and I would like to know how can I use a field from one table and incorporate it into another? I only want to be able to update this field in one table so the info will be current in another table.




Good Morning,

Ive come across an old form i made somewhere last year. And it needed an update.

I added a field to the table which uses a lookup function, and gets one single value from a seperate table.

When i put this field on the form, the value of the field is shown, but the formulas based on that field are not functioning untilll i click on the field in question.

Its like it acts like a dropdownmenu and has a afterupdate event. Is there away to simulate a mouseclick on that field, so that the user doesnt have to click the field for every id (autonumbering)?

I know theres an SetFocus, but that doesnt seem to work.

Cheers!!




I added a field on an existing form from a different table, Access prompted me to create the relationship which I did and everything worked like clockwork and the new field appeared. However I have now lost the ability to change or add new records (simply greyed out) checked property setting and they are correct. I removed the newly added field but this did not help how do I overcome this issue?




I need help creating a form that will auto-populate data for two linking fields in multiple tables (ASMIS_ID and LINK_ID). How can I get my form to auto-populate data in the linking fields..?




I have been away from Access for a while and now I am going to start on a new project. I need to refresh my memory. I am going to want to update multiple tables from one form....will I be able to do so? Any special code needed? Also I am going to want to be able use a drop down to find and select a specific value/name in one table and pass the record ID only on to another table.....ie: look up a name and pass the record ID for the name but not the name. Am I going to be able to do so? Your help is appreciated. Thanx, Kenk




I want a macro to change the value of a field in another table - its a combo box stating "available" and "unavailable". I would like to change these values automatically, based on what I enter in another form.

I have tried using SetValue, but it does not seem to work without error.




Is there anyway to auto populate a field in one table to a field in another table?




I'm trying to create a query based off a table.
Is it possible to create a calculated field in a query?

For instance, the table has fields for QTY and PRICE
Can I create a field in the query to multiply these 2 values without adding a field to the table? If so, how is this done?




in my form I created an unbound field with a requested combination, now how can I put those in a table field.
in other words, how can I send the content of a form's unbound field to a field in the table?

your help is very appreciated.

Regards,
CS.




Can someone please tell me how to add a field from another table to a form after the form has been created?

I want the name fields to automatically fill in on my form when I enter the primary key data from a linked form. How do I get the fields from the table that is being used for the linked form to the secondary form. There is a one-to-many relationship created. I hope this makes sense.