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 Replies


ADVERTISEMENT

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 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 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 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

Auto Populate Data From One To Another Field In Same Record

Aug 16, 2012

I have 2 fields, a "Total' field and a "Submitted" field. The 'Total' field value will never change as it is used for reporting purposes but the 'Submitted' field will be edited as the work is completed but the initial value should be the same as the 'Total' field.

Is there a way to have the data in the 'Total' be set as the default value for the 'Submitted' field?

View 7 Replies View Related

Access 2010 Selecting Data From A Table And Auto-filling Fields

Aug 14, 2015

I have staff table which is a lookup field on the phone call table.when the phonecall form opens I want the employee field to auto fill in the employees name from the lookup field according to there security level.so something like this

On Open
Select Case Forms!frmLogin!cboUser.Column(4)
Case X = 4'the employees security level'
Case X = Insert Table_tblPhoneCalls!EmployeeID"4" into Form_frmPhoneCalls!Employee.
End Select

of course this is a syntax error as I do not know the correct code words.

View 1 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

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

Forms :: Auto Populate Date Field During Data Entry

Aug 14, 2015

I have created a basic Volunteer Events and Hours Volunteered tracking database.

I created a form Volunteer Hours to allow an end user to use that form to record the volunteer and hours volunteered on a specific Event. There are two tables needed in the form Volunteer Hours to be used for the end users to record the Event, Volunteer, Date and Hours. The form has drill down fields from two tables:

Table A - Volunteer Events (event name, date, descr, owner, co-owner, loc, status)
Table B - Volunteers (name, co, div, emp#,title)

I want to be able to select the Volunteer Event and have the event date automatically populate in the Volunteer Hours form date field when the Event is selected to eliminate the end user from having to enter the date in.

Also; I would like to be able to add more than one volunteer at a time to an event on the form, this would also decrease the data entry time.

Are one or both of these things possible?

View 4 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

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

Auto-populate Data Into Field Based On Information Typed Into First Record

Sep 3, 2014

I'm trying to figure out a way to get my access database to auto-populate data into a field based on the information I type into the first record.

So what I'm looking to do is that when I type a name into the UserName Field it will automatically fill in the rest of the field with the same information.

To clarify I'm looking for a way so that when I type something into one record it will fill the rest of the field in with that same information.

View 8 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

Forms :: Auto Populate Fields And Save Data To Table

Dec 16, 2013

In my UpdateForm I have 3 fields. PartNumber, Description and SerialNumber. I use 2 tables for these. My MainTable and PartsList Table.

In my PartsList table I have the list of PartNumber in Column (0) and Description in column (1).

In my MainTable I have 4 fields: TransactionID (autoNumber), PartNumber(text), Description(text) and SerialNumber (text).

Now, In my UpdateForm I want the user to just select the PartNumber with a combo box (that also show the "Description" (I created this using the combo box wizard)). But I want to auto populate the field in my "Description" text box every time the user will enter new record and will also update my MainTable with all the values they entered in my UpdateForm.

I tried this codes in the after update of PartNumber combo box (properties):

Description = Partnumber.Column(1)

I tried also:

me.Description.value=me.PartNumber.column(1)

both codes unsuccessful.

View 3 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

Forms :: Auto Populate Field Based On Combination In Different Table

Oct 17, 2014

I have two forms - Main and StudentAdvisor (filtered by student ID#) SAdvisor.jpg

The main form originally had a combo box field to display advisor, the problem is that some students had multiple advisors during the course of their program. This problem was fixed by the creation of a StudentAdvisor (table/form) for recording ALL advisors. I set the form to display by newest term so the current advisor (or initial if no change) is the displayed record.

On the main form I then added "add" to open the StudentAdvisor form for entering information, and an unbound txtfield to display their current advisor (or initial advisor if no change).

The problem is I can not get the txtfield to display the information. I have managed to get the error ( #Name?)

Advisor Type 1=Initial, 2=Current, 3=Previous
Changed 1=Yes, 2=N/A

so a record would record as:
Initial and N/A
Current and Yes
Previous and Yes

I tried writing DLookup but could not get that to look up the combinations (above) and display either initial or current.

also tried pulling form SA = Advisor to form Main = txtAdvisor

For report purposes I need to know not only current advisor but how many were assigned during the program and also how many students each advisor was assigned during certain time frames.

View 1 Replies View Related

Find Matching Dates In Tables A And B / Then Populate Blank Fields In A From B

Apr 30, 2012

I have a table tROE with a field listing all dates starting 1/1/10 to date (populated), and three fields for currency exchange rates [USD], [RSD] & [EUR] which are empty and need to be populated. I have another table tROEPartial that has the exchange rates for some of the dates starting 1/1/10 but not all. Their structures are identical. I want to add the exchange rates from tROEPartial to tROE where the dates match, leaving the unmatched fields in tROE blank.

View 3 Replies View Related

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 5 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

Selecting A Field From A Popup Form To Be Populate Another Text Box

Apr 6, 2008

I have a field on frmOutpatient which is called EpisodeID.
I need to populate this field from a selection in a pop-up form.

The pop-up form (frmSelect) is a continuous form with a number of different EpisodeID numbers according to the date the patient was admitted. At the end of each record I want to put a command button (cmdSelect) and when pushed I want this SelectEpisodeID to be transferred to the field on the frmOutpatient.

Any ideas??

View 6 Replies View Related

Forms :: Matching ID Fields To Auto Fill Form Field

Aug 30, 2013

The table ProductInfo has an ItemID, and its corresponding Quantity.The table Orders has a PO column, an Item# column, a OrderQuantity column, and the column Item#CurrentStock which holds the Quantity of the selected item, for 3 items.I have created a form that has a ComboBox populated with the ItemID's from ProductInfo, that when selected populate the corresponding Item# in the Orders table.

I would love if the Quantity(stock) from ProductInfo would automatically fill the Item#CurrentStock field on the form when the ItemID is selected from the ComboBox.But all of the fields only relate to Item# from the first ComboBox!?!?I have tried =dlookup,

View 3 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

Auto Diplaying Second Field After Selecting Another One.

Feb 2, 2005

Two tables, Contacts and Jobs

On the Jobs I need to select the ID and show the Last Name, but only enter the ID. This has been done okay.

How do I get the Last Name to auto fill in from the selection of the ID number?

View 6 Replies View Related







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