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.
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).
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.
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?
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?
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.
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.
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.
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 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.
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?
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?
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.
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?
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?
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.
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.
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:
(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.
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 .
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)?
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 )
: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!)
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?
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:
