General :: Populating Data Between Tables

Aug 1, 2012

I have an Access 2007 database with two tables (I will call them table 1 and table 2)

Both tables contain the same two fields. (I will call the FirstName and LastName)

Table 1 has an associated Form where the user enters the two names. When the value in either one of the two fields in Table 1 change I want the corresponding field in Table 2 to automatically update with the same value that were entered in Table 1. Basically I want Table 2 to automatically replicate the same data in Table 1. So if I type the text "John" into the FirstName field in Table 1 then the FirstName field in Table 2 will automatically update with the text "John"

I am new to access and am struggling with the automatic updating.

If the automatic part is too hard then I will be happy to attach the update action to a command button.

I have uploaded my database file with the two tables for reference. I want to get the fields (for all records) in table 2 to replicate table 1 so that when table 1 updates table 2 values changes to show the same text.

View Replies


ADVERTISEMENT

Populating Multiple Tables With Data

Nov 8, 2004

I haven't used access for a long time so I am very rusty.

I have a few tables but I'll just use two for an example:

Table A
Ticket No
Part No
Defect Code
WorkCenter No
Clock No
(Ticket No is Primary Key)

Table B
Clock No
Supervisor
Employee
(Clock No is Primary Key)

Is there a way for me to create a form, that when Clock No is entered it will be placed in both tables?

Thanks in advance.

View 2 Replies View Related

Tables :: Relationship - Client Data Is Not Populating Into Routing Form

May 20, 2015

I have created 2 tables: Client and Routing.

Each client record is unique. A client can have 'several' routing records.

Key field in Client is 'Client ID'.

Routing table has foreign key of: Client IDFK

I created a relationship of one to many from Client table 'Client ID' to Routing table 'Client IDFK'.

I created a form for the Client table and works.

Where I am having issue is: Client data is not populating into the following Routing form.

* I want a 'Routing form' that you can lookup client info and place it into that form.
* The bottom of the form will be all the routing table fields. The new routing info will be entered into it.

My client needs that form printed for the driver. Client will have many routing forms(records) but only one client record. The driver will have one completed form for each time he picks up client.

View 3 Replies View Related

Tables :: Populating Several New Tables With The Contents Of Original Table

Mar 29, 2013

I have a feed from ODBC to Access DB. I need to populate several new tables with the contents of the original table. I know I'm being stupid but it's been a long week.

View 1 Replies View Related

Tables :: Populating Tables On Entry To Another Table

Apr 12, 2013

I've got a working database with pretty good structure etc. How I would go about creating 3 prepopulated records on a many side of a relationship on entering a new record in the one side of the relationship.

View 6 Replies View Related

Tables :: Auto Populating Fields Within Tables

Nov 23, 2012

I have 2 tables tblworkdone and tbltests, both have a date field and are both subforms in a tabbed form on the main form. When I enter a date into the tbltests subform I would like the date to automatically be entered into the tblworkdone date field and create a new record so that when I move to the tblworkdone subform with the date already there.

View 5 Replies View Related

General :: Populating Database Dynamically

May 15, 2013

I'm creating a database where results of tests for students can be saved. To do this I have a database where a student can be assigned to a specific education. This education is coupled to a number of subjects which in turn have a number of tests.I have made a form in which someone can double click a student which opens up another form where he can mark results for all tests coupled to the education that particular student is following. This report however, only shows tests that have already been created as testdata. So now I'm looking for a way to populate this form with the proper tests.

I assume one way would be to create a button in this form that will create the appropriate tests (look at education of student, get subjects and tests and then create results for those) and then do a requery, but I don't have a clue on how to do this. (the creating part, a requerry shouldn't be much of a problem anymore )

If interested, my databasedesign: (not allowed to post links or images )
img7.imageshack.us/img7/2416/databaseontwerp.png
(education = Opleiding, subject = Vak, test = Toets, result=Resultaat)

View 4 Replies View Related

Populating Tables And Queries?????

Jun 29, 2007

I have created a query from two tables. One table is Job information the other is job notes. How do i populate the query with the job # and job prefix from the job information table? I have the query(field) structure I want but its just gives me blank fields in the query form. How do I populate the fields with the info from the tables?

Thank you!

View 3 Replies View Related

Populating 2 Tables At A Time

Dec 8, 2004

I have two tables created. One contains only names of people, the other contains the names along with other information stored about those names. The names consist of first, middle, and last on both forms, but for some reason, I can only get the middle name onto the names only table and not onto the other table with all the other information. if you'd like to see the db I have it posted to yahoo briefcase, just send me a message for the username and pw.

View 9 Replies View Related

Populating Temp Tables

Jan 28, 2005

Here's the scenario:
I create a temp table structure, which works fine.
I create a recordset from SQL pulling data from a DB2 connection. This works fine.
I want to put the entire recordset result into the temp table.

I have a working means of doing this, but it is very inefficient and leaves the user staring at an hourglass for a minute or two.

What I am currently doing is iterating through each record of the recordset and appending it to the temp table. Desired results, yes, but takes way too much time.

Is there any way to simply 'dump' an entire dataset into the table, instead of on a record-by-record basis?



Here's some code from how I am currently doing things:

Set cmdP1 = New ADODB.Command
Set cmdP1.ActiveConnection = cnnP1
cmdP1.CommandText = "SELECT DISTINCT distribution_id FROM " & sDB & " ORDER BY distribution_id"
Set rstP1 = cmdP1.Execute

Do Until rstP1.EOF
With rst_Temp
.AddNew
.Fields!distribution_id = rstP1!distribution_id
.Update
End With
rstP1.MoveNext
Loop


Any help will be most appreciated! Thanks!

John

View 2 Replies View Related

Tables :: Foreign Key Not Populating

Apr 4, 2013

I have two tables in my Db: tblMaster & tblAddresses. They are joined with a one-to-many relationship, with the addresses being the "one" side. Many entries in tblMaster have the same address.

I use a form, based on a query, to add records which creates the new vendor in tblMaster, and (if I have contact details), a record in tblAddresses. The problem is that while the PK is created in tblAddresses once I add the address info, the related FK field in tblMaster is not populated with that #.

View 5 Replies View Related

General :: Data Not Being Stored In Tables

Nov 18, 2014

I have a database with numerous tables. I have set up relationships between all tables (they are all pretty one-to-many relationships). I then created forms in which to enter and view data. Data entry is fine; however, when I go into my tables, I realize that it is not storing the ID# for related tables in the main table.

For example - I have a table called "ContractTypes" with a field for ID#. That table is linked to the main table called "Contracts. In the form, I have the ContractType as a lookup field, I select the one I want for this entry, but it is not storing that ContractTypeID into the Contracts table. Also, when I go back into the form to enter a new record, the data on the subforms is from the previous record and not the record I'm currently working on. In other words, it does not appear to be linking the ID#s from the different tables.

View 5 Replies View Related

General :: Two Tables With Data That Do Not Match?

Jun 15, 2014

There are 2 tables:

Table 1 has National Insurance Number, first name, last name, phone no, address.
Table 2: has National Insurance Number and email address.

Table 1 is the master table where it will link to table 2 containing the email addresses of the individuals.

However, there will be a lot of email addresses in table 2 which do not relate to any record in table 1 because I do not have the individual in my database yet.

BUT I want to keep their email address because in the future this person may enlist in my imaginary business and therefore - if they do I would have their email address!

1. Is this possible in Access?

In Excel, it would be the case of a simple vlookup from the email address field into another sheet containing the National Insurance Number and email address.

2. Would it be possible to establish a one-one relationship while enforcing referential integrity?

3. Is there a way I can establish a lookup which can be built into table 1 which can lookup the email address of a person in table 2, matching on National Insurance Numbers?

So in the future if new data is input into table 1 or 2 which results in a match of National Insurance ID numbers, the outcome would mean an email address is now paired to the corresponding individual it belongs to.

View 10 Replies View Related

General :: Auto Populating Field In Form?

Nov 20, 2013

I actually have two questions.

1. I was reviewing the Access 10 commandments and I wonder why using "lookup" is evil?

2. I am working on a db which contains approximately 14 tables. One is tblMaterialslist which contains the following fields:

ID - Autonumber
PartDescription - ShortText
PartCost - Currency
Size - ShortText

Additionally, I have a form in which I would like the "PartCost" field to auto populate when the "PartDescription" field is entered.

View 4 Replies View Related

General :: Populating Combo Box With Delimited Field?

Aug 21, 2012

So I'm working on an Access 2007 application for my company's suppliers list, but I am restricted to using a single Excel worksheet for the back-end. One of the fields, called [Files], is a list of files in .pdf format related to a supplier. The number of files can be 0, 1 or many. I currently have this field set up so that each file is separated by a semi-colon. A sample first record in [Files] is:

K:Users est user2008-12-24-ASTM D5857-08.pdf;
K:Users est user2012-03-12APCPP25-Black.pdf;
K:Users est userDIN_16901.pdf

The front-end contains a multi-item form. I'm trying to add a ComboBox (cboFiles) for each record, which is populated by the value in [Files]. Then, selecting an item in the ComboBox should automatically open that file.

I've tried a few approaches so far for populating the ComboBox:
Setting [Files] as the Control Source results in a single item appearing as
"K:Users est user2008-12-24-ASTM D5857-08.pdf;K:Users est user2012-03-12APCPP25-Black.pdf;K:Users est userDIN_16901.pdf"

I also tried setting the ComboBox Record Source Type to Value List and created a hidden TextBox (txtFiles) with the Control Source set as [Files]. Then I used VBA in Form_Load to equate the ComboBox Row Source to the TextBox. The code is simply:

Code:
Private Sub Form_Load()
Me.cboFiles.RowSource = Me.txtFiles
End Sub

While this does create a ComboBox with the correct items for the first record, it also populates the ComboBox for all the other records with the same thing, ie. every ComboBox has the items:
- K:Users est user2008-12-24-ASTM D5857-08.pdf
- K:Users est user2012-03-12APCPP25-Black.pdf
- K:Users est userDIN_16901.pdf

I'm thinking maybe this approach could work with some modification in the code or approach. Unfortunately, I'm not too clear on why the code goes through all the Me.cboFiles.RowSource, but only once on Me.txtFiles or how to get it to check the txtFile for each row. Otherwise, I will need to figure out some other way to fill in the ComboBoxes.

Edit: Actually, I've noticed something peculiar. Each time I select an item from a ComboBox, the other ComboBoxes for the other records also change to the same item. I think this has to do with cboFiles being unbound. Changing it to bound doesn't seem to be quite right though as it just gives the same default values as txtFiles while the items are still just copied from the first cboFiles. Also, I can't actually select any of the items when it's bound. I imagine it might have to do with the read-only attributes of a linked Excel File.

View 6 Replies View Related

Urgent Help Please With Combo Box And Populating Tables

Oct 31, 2004

I am trying to create a database which will act as a timesheet and management system.

I have several tables set up. some are:
01-Staff details containing Staff ID, Names, etc
11-Timesheets containing Timesheet ID, Staff ID, Timesheet Period ID
17-Timesheet Periods containing Timesheet Period IDs, Start Date and End Date.
13-Hours containing Hours ID, Timehseet ID, Project ID, Hours
etc.

I want to set up a Combo that lists Names from Table 01-Staff Details, and when the user selects their name, enters the Staff ID on a new row in the tabll 11-Timehseets.

I am able to create the Combo to list the names from 01-Staff Details, but am not able to get the selected name to jump into 11-Timesheets. The field is not available in the Control Source.

Could you please assist? I am not an Access expert, nor have I done any programming, so a step-by-step solution would be greatly appreciated!!

Many thanks in advance

Sunil

p.s. I would then like to ensure that users can't edit data in certain tables (eg. they shouldn't be able to create a new Staff ID/name, etc)

My next task will be to create a sub-form (which I am able to do) where the user can select a project from a combo-box and enter the hours worked on it. I would like the project and hours to be entered on a new row under the table 13-Hours

I look forward to your reply!

Many thanks

View 4 Replies View Related

Tables :: Populating Foreign Key Field

Nov 16, 2013

i have made two tables with data from an excel sheet. The excel sheet has many duplicates and im trying to eliminate this. The tables are:

tblTasks and tblTeam. Both have autoincrementing primary keys, and the tblTasks table has the TeamID (primary key from tblTeam) as a foreign key.

My question is, how do i populate the TeamID field via perhaps a query, as it is blank on all records. I have over 5000 tasks so a manual approach is what im trying to avoid. A sample of the fields in the tables is as follows:

tblTasks

TaskID - PK
Task Name
Team ID - FK

tblTeam
TeamID - PK
Team Name

there's also a Staff table. tblTeam has a one to many relationship with the Staff table. tblTeam also has a one to many relationship with the tblTasks table.

View 3 Replies View Related

Populating Unbound Related Tables?

Nov 14, 2012

I need to make one database with information about torque values according to one defined table. I make 3N so I can avoid duplicating data, by my problem now is how to enter data into the related tables and save the data using a save button. I want to have control about when data is saved that is the reason because I used unbound controls to enter data and I want to use also a new button to add new records.

View 11 Replies View Related

General :: Linking Tables Without Duplicating Data?

Dec 12, 2012

I have two tables that I'm struggling with how to relate to each other. One is tblHardware which contains all the items that we sell. There are no duplicates in this table. The only columns are a part #, description, long description, and cost.

Then I have another table with is tblPackages. These are the packages we sell which pull from tblHardware. This one just has an autonumber, description, and image (image I'll add later and not worried about now).

What I'm struggling with is how to tie these two together because one Package will contain multiple items from Hardware however there are multiple Packages that have the same Hardware items (17" monitor for example).

View 3 Replies View Related

General :: Enter Data From A Form Onto Two Tables?

Jul 6, 2013

I created a form that allows users to enter data into the fields and populates the table.But can I have it do that and populate instead of one table, two tables?

View 13 Replies View Related

General :: Conditional Combination For Data In Tables

Jun 12, 2014

I have specific query related to Access, Currently I'm trying to create all possible combination of lot data. I need to know if this would be possible in MS Access.Below is simple example of what is required

Table1 Table2 Table3
Value1 TokenW GateA
Value2 TokenX GateB
Value3 TokenY GateC
Value4 TokenZ GateD

I have 3 sets of data for which I've to create all the possible combinations but with restrictions.Below are few of my restrictions

1. "Value2" always comes with "GateC" & Vice versa but, both don't exist independently.
2. "Value4" always takes "TokenW", But "TokenW" exists independtly as well doesn't need Value4 always.

View 1 Replies View Related

General :: Adding New Data Into Tables (via Form)

Jan 31, 2013

I have 2 tables.

Table one is for person ID
Table two is for number of absences

I created a one-to-many relationship with the update/delete option selected. It didn't work when I wanted to add new data into the tables (via form). Then I tried a many-to-many relationship (with a third table) but it doesn't seem logical to do so, as I only need to add particular data into one table, but need a field from the 1st to differentiate records.

(table 1 = one record per person, table 2 = multiple records for 1 person)

View 2 Replies View Related

General :: Comparing Data From Multiple Tables

Nov 5, 2013

We have an "item" table that contains information specific to an item (the item number is unique); and multiple "production line" tables. There is a relationship between the Items table and the Production Line tables (all 8 of them). Each item can be entered on 1 or more production line tables because one item may be produced on multiple lines and the settings vary based on the line they're produced. When entering an item in the Production table we MUST make a selection from the Item table.

Now, what I want to do is identify which item number(s) from the Items table does not have a specific field entry in ANY of the Production Line tables.

View 9 Replies View Related

General :: Accessing Data Tables In DB With Front End

Sep 7, 2012

I work in an office with Office2000. One branch of our non-profit organisation had a volunteer design a database in Access to track the usage of rented storage bins.The database works great for its intended purpose, however the volunteer is not available and the front-end is so locked down that I can't get into the table structure to run some usage queries I need for my own job, which is Resource Development (donations).

I don't know what to say to give more info, because the computer is a stand-alone in a different department detached from the network, so I have to physically go to the other end of the building & work in it, I can't get screencaps etc easily, and most of the menus have been disabled so it doesn't even give me the info about the version of Access etc.

View 6 Replies View Related

General :: Populating Field Based On Combobox Selection?

Oct 29, 2013

I have a table of raw materials that have two fields, their stock code and longer description.

I have another table that is tracking the usage of these materials. I want both the stock code and longer description to be recorded in the usage table.

I want the "Description" field in the usage table form to auto populate when the user selects the corresponding stockcode from a combobox.

View 3 Replies View Related

General :: Auto-populating Rows Based On Primary Key

Jul 24, 2012

I have an infopath form, which people in my company can fill out to order parts. The form is linked to an Access database. The primary keys for the database are the Order Number and the Line Number (where the specific part info is in the system). The form also takes info about who entered the order, when they entered it, etc.

My problem is that while the form will be used for only one order, it may be used to order multiple parts. So the infopath form has a repeating section with a table where they can enter multiple line numbers and part names, etc.If I use the form to enter just one part, everything works great. But when I use the repeating section to add multiple parts, the information specific to the repeating section is added to the database but the rest of the information (who entered, when the entered, etc) isn't.

My intial idea was that since the first part is entered correctly and the subsequent parts are missing some information which is already in another line in the database. Maybe if there was a way to pull the information from another line into the lines with missing info within access would solve my problem. So basically automatically populating the rows with matching Order Numbers with the information that is missing.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved