Queries :: Calculation Query To Add Values Of Three Fields
May 8, 2013
I created a simple calculation query to add the values of three fields:
Program_Cost, Auditorium_Cost and Millage_Fee.
I followed the steps found here: [URL] ....
But it doesn't work. The query pulls the values for the relevant fields but doesn't actually calculate the total. What am I doing wrong? Here's the query's SQL:
SELECT [Event Information].Event_ID, Sum([Program_Cost]+[Millage_Fee]+[Auditorium_Cost]) AS Total_Cost, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee
FROM [Event Information]
GROUP BY [Event Information].Event_ID, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee;
I have six fields in a record that look like Median.University1, Median.University2, Median.University3, Median. University4, Median.University5,Median.Unive rsity6. How can I create a calculated field that counts where values equals 4 in the fields . I have tried Dcount and Count with no success.
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).
I'm trying to create a query that can sum values of different fields in different tables...Can I sum values of a field and put the result into another field in different table?
I am needing a query to calculate elapsed time in business hours for each record selected (I normally base this on a date range). For the purposes of this query, business hours are defined as Mon-Fri from 7 AM until 9 PM.
So for example:
With a start time of 6:45 AM and an end time of 9 AM, the query would need to return 02:00 (in [h]:mm format).Likewise, with a start time of 7 AM and end time of 9 AM the query would also return 02:00.Is there any way to do this easily? Or at all for that matter? Is it possible to deal with weekends?
I am trying to set up a calculation between two values to show the percentage difference. In Excel, for example, I would have two values, £905,175 and £891,563, and I would enter =A1-G2)/ABS(A1), which would then return a plus or minus percentage value. how to do this in a query using Access 2010?
It works a treat! However, I do not want this to continue to calculate if the record has them as deceased - I want it to stop at their date of death.
I have a tick box that when selected indicates that this record has died, and a field where you can enter date of death.
Is there some way that via clicking this button, or by entering a date of death, I can stop the Age Query from calculating for just that relevant record, not all of them? If so, where to place the necessary VBA, etc?
I have a database with a Date of Birth field. I have a query with a field that calculates the age from the Date of Birth (DateDiff("yyyy",[Date of Birth],Date())+Int(Format(Date(),"mmdd")
As a criteria in this field I want to be able to select a minimum age, so >=[please input minimum age]
However the results are bizarre - sometimes it gives the right answer, and sometimes not. It seems to have a particular problem with ages above 10, which show up all the time.
I'm creating a query which pulls together the numerical values of 32 separate fields. Each field has the potential to have a value in it ranging between 1 and 9, but most fields will be blank.
I want to prefix the value (if the value is not null) with a 2 or 3 character-long code relating to the field name.I then want to combine them all together in a query so I can easily copy and paste the output to a spreadsheet.Here is what I have so far, with just 9 of the field names, and it's already looking a bit clunky.
I've got a table with data that's been imported from Excel. I need to run a query that pulls the same values of field one and adds the values of field 2. For instance:
Field 1; Field 2 Jones; 200 Smith;150 Jones; 300 Smith;100
In this example, I need the sum of field 2 for the records where Jones is the value in field 1.
So the result in this case is that records where Field 1 = Jones, Field 2 = 500.
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass ------- -------- --------- A123 Apple Fruit A123 Apple Fruit A123 Grape Fruit A456 Potato Vegetable A456 Potato Perishable A789 Carrot Vegetable A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
I have a query with a DateSerial Calculation field that I would like to filter the query by. The DateSerial calculates the same day of every year (5/31/"YYYY"). When I try to add a criteria sort to this field, I get a data mismatch error. Here is the code: ThirdMay: DateSerial(Year(DateAdd("yyyy",3,[LastDayYear])),5,31).
How do I get only dates due in 2015 to show? I have tried all the standard date criteria to no avail.
I need to create a form that using combo boxes selects a product-size-quantity, and then calculates total price.I asume that I create a query to make the calculation from the form, but for the life of me, I can't fathom out how to do it.
I have played with this problem for 3 days and have come close but not quite solved it. My problem, I have several drivers delivering several orders, the orders are named 101, 102 and so on lets say to 150. Due to locations of the drivers, some deliver more orders then others. I want to be able to create a report that looks like
"Driver #1 101 - 106" "Driver #2 107 - 110"
Driver 1 delivered 6 orders. Driver #2 delivered 4 orders and so on.
I have tried the 'count" which gives me the number of orders per driver but having trouble figure out the design of the calculation in the query.
I am trying to calculate the total hobbs time (Ending Hobbs - Starting Hobbs = Total Hobbs) based on a user inputed date range. The query that I created (see attachment) doesn't seem to give me what I'm wanting.
I have a database that has 2 forms. After submitting the first form, the user should complete the second form within 24 hours. The first form stores the Date/Time the form was submitted. I want to be able to run a query and have a column in the query that is "Time Remaining". In non-technical terms, this column would be: Date/Time form submitted + 24 hours - Current date/time.
I want to create a calculation query that uses different equations under certain conditions. Here's specifically what I need:
If the "Cost_Category" field is "Full Price" then the query uses the following calculation: Total_Cost: Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost]))
If the "Cost_Category" field is "BOCES" then the query uses the following calculation: Total_Cost: Sum(nz([BOCES_Number_of_Participants])*nz([Cost_Per_Person]))
I have successfully created these two queries individually, but combining them doesn't seem to work. Here's what I wrote:
It keeps coming up with errors, saying that I misplaced a comma, parenthesis or quotation. I've tried playing with it, changing the syntax slightly but it doesn't seem to work.
i made a query to display students results after an exam. there are 12 subjects but a student chooses 8 from that. After an exam, the results are keyed in and a result slip is printed later. The problem is that the result slip displays all subjects including those not done by the student. I typed a criteria >0 in one of the fields and no records appeared. Is there a way that i can use to display only the subjects chosen and marks attained by a student and leave out the rest?
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.
I have a query that pull data from hours worked fields that have values with decimal places. Hours is multiplied to rates to create my amount to charge in my query which is then displayed on my invoice report.
The issue is that when I sum my amount, my subtotal is off by $.01 due to the decimals entered for the hours worked (e.g. hours worked is 1.5*32.75=49.125)
I need the rounding to work in the query and display on my report as $49.13 and the subtotal to reflect it. I have the properties in my table as double with decimal place at 2, and my query and report properties with decimal place at 2 as well. It still isn't working.
1. I created a table that contains information about people and their details (mainly numerical info). 2. I created a form containing a command button and a label. 3. I have written a VBA script under the button so that when the button is pressed, the result of the calculation appears as the caption on the label.
My problem is...How do I get the script to run so it does the calculation for every record and places the result as a field in a query.
I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)
So something like this :
Code: SELECT [tblTable2].[fldBooleanField].... FROM [tblTable1] LEFT JOIN [tblTable2] ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.
So I tried this :
Code: SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField.... FROM [tblTable1] LEFT JOIN [tblTable2] ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)
How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?
Only thing I can think of is to use (untested) :
Code: IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))
But there's a number of boolean fields in there, all requiring the same treatment.
Hi, Best to have a look at attached relations picture. Basicly this is what I'm wondering about: I add a new customer who bought a product from a specific supplier. That unique combination gives me the right on commission. From company A I get, let's say 5% of the capital. Company B gives me 4,25%
Products are insurance policies, so at certain products you are insured for a capital i.e €100.000,- Percentages given are to be calculated of that capital.
So I have a lot of possibilities here which determines my provision. What is the best way to handle this ???? I have no clue where to start.
The field [Polissen.Provisie] is now manually filed in on the Form where I add all details, which should remain possible (sometimes there are different agreements concerning the fee) Hope it's a bit clear.. Please ask if more info is wanted.
