Calculate Field Using Different Field From Different Table?

Jun 2, 2012

i have got this table

Table 1:-- List of available classes

Design view

Class -- Primary key ---text

Subject ----text

Num. Lessons---- Number

Enrolments ---- Number

Max Class Size--- Number

Action -- Calculated because if enrollment is higher then max. class size then its says full otherwise it will tell enrol

Table 2:-- Teacher And Class Income

Class --- text

enrolment --- number

max class size ---- number

income per enrolment ----- currency

incomer per class ----- currency ?

1) but what i wanna do is if i change enrollment and max class size VALUE IN TABLE 1 ... i want that change in table 2 enrollment and max. class size FIELD AS WELL For e.g. if i change enrollment =25 and max class size = 30 ,,, i want that change in table 2 .......so i want that table 2 should display Enrollment = 25, max class size = 30 ,I want this things in TABLES not queries , what should i do... and i compulsory have to use access... NOT EXCEL.

2) then i want that Total Incomer per class (In Table 2) = Enrollment * Income per enrollment so that value should be display on total incomer per class ....So if i change enrollment value ... then the total income per class value has to change ....

3) i want in table that my total income in all class should display in same table ( Table 2)

View Replies


ADVERTISEMENT

How To Create A Field To Calculate Value Which Obtain Value From Different Table

Jan 1, 2013

I'm trying to summarize the value from multiple fields in a table and the total value will be updated on a different table as per highlighted below (taken from Northwind Web Database).

View 3 Replies View Related

Tables :: Auto-Calculate Field From Other Table Values

Aug 10, 2015

How to set up my tables as I'm just starting off with setting my database up.

I'm doing a simple database to track the purchase orders (PO) I am managing. Each PO has a PO Number and an Original Value. POs may have multiple amendments which would change the PO value. I would however like to keep the history of the PO original value and all different amendments.

So I created two tables:

tblPO:
ID
PO Number (Number)
PO Original Value (Currency)
PO Sum of Amendments (???????????)
PO Current Value (Calculated = PO Original - PO Sum of Amendments)

tblPOAmendments:
ID
PO (Lookup from tblPO)
PO Amended Value (Currency)
Amendment Date (Date/Time)
Amendment Desc (Text)

Now the two, million dollar questions are:
1.) Is this the right table structure to use.
2.) How do I go about calculating the Sum of Amendments field?

View 4 Replies View Related

Tables :: Calculate Value For A Field Based On Value Of Another Field - Statement

Dec 30, 2012

I'm trying to calculate a value for a field based on the value of another field, [Field1] has a value list of 28 choices I want [Field3] to take that value and multiply it by the value of [Field2]. I'm using an Iif statement and it sort of works. I looks like this:

IIf([Field1]=1 Or 2 Or 3 Or 4 Or 11 Or 12 Or 13 Or 21 Or 22 Or 28,[Field2]*0.06,IIf([Field1]=5 Or 6 Or 14 Or 15 Or 23 Or 24 Or 29,[Field2]*1.1,[Field2]*2.1))

I receive nor errors but it will only return the value of [Field2]*.06 no matter what is selected in [Field1].

I'm sure I'm missing something and there is probably an easier syntax to use, but I'm at a loss at the moment.

View 4 Replies View Related

Query To Calculate Field Depending On Value Of Another Field?

Oct 13, 2005

hey guys . .

I have 3 fields: TEST, NETQTY, EXTENDED

NETQTY usually = EXTENDED depending on the TEST

BUT the Test PTCGCD has a EXTENDED value that is TWICE the value of NETQTY

How do i create a query which will update the EXTENDED field depending on the TEST value>?

For Example:

The general TESTS: AFP, ANAS, CYC etc . . EXTENDED = NETQTY(1)

PTCGCD: EXTENDED = NETQTY(2)

?

View 1 Replies View Related

Calculate Field Using Sum Of Another Field

Apr 18, 2008

I want to create a calculated field in a select query.

Cost Per Call = Total Cost / Total Calls

The Total Cost is a fixed value that will be entered after the user is prompted.
The problem is that Total Calls is the sum of another field in the query called # Calls. Is it possible to calculate a field that uses a sum of another field in the query ?

View 2 Replies View Related

Calculate Totals In A Field

Dec 6, 2004

I have 4 fields in my table and form (Towing Charges) (Storage Charges) (Other Charges) and (Total Charges). I need to have Towing Charges, Storage Charges & Other Charges to calculate and populate in my Total Charges field. I tried a script in the control source of the field, but its not working. I probably have the script wrong.

Thanks Everbody

View 3 Replies View Related

Auto Calculate Field

Feb 1, 2006

Hi all,

I'm trying to set up a holiday register. In my holiday table i have the following fields HolidayID, PersonID, StartDate, EndDate. I would like to have a final field which auto-calculates the number of days taken between start and end date. I read somewhere this should be done in a query using the DateDiff expression but I can't seem to get this to work.

Any ideas please?

Thanks all!

View 3 Replies View Related

How To Calculate Field In Query

Jun 5, 2007

Hi Friends,

I have made one form based on query. Its a invoice entry form. I have price, quantity and amount. I want when i enter price and quantity, amount should be calculated. I know its very simple for you. Any suggestion.

thanks

mithani

View 2 Replies View Related

Automatically Calculate Field

Dec 16, 2005

Hi guys,

I have a mainform "frm_CaseReference" and a subform "subfrm_CasesControls".

In the main form I have "DOB" field and in the subform I have "DateSlideTaken" and "AgeAtSmear" fields.

I want to automatically calculate age in the "AgeAtSmear" from the "DOB" and "DateSlideTaken" but having problems.

I have tried the following code but it doesnt work:
=DateDiff("yyyy",Forms!frm_CaseReference!DOB-[DateSlideTaken],Now()

I have read it is not good idea to store age but my work place want this so i have to include it. Can someone please help....

View 2 Replies View Related

Having A Field Calculate Other Fields

Aug 15, 2007

I am trying to get one of my fields to calculate this expression which includes other fields:

If "Financing Type" = 1 or 2 THEN 20% * "Loan Amount" OR
If "Financing Type" = 3 or 4 THEN 100% * "Loan Amount" = 2,000,000.


I thought that this would work, but it only works in queries or reports.

=IIF([FinanceType]<3,[LoanAmount]*.2,[LoanAmount])

I would like this to work in the table.

View 11 Replies View Related

Field To Calculate Time Elapsed

May 15, 2006

I have nearly achieved this from an example on the Microsoft site but am having problems with the subforms. Can anyone take a look and if possible provide me with guidance as to what I am doing wrong. The instructions are included.Any help with this would be very much appreciated. Kind regards. Bernard

View 6 Replies View Related

Calculate Numbers From A Text Field?

Dec 27, 2006

Ok, this one has me stumped.

I have a several fields on my form that are list boxes that must have text as the properties so I can populate the lookup data with things like" Severe rating = 7 points" and "Moderate rating = 3 points". I have another similar field where the choices are "High probability = 9 points" and "Low probability = 2 points" etc. A third field needs to show the total points (product) of the choices from the first two fields, i.e. 9 points x 2 points = 18 points. Then,
a fourth field needs to display where the answer falls in a grid, i.e. 1-14 points = Low, 15-29 points = medium, 30-49 points = high, etc.

How do I apply the calculations on the field choices if the field properties have to be text instead of number?

Thanks,

bugleboy:confused:

View 10 Replies View Related

Calculate Query's Field With Two Result

Sep 28, 2005

Hi All
I try to create a query based on Table1 and fields Date and Result. Is it posible to get a query that calculate two amount of Result field when that field is null and not null?
That two values of Result field I will use to create monthly Pivot report in which each bar will display amonts Completed and NonCompleted result.
Thanks.

View 1 Replies View Related

Query To Calculate Conditional Field

Feb 5, 2007

Dear all,

I have a query that is based on orders table and contains "Qty", 'UnitCost', and "ctualUnitCost", in addition to yes/no control to indicate if "SalesTax" is applicable.

I want an expression in the query to calculate total cost in one of 2 conditions:
if no sales tax then TotalCost = Qty*UnitCost
if sales tax is applicable then Total cost = Qty*UnitCost*1.1

how can I write this expression

thanks

View 2 Replies View Related

Calculate # Of Occurrences Of String In A Field

Jun 25, 2007

Thought I'd be able to find out easily, but.. here I am...

How can I query the number of occurrences of a string in a field, e.g.:

if a name field has the text: Martin Lacoste

how can I get it to tell me there are two "a"s in the field?

I can use InStr to find one, and make a few more queries to find a few subsequent, but the data I need to search could have 30-40 occurrences of the desired text in a field.

Ideas?
Thanks!
Martin Lacoste

View 6 Replies View Related

How To Calculate The Accumulate Value Of The Current Field

Jan 21, 2005

If I have the field name as below:

Oil quantity
Accumulate Oil quantity
Date
Recordnumber


I want to get value of "Accumulate oil quantity" of current record to be equal to sum of "Oil quantity" from first record till the current record:

Let say
after I input "oil quantity" in the record # 1 ,2 & 3
The "Accumulate oil quantity" of record value show below:

"Accumulate oil quantity" of record#1 = "Oil quantity" of record#1
"Accumulate oil quantity" of record#2 = "Oil quantity" of record#1 + "Oil quantity" of record#2
"Accumulate oil quantity" of record#3 = "Oil quantity" of record#1 + "Oil quantity" of record#2+"Oil quantity" of record#3

Anyone know , please help
Thank you so much

View 1 Replies View Related

Calculate Text Box And Insert To Field

May 15, 2012

i have 1 table name "table1"and i have in this table 4 fields

1) id
2)pay
3) tax
4) total

The id is primary. I have form in this form i have 3 text box "pay" "tax" "total". i want to insert some number to pay and number to tax and make some button to make calculation of the 2 text box and it will show me the result in the total and insert them to the fields.

View 3 Replies View Related

Calculate Numerical Data In A Text Field

Nov 29, 2004

is there any way I can make a field with a data type that calculates numbers in the field but also allows text to be entered into the field (e.g. N/A or No Score) The non-numerical data certainly wouldn't have to be calculated and could be filtered out when calculating averages and other numerical operations.

Thanks

View 2 Replies View Related

Queries :: Calculate Multiple Values Same Field

Feb 22, 2014

I have a table where there are multiple vehicles, each identified by their vehiclenumber. Each record holds the vehiclenumber, date and odometer reading. I need to figure out how to calculate records in this table per each vehiclenumber.

Below is a code that works, but only when i have each vehicle with the same vehiclenumber.

SELECT tblOdometer.VehicleNum, tblOdometer.ODate, tblOdometer.Odometer, tblOdometer.Odometer AS OdomAlias,
Nz(DLast("Odometer","tblOdometer","[Odometer] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference
FROM tblOdometer;

View 4 Replies View Related

Field In A Database That Calculate Age Of A Person Based On DOB?

Jun 23, 2014

I need to have a field in a database that will calculate the age of a person, based on their DOB. I have it in the excel sheet that I imported into Access to create the data base, but it just copied the number that was already there. When I add new entries into the database, the age isn't calculated. How do I do this?

The forumla I use in my spreadsheet is: =DATEDIF(O2,TODAY(),"y")

View 1 Replies View Related

How To Calculate Number Of First Letters Of Names In A Field

Sep 9, 2011

how to write out the query Im trying to create so I'll create an example:

Field: Drivers Names
I want to know how to create a query that will calculate the # of drivers names which starts with, A, B, C etc.
I want my query to be:

Drivers names first letter # of first letters
A 10
B 19
C 15

how I can create such a query

View 10 Replies View Related

Queries :: Calculate Cumulative Values Stored In Field

Oct 7, 2013

I have several projects with different tasks for each. I have 3 fields [fkProjectsID], [TaskOrder] and [Duration] in a table for project tracking with that structure:

[fkProjectsID] [TaskOrder] [Duration]
1 /1 /5
1 /2 /8
1 /3 /15
1 /4 /6
2 /1 /8
2 /2 /30
2 /3 /25

I want to calculate cumulative values stored in [Duration] field (represent a number of days). I'm using the field [TaskOrder] to order different tasks within each project. With some testing, I was able to calculate cumulative [Duration] with 1 project using the DSum fucntion as following:

CumulDuration: DSum("[Duration]", "[tblProjectTracking]", "[TaskOrder]<=" & [TaskOrder])

I was having the sequence: 5, 13, 28, 34 for respectively Task 1,2,3,4. However, when I add a second project (and then a third...), I need to be able to filter based on [fkProjectsID] as well (i.e. a specific DSum by ProjectsID). I add this criteria but I get the sum of [Duration] on each row instead of the cumulative:

CumulDuration: DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] And "[fkProjectsID]=" & [fkProjectsID])

View 3 Replies View Related

Forms :: Calculate Field Based On Second Column Of Selection

Nov 20, 2014

I have a form, where one field is a combo box

The combo box is two columns linked to at table,

task and sla

You pick the task, and it displays the task

What i need is, another field, to equal the SLA part of that task.

View 5 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 :: Update A Field In One Table Based On Status Of A Field In Another Table

Aug 2, 2013

I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.

I want to prepare a report from the projects table that lists projects IF they have any open tasks.

In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'

View 7 Replies View Related







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