Tables :: Populate A Field From One Table To Another

Aug 7, 2013

I have a small database to keep track of some student information. Right now I have one table where a student is first entered with their contact information and then other tables that house specific information about other areas related to the student (if there's no longer with the school, paperwork is complete, etc.). The primary key for all of it is their student ID. The tables are linked parent/child with forms, etc., so that when I open a form having to deal with one of the related tables (student active status), and pull up the student's ID from the first table, it'll then populate that ID into the status table, even if something else isn't added.

What I need is really that when I create a new student on the first table with their contact information, that their ID is populated into all of the related tables, without waiting for additional information to be added. This is because I need to run reports to see who is missing information, etc., and without their ID showing up in that second table, I'm not getting accurate results. How would I set this up?

Sample of tables with smaller number of fields:

Table 1 - Contact Info (always done first for a new student)
Student ID
Name
Address
Email

Table 2 - Student Inactive Status (want the ID numbers to be the same quantity as table 1, even if Student ID is then the only completed field in this table)
Student ID
Inactive status type
Date

Table 3 - Paperwork (same issue as table 2)
Student ID
Submission Date
Approved By

View Replies


ADVERTISEMENT

Tables :: Auto-populate Table Field From Calculated Field In Another Table?

Jan 15, 2015

I am creating a database to keep up with the expenses for my art business. I have a table for Vendors with a field (Mileage) for one-way mileage that I enter manually (EX: 11.8 miles). Then I have a calculated field that basically just multiplies the Mileage field times two to get total mileage to and from my house to vendor location. (Total Miles) {The reason I want to do this this way right now is because I didn't keep up with my mileage this year and I am having to look up the distance from my home to each location)

Then I have a "supplies" table where I show the date, the vendor, the item, cost, etc, and I want to add a Total Mileage field based on the calculated Total Miles field in the Vendors table that will automatically populate based on the vendor of each transaction. I just can't remember how to make this work and I know just enough SQL to be dangerous.

View 2 Replies View Related

Tables :: Auto Populate Field Based On Answer Of Another Field In Same Table

Feb 28, 2013

Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.

I am not quite sure how to lay this out. I am using Access 2010.

View 8 Replies View Related

Tables :: Auto Populate Field In Table

Feb 15, 2015

I have a table ([AllNames]) that a field ([Past_Employers]) is being auto-populated, and I don't want it to.

[Past_Employers] population is setup form a form, which references a separate table of employer names [Employers].

If I enter a new record directly in the [AllNames] table the [Past_Employers] field populates.

[Past_Employers] is a multi-value field (yes ugh - I know!!!) and is not required.

View 4 Replies View Related

Tables :: Auto-populate By Matching Data To Another Table And Selecting Corresponding Field

Sep 16, 2013

I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields

Purchase Currency
Exchange Rate

I also have another table called Exchange Rates 13/14 within which there are 2 fields

Currency
Exchange Rate

When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:

SQL Tried

1.
UPDATE Costs
SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency

2.
UPDATE
Costs
SET
ExchangeRate = [Exchange Rate]
FROM
[Exchange Rates 13/14]
INNER JOIN
[Exchange Rates 13/14]
ON
Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency

3.
SELECT [Exchange Rate]
FROM [Exchange Rates 13/14]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency

I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.

Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.

View 4 Replies View Related

Tables :: How To Auto Populate Field 2 From Key Field

Oct 17, 2012

I need a continuity field...transaction number..that can be both a parent and child key. Would like to create this upon accessing a new record / row.

View 3 Replies View Related

Tables :: Populate Value Of Field From Value Entered In Another Field?

Oct 25, 2012

automatically populate the value of a field [Industry] in a table after a user has entered the value in another field [IndustryCode] in the same table. The [IndustryCode] and "Industry" are stored in a separated table [2012IndustryCodeTable]. I have tried using DLookup and cannot get it to work.

Ex.

|IndustryCode|Industry|InvestigationDate|
|001 |Car |
|002 |Plane |

How can i get the [Industry] to populate the information from the [2012IndustryCodeTable] once a user enters the code into the [IndustryCode] field in a table?

View 3 Replies View Related

Tables :: Auto Populate Field With Now Date Stamp

Jan 25, 2013

I need to autopopulate a field with now datestamp when another field has been filled in?

View 10 Replies View Related

Forms :: 2 Tables - Auto Populate Field In A Form

Oct 7, 2014

I have 2 Tables with following fields

Table [APM]: apm_id, apm_owner, apm_values
Table [DATA]: apm_id, serv_name, serv_place, serv_choice, serv_role

Both tables are joined with 1 to 1 relations based on apm_id as both of the fields are same. The table [APM] is pre filled with data. The idea is to list data from [APM] and collect data from user to be saved in [DATA] based on the info from [APM].

I created a form which list all the fields from both tables. The fields from table [APM] are only for displaying data and created a combo box based on [APM].apm_id to display the fields by chosing an APM ID from the drop down box.

A user adds a record and moves on to second record, completes the second record. Now if the user wants to list the first record by chosing the APM ID from the drop down box it only populates the fields from table [APM] but not the fields from table [DATA] which the user just added. Infect the fields related to table [APM] changes, but the fields from table [DATA] stays the same.

How ever if I click on the small arrows on the bottom which lets you cycle through records than it populates all the fields from both tables correctly.

How can I fix the issue so that all the fields would populate based on APM ID selected from drop down box.

View 4 Replies View Related

Tables :: Auto Populate Date Field Just Once (on Creation)

Jul 21, 2015

First hurdle in my database tables, I have a field called Date_Created which I want populated just once, whether from a form or an import.

I have another field called Date_Updated which I want to update any time the record is changed. I know I can do this by using the default value of Now(), but how can I get the Date_Created field just to update when the record is created and not any time after ?

I also want to show these values on the form, but have them as view only, hoping that is possible ?

View 5 Replies View Related

Make Field Auto Populate Based On Value Of Field In Another Table

Mar 4, 2008

I'm creating a database that keeps a track of questions and scores.

The questions in the database need to be dynamic and are changed frequently.

I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.

I want the question field in my scorecard table to populate with the value in my question table.

I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:

SELECT tblQ1.Q1
FROM tblQ1;

This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?

View 1 Replies View Related

Tables :: Populate Lookup Table With Data?

Nov 22, 2013

I have problems understanding lookup tables, especially how to populate them with data. I'm working on an exams database, and have many such tables...

Table for Students
Table for Subjects
and a lookuptable for studentsubjects

Table for Classes 1, 2, 3, 4
Table for Subjects Eng, Math, Geo, Chem... 11 subjects in all
and a lookuptable SubjectClasses

Having inserted data for all subjects and all classes, should I then go ahead and Insert data for SubjectClasses? There will be 44 items!

Even more appalling, studentsubjects table will have (no of students * 11) for my small school, it will be 1,760

View 8 Replies View Related

Tables :: Populate A Table With Report Names

Apr 1, 2015

I want to populate a table with the database's reports.

And somehow be able to select a row and open a report in design view.

View 4 Replies View Related

Populate Junction Table Related To 3 Tables

Dec 8, 2013

I explain , I have 5 tables :

Class ( classID , classname,... )
Student (studid , firstname, lastname , classID ,....)
Course( courseid , coursename )
Term ( termid , termname , begindate , enddate )
Score ( scoreid , studid , courseid , termid , score)

Note :
1) A class can contain one or more students (one-to-many between Class and Student tables)

The table "Score" is a junction table between three tables : Student , Course and Term because it contains three foreign keys ( I could use a combination of 3 foreign keys to make a primary key ! ) .

The tables ; "Class", "Student", " Course ", "Term " already contain data for each table I created a data entry form .

My biggest problem is how to create a form to enter students' grades or scores for each student that belong to his class.

I do not know how to do it especially since the idea is that :

On a form I would like to use ComboBox to select a class that displays student's list from class selected and a ComboBox in same form to select course and another ComboBox to select a term and then enter grades or scores for each student

This is my general idea to enter students' grades. The rules are :

A class contains one or more students
Each student takes one or more subjects.
Each student gets scores for each subject and each term(quarter)

View 5 Replies View Related

Tables :: Populate Date Based On Specific Text In Another Field

Mar 27, 2013

I would like the "DateOfConfirmation" to populate with today's date when "SSurvDiagThisYear" is Confirmed.

The DateRecordCreated is a simple =Date() that populates when the record is entered into the table.

SSurvDiagThis Year is the case outcome - Pending, Probable, or Confirmed. It is possible that the case could be confirmed on the same day it was entered into the table but that is RARE.

I am hoping for the The DateOfConfirmation to capture the date the case is confirmed so that I can gather some duration between the case being opened to confirmed.

SSurvDiagThisYearDateOfConfirmationDateRecordCreatedIdentificationNoPending12/31/2001269Pending4/1/1999270Confirmed7/29/2001338Pending5/14/2009375Confirmed2/20/2012440Pending3/30/2001543Pending7/1/2000552Confirmed3/30/2001596Pending8/3/2001649Pending6/15/2001672

View 1 Replies View Related

Tables :: Get Table To Populate When User Moves Onto Next Record?

Sep 20, 2012

My form has been set up to enter and display info.However, the table it goes into is not populating until the user has finished his/her session and closes the form down. We need this info to update earlier, preferably on entry, as we require certain elements to be able to populate other forms.

The MainForm displays all information and the School or Team name is entered through this form, going into the School or Ministry table.This generates an autonumber which is then used in the TeamMember table to identify team members within each team. the only way we have access to this autonumber is by shutting the mainform down and then reopening it. Which event in the property sheet do I require to get the table to populate when the user moves onto next record?

View 1 Replies View Related

Union Query - How To Populate 5th Table With All Data From 4 Tables

Sep 24, 2014

I have 5 tables in access 2010, 4 of them have data in them and I need to populate the 5th table with all data from the 4 tables. I know you have to create a Union query, but i dont know the sql statement

Table 1 - data
table 2 - data
table 3 - data
table 4 - data
table 5 - combine data from table 1, table 2, table 3, table

View 4 Replies View Related

Queries :: Populate The Field From Another Table

Sep 25, 2013

I am trying to populate a field on Table (Drawing Sheets - Arch) from field on Table (Project Information). I had attached an image for reference.

Table (Drawing Sheets - Arch) has a relationship with Table (Project Information) which is the Project_Number. I can populate this manually but it will take me forever as this are 500+. I had done the other Field (Revisions) using a Query but this is not linked to another Table so it is easy to type "0".

I had tried to make a Query and using the "Build" (Right-click from the Update to) but I couldn't make it right.

View 2 Replies View Related

Auto Populate Table Field

Sep 12, 2012

So I have this query that runs ....

Code:
Expr1: Left([EVENT_TBL].[EVENT_TYPE],3) & Left([EVENT_TBL].[EVENT],4) & Format([EVENT_TBL].[EVENT_DATE],"ddmmyyyy")

How can I get the result into the [EVENT_TBL].[EVENTTYPDTE_ID] Field?

View 3 Replies View Related

Auto-populate Junction Table Based On Equal Fields In Different Tables?

Jun 24, 2014

Is there an easy way to auto-populate a Junction table [in access 2010] given the following two tables with a many-to-many relationship for Tasks? The two tables are

Table 1) tblTasks (TaskID (PK), Description), and

Table 2) tblMeasures (MeasureID (PK), Description, Tasks)

If JCTN table is JCTN_Tasks_Measures (TaskID, MeasureID), is there a way to populate when tblTasks(TaskID) == tblMeasures (Tasks)?

View 1 Replies View Related

General :: Populate Calculated Field Into Table

Aug 6, 2013

I need to populate a table with Grid reference values consisting of 6 figure integers, eastings and northings.

I am receiving/downloading the Grid Refs in a UK Grid Tile format, with 3, 4 or 5 figures preceded by letters.

E.g. SK 456 849, SP 6789 4356.

I have used some query functions and written a bit of VBA behind a form to convert the received coordinates into the full coords I want. The coordinates are now in unbound controls on a form, and I want to pass these back to a table so I can use the data for display purposes.

Ideally I want to link to the table from a GIS and autoupdate without having to open the database (my imported data is linked in to feed the database automatically).

I have done some searching and not found a method to pass the values into a table - What is my best way of doing this automatically?

View 10 Replies View Related

General :: Lookup Into A Table - Populate Same Field

May 28, 2014

I have a table holding a list of post codes, and their servicing depot EG -

ID Postcode Depot
1 AB10 Edinburgh
2 AB11 Edinburgh

Then a form, which has a field for depot ( Fld_Depot )

What i would like is, when the user clicks on the field, a msg box prompts, asking for them to input a postcode

Once the postcode is entered, it populates that field with the relevant depot from the table....

View 3 Replies View Related

Command Button To Populate Field With Value From A Table?

Oct 14, 2011

What is the best way to get a Command Button to populate a field on a form with a value from a another table in the database?

View 3 Replies View Related

Populate Textbox With A Field From Another Combo Box's Row/source Table

Jul 18, 2005

I've designed a data entry form based on a table. I use a few combo boxes, (linked via SQL statements for their row/source) to fill most of the fields in the table.

What I want to do is populate one textbox on the form with the contents of a field in one of the combo box's row/source tables. The field I want isn't shown in the combo box.

Basically, what I want is that when I choose a PART NUMBER from a combo box, I want the OEM_ID from the same table to jump into the textbox below it.

I think I may have tied myself in knots though to the point where what I want can't be done. Any ideas? I know this is probably going to take a couple of goes at explaining. :P

View 11 Replies View Related

Auto Populate Date Field In Payment Table

Nov 8, 2012

I am new to Access and have started to move across some Excel based programs.

I have a simple example with two tables and a form where on the form a customer ID is entered which displays the name and below it is necessary to enter the total payments made by that customer for the day ( all records for the same day are entered at one time, which is why I would like to autofill the date).

I can enter the date directly on the form for each customer entry but I would like to automatically enter the required date in the payment table using the value of a datepicker on the form to ensure that no date errors are made.

I have been trying to use the expression builder to do this but the datepicker does not seem to appear as a valid option.

View 4 Replies View Related

General :: Auto Populate A Table Field From Combo Selection

Jan 17, 2013

I have a Table (tblFPAData) that is being populated with entries from a form I created. There are several fields but if I can get an answer for one relationship then I can repeat it for the others.

In tblFPAData there are the fields MaterialNo and Description.

I am creating another table called tblSchedule and using a form to enter the information. I need the user to select the MaterialNo from a combo (cmbMaterialNo) and the then table auto populates the Description from tblFPAData.

View 3 Replies View Related







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