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 haveformed 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 theseforms 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 theforms to input data, while of the tables that store the information remained hidden so that there are no accidental errors that corrupt the entire database.

Sponsored Links:

Related Topics

Adding multiple data to one field
I would like to be able to have the date written in to a field when another field is updated. Pretty simple, however, there are circumstances that this field could be updated as many as 6 times. I will have to build a query that will search this field for specific dates so I can count them. I need to keep on file each added instance and not just the latest update date. I had thought of using the field being updated and adding Now to it to get a value, and then should there be another update I could simply take the value from the field and the new date (updates will never be done more than once on a given date). Not sure if this is the best idea or if there is a much simpler path


Adding Record to Form that updates the Table
I am an Access rookie. I have built a form and want to make sure that if I add a record in the form that that record also updates the tables/tables.

I have noticed that any edits that I make to existing records are updated in the tables.

Can anyone help. I think it may have something to do with my relationship settings, but I really am not sure


Download in MS Word file format (.doc)
This tutorial has been designed to give an introduction to some of the basic features of MS Access. The tutorial will cover the following topics:
Creating a database; Creating tables; Changing the Field Properties in the tables; Adding Lookup Tables; Creating relationships; Adding Passwords to the database; Creating simple forms using the Form Wizard; Adding graphics and creating customized forms; Creating forms using multiple tables; Creating reports using the report wizard; Formatting reports; Creating Switchboards.


Adding a name to multiple tables
I am trying to decide the best way to setup a new database that I am creating that right now has two main tables in it. Right now the tables are linked by the LNFN field so I can puttogether queries and draw data from both tables.

Is LNFN the best way to link the tables?

How do you add a new name to the database that would show up in both tables or additional tables that I may add?


Import multiple updates for a single record
I have a fixed number of records that will have updates to a single field. The new information is coming from a template sent to multiple users that will only manipulate the records under their jurisdiction from the master list of fixed records.

Each version of the Excel file will always contain the same # of master records with an identical update in one field.


Replacing and Adding Data
I have 4 Excel sheets that feed into 1 database. From there, I have been able to create multiple queries and develop 1 table with data from all 4 sheets. My problem arises when data is updated (new address, open issue changed to closed, etc.) or new entries are added. How can I get my database to search for updates from the queries and correct the old entries while adding the new?


Can't Update Queries linke to SQL Tables
I am in the process of moving all my backend databases from Access to SQL Server. I have upsized all the tables but having problems with my Access Front End. I have several forms based on queries that when run, pull information from three tables. When I had the tables in Access, after running the query I could make changes/updates to the query results.

But now that I have upsized the tables to SQL, I can only view the data. I can't add, delete or make any changes. All my tables have a key field in them.

If I run a query on just one table, then I can make changes/updates


Multiple value check list load from table
We use some multiple value fields where users can check multiple values. Within a new form we want to load the contents of a multiple value field from a tabel (we have made the query for this and get the multiple values) in a new field and use it as a multiple value lookup field.

This does not work.

We get an empty field.

How can we see the selected multiple items from the query in the field on the form(table lookup)?

Example:
John; Peter; Ted (is selected in the multiple value field)

Get these values from the tables in the database.
Then in another form:
Load these values in a new multiple value field:
See the selected items:


Using 1 Autonumber field for 3 tables
I have three tables which each have their own uniqueID field that I really need to be unique across the three (for union at a later point). Therefore I have created a table which has two fields - an autonumber field and another indicating which table it lives on.

I basically want to work out an efficient way of adding a new record to the autonumber table and adding the number it produces to a new record within its equvalent foreign key.

It needs to be reasonably efficient as around 1000 records will be added at a time on one of the tables, whereas only 1 record will be added at a time on the other two.


Values not being inserted into the tables from form
I have a form which has multiple tabs and on each tab there are fields which are from multiple tables. I have a combo box on the first tab which successfully finds all the fields information as I have linked all the tables by the Entity_ID which is common in all the tables. However when adding in some information into the form, the Entity_ID is not inserted into the tables and therefore when using the combobox again which uses the Entity_ID the information does not come up


Access 2007-10 table lookup, multiple entry
I am kinda new to Access (well I used it back in 2002 my last time) and I am trying to create a simple database. It consist of three tables, one of the tables is used as data lookup to fill the field in another table.

My problem is that this should be a multiple value field which is easy,form or anything that gives me the multiple values separated by a comma in a message box for example


Multiple fields or multiple tables?
I have to ask the user up to 50 questions each of which requires me to record 10 pieces of information. As far as I've got is A) Split tables with large field numbers for users B) Table for each user. To me, the way forward seems to be B but all those tables!


Use Cascading Updates or Cascading Deletes
If you have defined relationships between tables in an Access 2003 database, you can use cascading updates or cascading deletes to propagate changes across all the tables with that relationship. Once you have the cascade enabled, changes that you make to the primary table are carried through to all related tables.


same field in multiple tables
I have multiple tables which are linked by the same primary field key. How do I set it up so that I only need to add the data in the primary key field in one table which will automatically populate the primary key fields in the other tables


SQL Update for multiple records
I have a set of records I'm trying to run an update on,
[CODE]

When that code runs only the record with focus gets updated, so I need to manual select each record, press the button and select another record until done, how can I make it so all the records update?

If I remove the productID from the WHERE clause it updates all the records for the order but it updates them all with the first record from the source.

I tried adding the SELECT and FROM to the code but it always brings up a runtime error saying syntax error in statement.


Duplicate info in tables Best Practice
I am debating on adding another field to a table in order to track dates. I have the information in another table but these 2 tables have no relationship to each other.

Initially, I was thinking of creating a relationship between the two tables in order to pull the date from there. But this table will not always be filled out depending on the employee class.

So, I was thinking of just adding the field rather than creating a relationship as I don't always need the info in that table.

I am wondering before I go and add the extra work, would this be the most efficient way of doing this?


Adding Forms, reports, etc. to Multiple Front Ends
If I have to make changes to front ends by adding more reports, changing reports, exports, etc., would I have to update each front end separately?

Right now, the test database resides on the server and each of the users has a short cut to their front end on their desktop. I need to know as I'm creating an instruction manual for useof the database and my boss wanted me to include in there about updates and what not because users will be able to request different reports that I will create for them and put on the frontend.


1 autonumber for 3 tables
I have three tables which each have their own uniqueID field that I really need to be unique across the three (for union at a later point). Therefore I have created a table which has two fields - an autonumber field and another indicating which table it lives on.

I basically want to work out an efficient way of adding a new record to the autonumber table and adding the number it produces to a new record within its equvalent foreign key.

It needs to be reasonably efficient as around 1000 records will be added at a time on one of the tables, whereas only 1 record will be added at a time on the other two.


Transaction with Access 2007 and SQL Server
I created Access 2007 (accdb file) and link tables (through odbc) to SQL Server 2008 as the backend database.

I'm using bound forms in my application. When I save the form, I need also some other updates to other tables. I thought using transaction but it seems failing.

Start transaction
DoCmd.RunCommand acCmdSaveRecord (to save current record)
Updates to other tables (open recordset with dao, updates, deletes)
Commit transaction

Is it possible to run such a transaction as a whole and even rollback if any process fail?


Multiple value check list load from table
We use some multiple value fields where users can check multiple values.
Within a new form we want to load the contents of a multiple value field from a tabel (we have made the query for this and get the multiple values) in a new field and use it as a multiple value lookup field.

This does not work.

We get an empty field.

How can we see the selected multiple items from the query in the field on the form(table lookup)?

Example:
John; Peter; Ted (is selected in the multiple value field)

Get these values from the tables in the database.
Then in another form:
Load these values in a new multiple value field:
See the selected items: John and Peter and Ted.