Tables :: Need Multiple Calculated Fields In One Table

Aug 8, 2014

I am great with Excel but not soo much with Access 2010. I Excel, what I needed to do was very simple but duplicating what I did in Access is not soo easy.I am trying to calculate billings for FSA & HRA. I have set up one table with all the data. I want to add columns to the table to calculate:

1. Is there an account balance-excel formula:
2. If the account is still active
3. If the plan year run out is "active runout" or "runout over"
4. calculate each account type with a rate *count of FSA accounts = total to bill

Here are my excel formulas that for the life of me I can't get to work.

Account balance==IF(V2="HCRA",(IF(AB2-AD2>0,"Available Balance",IF(AB2-AD2=0,"Zero Balance","Negative Balance"))),((IF(AC2-AD2>0,"Available Balance",IF(AC2-AD2=0,"Zero Balance","Negative Balance")))))
Active runout/runout over=
=IF(AG2="YES","Active Runout",(IF(S2=W2,(IF(AF2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")),(IF(AE2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")))))
Active account=
=IF(D2<$AJ$1,"NO",IF(W2>$AJ$1,IF(S2=W2,"YES","NO") ,"NO"))

Can I have calculated fields that refer back to a calculated field?

View Replies


ADVERTISEMENT

Tables :: Multiple Conditions In Calculated Fields

Jan 9, 2013

is there any way to put into the calculated field (in expression builder) conditions? What I need is something like

Sum If (Table1.Field1="Y" And CurrentTable.Field2=Table1.Field3)

I means sum how many times there is "S" value in the field1 Table1, but only for records where the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).

View 13 Replies View Related

Tables :: Calculated Fields From Two Tables / Based On Relationship In Third Table

May 29, 2014

I have two tables of data, each relating to three business branches (branches A, B and C).

Table 1 shows the expenditure of each branch (by fuel, premises and wages).

Table 2 shows a number of units for each branch (mileage, floorspace and sales).

What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.

View 6 Replies View Related

Tables :: Calculated Fields In A Table

Sep 27, 2012

I have three columns where A is a number, B is a number and I want to make C = to the greater of the two. i.e.

Col A = 10, Col B = 12 therefore Column C = Col B or Col A = 12, Col B = 10 therefore Col C = Col A

View 4 Replies View Related

Multiple Fields Of Multiple Tables To One Table Query Or Report

Apr 12, 2013

I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?

View 1 Replies View Related

Tables :: Calculated Fields As Data Type In Tables - Calculating Total?

Apr 23, 2013

I am using calculated field as a data type in access 2010.

They are working fine.

However, I added a new field and now the final calc won't work.

I have Subtotal adding loads of fields together. Works fine.

Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.

The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?

View 2 Replies View Related

Modules & VBA :: Calculated Date Fields In Tables

Jul 29, 2014

I want to use an expression to compare 2 dates and calculate the date of the last set of accounts for a company. The user will enter(DD/MM) of the company year end e.g. 31/12. I then want to compare this with todays date (in another field) to ascertain if the month has already passed in the current year and hence calculate the last year end. i.e. If (current month>company month end), year end date is DD/MM/YYYY (where YYYY is taken from today's date), year end date is DD/MM/(YYYY-1). I am not a programmer and although I understand database theory (from the teaching perspective) I am not expert in developing databases.

View 1 Replies View Related

Tables :: Turning Off Rounding Function In Calculated Fields

Jan 22, 2013

Is there anyway to turn off the rounding function in calculated fields - I need to divide a currency amount by a whole number to calculate the number of full coin bags that will be needed (bags contain different totals according to the coin value) - a whole number.

Then the result is used to calculate how much loose coin will remain.

But regardless of the field type and/or the format, the number rounds when I reduce the decimal places to zero. I need the result to be the whole number and to calculate as the whole number.

View 4 Replies View Related

Tables :: Averaging Calculated Fields While One Field Is Blank

Feb 5, 2015

Access 2010 database.

I am trying to get an average from several numbered fields, which some may occasionally be blank.

=Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0)

I get an error message that says "The expression =Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0) cannot be used in a calculated column. This is a hypothetical expression being used.

The real expression is ...

([GC A1 - 2]+[GC B1 - 2]+[GC C1 - 2]+[GC D1 - 2]+[GC E1 - 2]+[GC A2 - 2]+[GC B2 - 2]+[GC C2 - 2]+[GC D2 - 2]+[GC E2 - 2])/10

This expression works great until a field is left blank, and then the average box is left blank.

The fields I am trying to add are Long Integer. Would that affect it?

View 1 Replies View Related

Tables :: Stock Update - Calculated Fields And Lookups

Aug 7, 2013

We have our access database with a bit of a messy structure?

We use our database to record sizes of our product. I am hoping to improve it by adding the items we have in stock to prevent manufacturing more.

Basically our main database called "Make & Model 1" has a list of various makes and model numbers, each model number lists various information needed to manufacture a replacement part.

Customers order the part and these part details are entered into a table called "Order Detail" What I would like to happen is that when the details are entered a calculated field adds the data entered to a text string. I need to match the text string to the same text string in a table called "stock" as there could be one part that matches hundreds of models.

Basically the "profile" "Colour" "height" and "width" make the string and this is what I need to match and tick a box / populate the number of items in stock. Eventually I would like this to reduce by the amount ordered but lets do one step at a time.

I am using Access 2010

View 1 Replies View Related

Reports :: Calculated Fields On Tables - Removing Zeros From Report

Apr 24, 2013

I have calculated fields on my tables and used zero values as default to show totals. But when I view my report, all the zeros appear. Don't want those zeros to appear on the report.

View 1 Replies View Related

General :: 2 Fields From Separate Tables Needed To Create Calculated Field?

Aug 19, 2013

The interface being used is a main form with various tabs and a subform on each of these tabs.

There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?

View 7 Replies View Related

Storing Calculated Fields In A Table

Apr 20, 2007

I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.

I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.

For example:

Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).

So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.

Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?

Thanx for any help!

View 2 Replies View Related

Saving Calculated Fields To A Table

Sep 14, 2006

I have a small Database that produces invoices, Each invoice could have 5 items on it which are compeleted at different Dates. (cutting hair in an OAP Home) Invoice to be produced at the end of each month which shows what happened each week. This will be generated for each person in the home. I can display all the information in a form when inputing the invoice information. I want a button to copy the invoice number the client name and the total of the invoice to a new table. The total of the invoice is calculated control on the form.

How do I do this??? can anyone help

I need a total in a table to substract payments from.


All Help will be much appreciated.

View 1 Replies View Related

Calculated Fields Not Writing To Table

Apr 26, 2007

Hi all,

I need to figure out how to have two calculated fields written to the database table instead of just being displayed in the form.

The user selects a number from 1 to 5 in the first field, and a number from 1 to 7 in the second field. The first calculating field multiplies these two numbers and displays the result. The second calculating field determines where the resulting number fits in three ranges of numbers and displays the result as Low, Medium, Or High.

My problem is the calculated results are be displayed in the form but not being written to the table. I cannot create a report to display all the High results, etc.

How do I update the calculation field formula to write the results into the table so I can create queries/reports on the calculated data?

Thanks,
bugleboy62

View 3 Replies View Related

Use Calculated Fields In Form To Update Table

Nov 29, 2005

I have a form created from a table and I had to use calculations in some of the fields to automate certain processes. Now, if I pull a report based on the fields in the table - I am getting all except for the fields with the calculations. Is there any way to update the main table - since most of the calculations were done with text boxes?

View 1 Replies View Related

Help With Linking Calculated Fields To Table Values

Aug 28, 2006

I need to display a value from a table in a text field on a form based on another text field on the same form.

The table has records as follows:

Month/Year xx Month No xx Year No xx Month ID (xx shows break in columns)
Jan-06 xx 1 xx 2006 xx 24
Feb-06 xx 2 xx 2006 xx 25
Mar-06 xx 3 xx 2006 xx 26
Apr-06 xx 4 xx 2006 xx 27
May-06 xx 5 xx 2006 xx 28
Jun-06 xx 6 xx 2006 xx 29
Jul-06 xx 7 xx 2006 xx 30

In one text field I use the DateSerial function to calculate the value of the previous month (e.g.Jul-06). I need to return the MonthID value to the second text field based on the value stored against it is the table, so in case of Jul-06 it would show 30 in text field 2.

View 1 Replies View Related

Calculated Fields In Form Don't Carry To Table

Aug 5, 2006

Is there a way to make some of the fields from my form that contain calculated values carry to the table? Right now the values calculate correctly in the form but when I look at the table the field is blank...

I was hoping to use one form to calculate a value, insert it in the table, and then use that value in another form.

View 6 Replies View Related

General :: Storing Calculated Fields In A Table

Jul 26, 2012

I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary, and I haven't really found a good answer for this after searching these and other forums.

Currently my database has one "Name" field (I didn't make it like this) which might look like this "Jonathan (John) Smith"
Now I also have reason to grab the name like this "Smith, John" for other purposes.

I've set up a form for inputting new people with separate fields for firstname, preferredname, and lastname. I have the calculated fields:

=[fname] & " (" & [pname] & ") " & [lname]
=[lname] & ", " & [pname]

(Later I will add in all the iif's for if there is no preferred name)

Upon finishing the form, the user hits Submit, and I would like to store the concatenated values into the table in their appropriate fields.

I can do this with a SQL INSERT INTO statement but I'd have to add all 25 or so fields from the form, and injecting user input directly into a SQL statement is not best practice either.

View 2 Replies View Related

Forms :: How To Store Calculated Fields Into A Table

Oct 16, 2014

How to store a calculated field into a table

I am preparing an invoice and I have a field called deposit .Now this field calculates %40 of the total of the order .but if the user wishes they can enter what they like .Now this field must be stored in a table for ever and a day and must not change or the invoice will be useless .I can see no other way of doing this and my research tells me (allen Brown) that this is sometimes necessary .

View 4 Replies View Related

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?

View 3 Replies View Related

Modules & VBA :: SQL - Select Multiple Fields From Multiple Unrelated Tables

Oct 28, 2013

A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is

Code:

dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "

Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )

View 7 Replies View Related

Heeelp! Very Confused - Calculated Fields In My Form Wont Go Into The Table

Mar 6, 2006

:rolleyes:
Please help! I am a newbie and VERY confused. I am not a programmer but am just learning so any help much appreciated! I'm not even sure if I've posted this to the right place?

I have so far created a table and also an asscoiated form - they both work! Changes in the form go into the table, no problem EXCEPT where I have a calculated field in the form - this info doesnt go into the table - how do I get this information to go back into the table?

I am trying to do this because I want to include the result of the calculated fields in a mail merge letter in Word (and Word wont pick up fields from my associated form - only from the table or from a query!)

Please rescue a damsel in definite distress (I've been knocking my head against a brick wall on this for 3 hours now!)

Many thanks in advance :D

View 5 Replies View Related

Tables :: Get Rid Of Calculated Field In A Table?

Sep 13, 2013

I've learned the hard way about using calculated fields in a table. I used on while building in Access 2010 for users that will be using Access 2007. Once my testers pointed out the error message, I did some research and realized my calculated field was the problem.

So, I created a query to replicate the calculation, deleted the offending field from the original table and redirected everything to point at the new query vice the original table.

However, my testers are still receiving the error about needing version 14 to access that table. There isn't a calculated field there any longer, but they still can't access that table. Did that calculated field put some sort of taint on my database that can't be removed? I'm not sure what to do. Do I need to start over, so that a calculated field was never part of the database to begin with? If so, how much of my existing one can I reuse?

View 6 Replies View Related

Tables :: Calculated Column In Same Table

May 19, 2014

I have a table with columns as

1- Name

2-Marks in Subject 1

3-Marks in Subject 2

4-Marks in Subject 3

I want to create more columns in same table as

5-Total Obtained Marks

6-Maximum Marks

7-%age of Obtained Marks

How can I do it in one table

View 3 Replies View Related

Tables :: Using NZ In A Table Calculated Field

Jul 22, 2013

Access 2013

I am trying to add some numbers together for a number of fields, of which at least one field will either be 0 or empty. I am trying to use the following formula in a calculated field in a table:

IIf(IsNull([Price Increase %]),[Total],([Total]*[Price Increase %])+Nz([LumpSumAnnualIncrease]))

Access returns an error statement: The expression XXX cannot be used in a calculated column.

Is there a different formula I should be using, or a work around, other than using the formula in a query.

View 5 Replies View Related







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