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


Post your answer or comment

comments powered by Disqus
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.

Hi all, quick problem from a relative novice!:

What validation should you use for:
Not greater than a Field X in Table Y?

So for example if the field was Order_Quatity in the Orders table:
Not greater than Maximum_Available in Products table


I'm trying to use the validation rule box in the design view of my table, not VB.

Many Thanks
Luke

I added a field in the template that comes with access call service call management in the invoice screen . That went ok BUT when i try to print the invoice it does not show up . Where do I go to add it to the print ??? This is my first time at this BUT the add worked

THXS Steve

I have three tables setup in a 1 to 1 relationship using a Autonumber field as their primary keys. I created the seperate tables to group related data together and reduce the tables sizes in general. So think of it as one main table and two tables of extended data.

I need to be able to add a record to table 1 and have it use the same autonumber to reserve that row for data in tables 2 & 3 so that my relationships stay in sync. I want to update the data in tables 2 & 3 later when that information is available.

I have been breaking the database testing some things out like building a form from a select query using all three tables. When I add the record to table 1 nothing is added to the other two. Things get messy on subsequent attempts to add data to any of the tables.

I have avoided this problems in other tables by not using the autonumber datatype, however, I don't have a good unique key that can be manually entered and kept accurate, so autonumber solves that problem in this case.

My VB is very rusty but I'm thinking there should be a way, before update, to capture the autonumber being used for the new record and write it to the other tables. I'm thinking that would be one solution, but I can't seem to get started on that code. Any help or examples would be great.

Thanks,
Jim

I am trying to setup a database for vehicle stock control.
Im not sure if I have gone about this the right way as I am new to this but thus far it is working correctly except for one annoying problem.
The database consists of so far

tblIAWVehicleDetails (Primary key "IAWvehicleID" autonumber)
tblIAWSellers (Primary key autonumber)
tblIAWBuyers (Primary key autonumber)
tblIAWStates (Primary key autonumber)
tblIAWStatus (Primary key autonumber)
tblIAWSafetyDetails (Primary key autonumber)
tblIAWSold (Primary key autonumber)

In the Vehicle Details table a stock number has to be manually added as this will be used for new stock as well as current stock (Number range from 100 - whatever) "IAWVehicleNo".
This table contains all relevent data with reguards to make, model, bodytype, color etc.
The sellers table contains the details of the seller Name, address, Phone, LicenceNo etc.
The Buyers table contains employee names.
The States table contains all Australian states.
The Status table contains current vehicle status Retail, Wholesale, Wrecking etc.
The Sold table contains the details of the person who purchased the vehicle if sold.
The Safety details table contains a safety checklist for pre purchase inspections eg: Headlights yes/no checkbox, Headlight text field for any extra info.
It also has a field for a safety Certificate No once the vehicle is checked and recieves a Safety cert.
The forms are setup as
frmIAWVehicleDetails
frmIAWSellers Subform
frmIAWVehicleSafetyDetails
frmIAWVehicleSafetyDetails Subform

The Vehicle Details,Sellers,Sold & SafetyDetails tables all have the IAWVehicleID & IAWVehicleNo Fields but when the details are entered through the forms the IAWVehicleNo which is the manually entered number only updates to the tblIAWVehiclesDetails but the other IAWVehicleNo fields in the other tables remain blank.
Any advice or help would be greatly appreciated.

Hello,

I have a database with 10+ tables that I use to keep records for monitoring birds and performing behavioral experiments. Each table is based off of information that is updated at various points in time. For example, I have a table for females that lists their USGS band number, their weight, the date I captured them, etc. I another tables that keep track of when the first egg was laid and the hatch date. I also have tables for each nestling to record growth parameters when they are 5, 11, and 14 days old. However, I do not input information in every table at one time because data is collected on different dates depending where the adults are in their nesting cycle. All of these tables are linked in a one-to-one relationship with a table that holds the record number. The record number (primary key for every table) is an integral part of every table as it is the only unique identifier I have. I have enforced referential integrity and checked cascade update and cascade delete because I need to have information in every table for every record, even if the field values are null.

All of my fields are displayed in a form to make updating the tables easier (as I have multiple users updating information as data is collected). However, I am having a problem where the form will display the entered information IF I have an record number in every single table (so the "record" field is populated in every table). I only type the record number in once at the top of the form and that field is bound to the original table that connects all of the other one-to-one relationship tables. I would like to be able to enter this number once in the form and have all of the successive tables populate the "record" field with the number I typed in the form so viewers can toggle through every record in the form view and update the appropriate form as data is collected. It is very time consuming for me to manually type in the same record numbers on every single table just so all of the data will display in the form window.

Any insights on how to solve this? Anything I can do as an "afterupdate" on the form so all of the tables that are linked successively are updated with the record number? Thanks!


Not finding an answer? Try a Google search.