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
I've now since moved everything into one table but i need formatting of my calculated field (I'm new to all this).
Below are the 3 expressions working independently of one another, I just need them combined in to one if possible. As I am getting an "invalid syntax" error or "too many arguments" when I try to get it working
PHP Code:
0.5*IIf([Type]="Offlist" And [APP_LINE_OFFA]>3,1,0)+0.2*IIf([Type]="Offlist" And [APP_LINE_OFFB]>2,1,0)+
IIf([Type]="Offlist",Abs([EP_DETAILS_OFF]*0.15+[REF_DETAILS_OFF]*0.15),0)
PHP Code:
IIf([Type]="Onlist",Abs([EP_DETAILS_ON]*0.15+[REF_DETAILS_ON]*0.2+[GEN_NOTE_ON]*0.2+[APP_LINE_ON]*0.25+[SPEC_INS_ON]*0.2),0)
PHP Code:
IIf([Type]="Aged Report", Abs([EP_DETAILS_AGED]*0.2+[REF_DETAILS_AGED]*0.2+[GEN_NOTES_AGED]*0.3+[SPEC_INS_AGED]*0.3),0)
Currently I have a calculated field in my table; however, under certain circumstances I need to change the value to another value which is not related to the calculation. I understand that the calculated fields are read only (why is that?), so I was wondering if there was any way to change values within a calculated column without actually changing the expression itself.
View 1 Replies View RelatedSo I have this If statement that goes something like this;
Code:
IIf {ttblEQAnswer_FCLIT.ActionType} = 'Answer Affir Defense' andnot isnull({ttblEQAnswer_FCLIT.METPrep}) and not isnull({ttblEQAnswer_FCLIT.AttyInstr}) and
({ttblEQAnswer_FCLIT.METPrep})> date(1900,1,1) and not isnull({ttblEQAnswer_FCLIT.AttyInstr})
and {ttblEQAnswer_FCLIT.METPrep} < currentdate - 10
then 'Y' as [MET InstructNeed?],
I want to use the above formula to calculate a column. However, do I need to join the tables referenced above in order to get this formula to work? As it stands I'm getting an error that says "Malformed GUID in query expression".
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 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 RelatedI'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 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
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.
Hi,
I have two tables:
Table A provides total sales volume of the UK shoe market from 2000-2007.
Table B provides the sales volume of different shoe manufacturers from 2000-07.
I want a third table created, called Table C. This should look exactly like Table B but instead of sales volume it shows percentage sales that are calculated by using the figures in Table A and B (i.e. [sales volume from Table B/total sales volume from Table A] * 100).
Could someone point me in the right direction please (assuming that such a table can be created, based on a calculation of figures in other existing tables).
Thank you.
I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?
View 1 Replies View RelatedI 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?
Access 2010.
Have a table with columns of data which I want to total at the end of each row. The Calculated Field does this simply enough but it only seems to be able to add whole integers? How do I get it to accept and add values to 2 decimal places?
i want to insert a calculated value of a e.g field6 (field4 * field5) in forms into the database table. by using expression builders in my forms. i have made that feild and on click it shows the calculated value but on filling in the rest of the form and clicking on Insert button does not update this calculated field but iserts all other fields.
View 3 Replies View RelatedI am trying to create a calculated field in my table, I am trying to use the following code:
DatePart("ww",[Gas]![Date Opened])
I receive the following error message: "The expression DatePart("ww",[Gas]![Date Opened]) cannot be used in a calculated column."
Access 2007 - I really need to be able to display the week number in the table, based on the Date Opened field.
I have 2 tables:
Trainers
-> Name
-> Date of Birth
-> Address
etc etc.
Courses
-> Name of Course
-> Course approval expiry date
-> Type
etc. etc.
I now need to make a table whereby I can have the list of trainers and tick the courses that they are allowed to teach in each column.
I can forsee the columns are being:
-> Trainer
-> Course 1, course 2, course 3, course 4 etc etc.
The problem is that courses can be added/changed/deleted.
One of the Tables in my membership database is named [WaitList] and has a linked Form.
The Form is configured to pull data from another Table named [Groups]. The fields are [Group ID], [Group name] and [Leader].plus others from a second Table.
When the [Group name] is selected from a Form Combo Box it succesfully records the [Group ID] in the Form and Table, but repeats the [Group ID] in the [Group Name] column of the Table. I am convinced that the problem lies with the Column Width setting on the Property Sheet which is currently set at 0;2.54, but trying various combinations has not solved the problem.
The Row Source for the [Group Name] field is
; SELECT [Groups].[Group ID], [Groups].[Group Name], [Groups].[Leader] from [Groups] and the Column Count is set at 1 (2nd in Table)
I believe the [Leader] field is a different problem in that the Form correctly finds the ID and the Name but not the Leader. I have checked the property sheet for anomalies, but without success.
I have built this type of Table/Form relationship many times but have failed on this occasion.
Query:
I have two tables : Table1,Table2.
Table1 has columns : A,B
Table2 has columns: A,C
What I want to do is to copy all data of "C" from Table2 to Table1 so that Table1 has columns: A,B,C..(Column "A" is common in both Tables).
i am making a database from which i can calculate fuel required from one point to other in any particular type of vehicle.the problem is fuel from Pt A to Pt B is not same as from Pt B to Pt A.A sample table is attached as zip. qp.bmpis there any way i can cross refer field values with that of rows .
View 9 Replies View RelatedMy employer wants me to update and revise a form with almost a hundred controls. All of the information is stored in a single table.
Is there a way that I can (easily) sort the order of columns in the table to alphabetize them? I know how to sort and filter records, is there a way to sort the columns other than the manual click and drag solution?
Note: I know that the database I'm working on for this project is not normalized. My employer isn't concerned with normalization. I've made as many corrections to the table as possible, already, but some things just won't be fixed.
When I originally created my access database, I used a text field/column to connect 2 tables together. I would like to change this to reference the Primary Key/ID column instead of the text field.
Is there an easy way to go about doing this? My original thought was to create some vba code to replace the data in the column of the sub table with the autonumber in the primary table but I cannot figure out how to do it.
I have 4 tables that store information on guest charges (stayinfo/room rate, fuel charges, bar tab, merch charges) each linked to table carrying guest ID's. Is there a way to combine/merge all the tables into one in such a way that all of the guestID's are in one column, all of the charges are in another column etc? Basically adding one table to the bottom of another.
View 1 Replies View RelatedI have a replica at work, 1 copy on the network, 1 local.. I need to add a column to the main table, getting "Operation is not supported for this type of object".... I have users that use the database but was given a change to the form/report which requires a column addition. What is the simplest way to get around this??? I'm trying not to get into major problems.
View 1 Replies View Relatedhow I can set a color on table column formatted as rich text and memo. For instance this table has 4 different columns of remarks, one from each engineering operating center. Remarks_1, Remarks_2, Remarks_3 and Remarks_4. Each one of the 4 operating centers enters the remarks in one of these fields. On the Dashboard report, what I do is I get all these 4 fields and merge in 1 because the report is already narrow due to several other columns as well. So there is a report combining all data : =Remarks_1& " -"&Remarks_2&" - "&Remarks_3&" - "&Remarks_4 under Remarks.
The point is that the Dashboard has colorcode, Orange for Remarks_1, Red for Remarks_2, Blue for Remarks_3 and Green for Remarks_4. All these fields are memo on rich text format. On the form I have set the color of each field, however the color doesn't get set on the table as well, but only on the form fields. How can I set a defined color to each field on the table? And also when I get all data in one column, I would see all different colors.
My problem is I have two tables. One of the tables was made specifically to draw information from to populate the second table. The second table has a combo box that draws information from one of the fields from the first table. What I am hoping to do though is have each selection from the combo box in the second table to draw different information from another field in the first table and use that data to populate another field in the second table.
Simplified: Combo box in Field (1) on table two has multiple selections that I want to autofill the data in Field (2) on table two based upon the selection of the combo box. Data from both fields in table two would come from two different fields from table one.
I have tried to work with the After Update button in the After Events section on the ribbon at the top; however, I am simply lousy with coding. Is there an easier way to do it other than using that option?
I have two tables,
Table A:
Year Type Count
1992 Walk 82
1992 Bike 43
1992 Vechicle 298
1992 Ship 3
1992 flight 3478
1993 Walk 47
1993 Bike 13
1993 Vechicle 638
1993 Ship 24
1993 flight 3111
1994 Walk 0
1994 Bike 8
1994 Vechicle 1298
1994 Ship 24
1994 flight 3234
Table B:
ID Type
T1 Walk
T2 Bike
T3 Vechicle
T4 Ship
T5 flight
The two table linked by the [type]
I need a query expression like: SlowWay: IIf([TableB].[ID]= 'T1' AND [TableB].[ID]= 'T2',[TableA].[Count])
FastWay: IIf([TableB].[ID]= 'T3' AND [TableB].[ID]= 'T4' AND [TableB].[ID]= 'T5',[TableA].[Count])
My expression keep gives me the error.