Sum Up The Values Calculated.
Jul 8, 2007
hi I am making a late report of each employee's data and I calculated for each individual day by having late for how long, but I would like to make another column which displays the total amount of time late. This is currently what I have now:
http://i8.photobucket.com/albums/a45/janue/num1.jpg
My SQl looks like this:
SELECT username, DateValue(clocktime) AS Date_Clocked, Min(TimeValue(clocktime)) AS Clock_In_Time, ((Clock_In_Time-#9:00:00 AM#)*24) AS Late_By_inHours
FROM tablename
GROUP BY username, DateValue(clocktime)
HAVING Min(TimeValue(clocktime))>#9:00:00#;
clocktime is a date/time field, in reality it is regardless whether it is clock in or clock out time. And now I want my table to look smth like this:
http://i8.photobucket.com/albums/a45/janue/num2.jpg
Has anyone knows how to do smth like this? I tried using the SUM() but it states error stating it cant take the expression ((Clock_In_Time-#9:00:00 AM#)*24) smth lidat
View Replies
ADVERTISEMENT
Oct 14, 2005
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?
View 2 Replies
View Related
Aug 24, 2006
I have created two queries which calculates a total. I want to add the values of the two queries in a third query to give me a grand total.
When I try to create this third query, it gives me an error saying that the fields cannot be used from two different queries. So I'm just wondering whether this is possible or is there any other way of doing this.
View 1 Replies
View Related
Mar 7, 2014
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.
View 3 Replies
View Related
Feb 10, 2014
I have a textbox in a report that I would like to calculate the sum of values that are less than 600.Then have a second textbox that counts the number of records that have values less than 600..This is based off of a query with calculated fields..I have been working with a formula that looks something like this
=Sum(IIf([BaleETime]="<600",0))
But all I get is a value of 0.BaleETime is the elapsed time between bales.
View 3 Replies
View Related
Feb 3, 2006
Hi,
I have a field in a form which displays the Sum of 10 values from other textboxes. I want to display the values of that calaculated field in a report and somehow I am stuck. I am running the report based on a query.
How would I get those field calculation values to display in a report?
Thanks
dfuas
View 2 Replies
View Related
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
Jan 8, 2014
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 Related
Jul 20, 2015
I am trying to get a value from a textbox into a table I created...
The textbox "calculates a number based on which radio button is pressed example if radio button 1 is pressed it returns 0 if radio button two is pressed it returns 8 and if radio button 3 is pressed it returns 15." I need to save the 0,8, or 15 depending on which radio button is pressed into the table...
I know you aren't supposed to store calculated values into a table but the report would never need to be changed and it just saves a new one each time with that being said anyway to make this work would be very useful!
Also how can you use the answer of calculated textboxs in other calculations?
View 14 Replies
View Related
Sep 17, 2013
I am trying to get calculated fields in my report to work but zero values in the data are throwing up #Div/0! and #Error! and #Num!
The main data fields I am trying to work with are:
VehicleReading
PreviousReading
VehicleLitres
My aim is to track mileage and consumption for a fleet of vehicles and show daily and weekly averages in mileage and consumption and then to flag 20% increase in consumption.
I have a calculated field in my report named Kilometers,
= [VehicleReading]-[PreviousReading]
And also a calculated field named Consumption,
=[Kilometers]/[VehicleLitres]
My sum and average calculation fields in the group footers are only working where there are values above zero in the Kilometer and VehicleLitres and Consumption fields.
In reality there are some days where there has been no travel, so some records will have a zero for the Kilometers field and there are also days when there has been no refueling so there is a zero for the VehicleLitres field.
I have tried using the expression builder to create an IIf function but to no avail. I have tried copying the syntax suggested by the Expression builder:
«Expr» IIf («expr». «truepart». «falsepart»)
However I keep getting error messages and despite troubleshooting and looking on the internet I can't seem to find a solution to this.
View 9 Replies
View Related
Mar 17, 2014
i have a subform which contains a lot of calculated values. fields which calculate values are located both in master form and subform. i use those calculated values in various reports where i have to insert formulas again. so i have decided to insert calculated values in table directly. i can use update query but i am unsure how to trigger it; and how to keep fields updated everytime a value is changed.
main form fields = [gsm] , [rate]
sub form fields = [pages], [qty], [gsm]*[rate]*[pages]*[qty]
View 1 Replies
View Related
Jun 12, 2013
I have a query that performs some calculations, these calculations feed into another query and produce a final value. If there are no null values everything works perfectly. But when there are null values, I get errors.
What I have tried:
1) I tried to implement the Nz function for each field of the formula that is not calculated but from user input.
2) I tried to implement the Nz function for just the field that calculated the last result before feeding into the next query, but it didn't show the 0 value I gave.
3) I tried to use UPDATE TABLE SET in SQL, but I don't think I was using it right. I tried to use it for all records but always got an error when it ran.
The first two above result in showing no values at all, even if there are some. The third didn't work. How to easily update null values in the query to 0? All I could think of was to somehow use Criteria or SQL.
View 14 Replies
View Related
Mar 24, 2014
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.)
View 6 Replies
View Related
Jul 31, 2014
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 Related
Jan 27, 2015
So I have a report with the following text box controls:
[Surname] & ", " & [Firstname]
=Sum([Quarter1_A]) - Named "Quarter_Total"
=Sum([Quarter1_T]) - Named "Quarter_Target"
=Val([Quarter_Total])/Val([Quarter_Target]) - Named "%Target" (Percent Format)
The report is grouped by the expression '[Surname] & ", " & [Firstname]'.I am trying to sort the records by the %Target text box. I tried entering the expression into the sort function but it still sorts by the grouped expression. I also tried sorting by the name of the text box but got the same results. How can I sort by the desired control?
View 14 Replies
View Related
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
Mar 21, 2013
I need to pull the calculated values from approximately 10 forms into another form. This is a summary form that should have all the totals pulled from the other forms.e.g. Form A has a textbox that reflects the sum of the amount. This is the total balance of form A.Form B, Form C, etc. all have a total Balance.Now, i need to pull all these totals into a summary form
-Form A Total Balance: x
-Form B Total balance: y, and so forth.
How and what is the best method to approach?I have tried using Forms![Formname]![Total] to get the data. This necessitates the need to hide all these forms and I ended up with blank forms, etc.Even so, the total sometimes appear and sometimes it does not. so it is very unstable.
View 11 Replies
View Related
Nov 13, 2013
I am trying to get calculated fields in my report to work but zero values in the data are throwing up #Div/0! and #Error! and #Num!
The main data fields I am trying to work with are:
VehicleReading
PreviousReading
VehicleLitres
My aim is to track mileage and consumption for a fleet of vehicles and show daily and weekly averages in mileage and consumption and then to flag 20% increase in consumption.
I have a calculated field in my report named Kilometers,
= [VehicleReading]-[PreviousReading]
and also a calculated field named Consumption,
=[Kilometers]/[VehicleLitres]
My sum and average calculation fields in the group footers are only working where there are values above zero in the Kilometer and VehicleLitres and Consumption fields.
In reality there are some days where there has been no travel, so some records will have a zero for the Kilometers field and there are also days when there has been no refueling so there is a zero for the VehicleLitres field.
I have tried using the expression builder to create an IIf function but to no avail.
I have tried copying the syntax suggested by the Expression builder:
«Expr» IIf («expr». «truepart». «falsepart»)
I know I need to get those zero's to fail in the calculation but as I said before all of the suggested expressions I have tried fail due to syntax!
View 11 Replies
View Related
Apr 12, 2006
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
:confused:
View 1 Replies
View Related
Aug 6, 2014
I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
View 9 Replies
View Related
Aug 29, 2014
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
View 5 Replies
View Related
Dec 5, 2014
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134
[code]....
A few sample outputs
one solution is taking rows 1-6 and 26.
OtherID
11098554
56200936
25138850
56148974
56530104
56148975
Another solution is taking rows 1-4 and 10-14.
OtherID
11098554
56200936
25138850
56024315
56115247
56148974
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
View 12 Replies
View Related
Sep 17, 2013
In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).
Problem: The default date shows up perfectly until a value is put in any of the other text boxes.
For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.
View 4 Replies
View Related
Sep 18, 2013
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
View 2 Replies
View Related
Nov 22, 2013
I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate()
If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
[code]...
View 14 Replies
View Related
Nov 3, 2005
I'd like to create a query which will consist of simple SELECT statements as follows:
SELECT [table1].[field1], [table2].[field1], [table2].[field2]
FROM table1 INNER JOIN table2 ON ([table1].[fieldX] = [table2].[fieldX]);
The challenge arises b/c instead of joining on equal values, such as the following:
[table1] INNER JOIN [table2] ON [table1].[field1] = [table2].[field1]
I would like to join based on equivalencies, such as:
[table1] INNER JOIN [table2] ON [table1].[field1] = 34 is equivalent to [table2].[field1] = 2;
I do not know the proper syntax, so this is where I need help. I tried to search online without any success.
I appreciate your help in advance.
View 1 Replies
View Related
