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 RepliesI'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)
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 have been reading previous post s regarding the storing of calculated fields. Basically everybody says don't do it. However, if I don't I am not sure how to achieve what I need to achieve.
I am storing data on tool vibration levels. Data is input for X, Y and Z axes and a vector sum (total) is calculated on the fly using a query. So I only ever store X,Y and Z.
Ultimately I am looking for this database to be able to give me useful statistical outputs in particular a range of vector sums for a tool type. If I don't actually store the vector sum for each test then how I am ever going to report a range? The data would never change for each test as these become statictics, it's not the same as working out rates for example which may vary according to parameters.
What is the best way to do this?
I understand the conventional wisdom of not storing calculated values on the table, but I have a need to do do so. On my Input Screen I have a generated Certificate # derived from 5 single value fields keyed onto the screen. This certificate # is unique to those 5 single-value fields which should not be repeated. I want to store it on my Table as the Primary key with "no duplicates" so that if a keyer keys the same 5 single-digit values , thus creating the same Certificate #, he will get a MsgBox back telling him he cannot add a duplicate entry.
attached is my table and Input Screen.
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 RelatedWhen 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 a table that stores information for multiple behavioral surveys (numerical values). My goal is to add the proper fields that compose total scores value for each respective survey (do a summation of scores). Now, under design view for my table, I see that I can add a calculated field. When I create this calculated field, I can use the Expression Builder to do a sum of the proper fields (the fields that compose a total score for a survey). The only problem that I'm encountering is that if a field that is part of a survey is missing information, the summation disregards the rest of the values for that survey.
How can I account for these missing values so that, if 1 out of my 9 fields have information, I will still get a summation score for the 9 fields? I want to be able to do this without having to change the value of the missing field to 0.
In SPSS I can easily do this by computing a variable and using a code like this:
SUM.2(field1, field2, field3, etc.)
Access 2010. I inherited a database that I need to copy and get it ready for next year's data. There are several places in the various tables where the default value on a field is 2013. I need to change to 2014. Any vba code or macro that I can do this without opening all the tables to change the values?
View 10 Replies View RelatedI have a column that's called "Date" which stores values for begining to end date as 'Text' like: 070314-073114 So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114?
This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.
I have a database for scheduling students' for tests. They can take up to six tests in a day. There are about 80 different tests that they can take.
In my table, I created columns titled Test1, Test2, Test3, etc. They are lookup columns and I chose to enter my own values, putting in the tests titles for the values in each column.
When I add these drop-down lookup fields onto the form, it will only display 37 of these values. When I go back to the table and select "edit list items," it shows that it did cut the list off at 37, even though originally it allowed me to enter all 80-ish titles.
Anyway. It appears that there are limited values you can have in a lookup column, though after doing a lot of searching online I can't find anything to indicate that is true.
It seems to me that it would be smarter to set this up with two different tables, storing the reg info in one table and the test titles in another table. However, I am having a hard time figuring out the relationship aspect of this solution and how to make it pull up the correct values for queries/reports as well.
I am using a form based on a table in MS Access 2010 for data entry.
The Data Type for column ID is AutoNumber with Increment=1. When I found ID for recent entries gets an anomaly (e.g. should be ... 3, 4, 5 ..., but it shows ... 3, 5, 6 ... that misses 4), I tried to refresh/change the ID column with the following steps:
1). Change the Data Type for column ID from AutoNumber to Number
2). Add a new column IDD and set its Data Type as AutoNumber
3). Delete the original column ID
4). Change column name IDD into column name ID
However, I got a problem with step 2): here the order # for the records becomes messed up, e.g.
Should be:
ID IDD
1 1
2 2
3 3
5 4
6 5
7 6
... ...
But, I got:
ID IDD
1 3
2 4
3 5
5 1
6 2
7 6
... ...
Why? Is this caused by the relationships between this table and other tables?
How to control the below message to something like this through VBA:
"The Job: " & JobNumber & " already exists in the table." or to open an alternate form where they can make the change. Allowing duplicates is not appropriate for what I am trying to accomplish. I just want a friendly way of rerouting a user to understandable answer.
instead of this:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
how data is best structured in Access.I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).How is this sort of data best structured and accessed in Access?
View 12 Replies View RelatedI 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 have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
I have a Access 2003 file and I want to filter anywhere where there last name is "expired" and change the column first name to say "no". How do I do that?
View 1 Replies View RelatedI have two email addresses in a table one personal and one work and a question asking which to use, I have a query running from the table and have an IIF expression creating a field which looks to see which one to use (that all works fine !) anyway my client has now asked for an extra field in the query which combines the two email addresses, as in aaa@homedomain.com;bbb@workdomain.com so that I can run an email to both addresses at the same time. fraid I just can't figure it out ! I'm sure it's obvious, I can't find a similar question posted either, I've tried the expression builder using AND for the two email addressses but just end up getting -1 in field when I run the query, not the two email addresses separated by a ;
Anyone any ideas
many tks
:)
I am very inexperinced with Access, please be kind.
My problem is that from the form view the simple division of 2 #'s are placed in the correct text box I selected, but I am unable to get the value to enter into the data table. Please help.
Thank you
Hi all,
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
How do I do this?
Kind Regards,
Matthew
When i use MS Access help to search for my query i get this result:"Note: If you add a calculated field that includes one or more aggregate functions to a query in qhich you're calculating totals on all the records, you muct set the calculated filed's TOTAL cell to EXPRESSIONHow do i do this?????
View 1 Replies View RelatedI having a problem with the expression builder in a table.I got a lot of fields with dates and I what a calculated field so I can see if any of the dates are newer than 7 days.I have been trying this formula:
IIf([Field1]or[Field2]or[Field3]<"Date() -7";true;false)
But it don't seen to work.
In my table for duplicate "line no" I have different "contractor" like below.
LINE NO CONTRACTOR
L-0001 C-1000
L-0001 C-2000
L-0003 C-6000
L-0003 C-8000
L-0003 C-9000
L-0004 C-5000
Now I would like to make a query for transposing values like below:
LINE NO CONTRACTOR1 CONTRACTOR2 CONTRACTOR3
L-0001 C-1000 C-2000
L-0003 C-6000 C-8000 C-9000
L-0004 C-5000
how I have to make this query?
I have this table ("people") and an example of possible rows:
id(key) COL 1 COL2 department country name
1 xx yy KPP USA John
2 zz kk KPP USA John
3 ss ff TLL USA John
4 ww qq PPO Italy Marco
5 jj uu PPO Italy Marco
I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).
I have tryed in this way:
SELECT
COUNT(DISTINCT department) AS NumberOfDifferentDepartments
FROM people
GROUP BY name;
But Access says me there is a syntax error.
I'm working with MS Access 2002.
I am trying to run a change an existing query in real time to allow date filtering for 4 different categories. I can get two of them to work. Calibration Date and Icepoint Date. But for The two calculated fields Calibration due date and Icepoint date I cannot get it to filter properly e.g. for 2013 dates it also includes 2014 dates and just does not work properly. I am thinking its due to it being a calculated field but don't have a clue how to fix it. see pasted code for calibration due date filter where Todate and Fromdate are the 2 dates used. I also have the on current code and the exit code to reset the query to its original status.
Public Sub SetDate1()
'Apply date filter and rebuild query in real time
On Error GoTo Err_SetDate1
If IsNull(Me!ToDate) And IsNull(Me!FromDate) Then
MsgBox ("Please Enter Date First"), vbExclamation
GoTo Exit_SetDate1
[code]....
I have a calculated field in a report, which is as follows:
=IIf(IsNull([POI_E1A]),(([TodaysDate]-[EADDate])/365)/3,[A1]+[T2]+[A3]+[T4]+[A5]+[T6]+[A7]+[A8])
The calculation works fine; however, I only want to show the whole number (not rounded up; not rounded down). In other words, if the result is 0.9967, then I want 0 to appear.
I cannot figure out how to make this happen. In the properties section I ensured the format was "general number" with 0 decimal places but this didn't change the result. I tried changing the format in the calculated field, again, I wasn't successful.