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;

View Replies


ADVERTISEMENT

Queries :: Calculation That Counts Where Values Equals 4 In Fields

Nov 15, 2014

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.

View 7 Replies View Related

Queries :: Calculation Based On Values Selected In Table By Looking Up Corresponding Values In Other Tables

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

Queries :: Calculation Only Works If Field Have Values

Jun 23, 2015

I have a calculated field in a query. The calculation only works if the fields have values. This is what I used a the calculation:

PrimaryShareTotal: [Primary1Share]+[Primary2Share]+[Primary3Share]+[Primary4Share]

I tried using the Nz command

PrimaryShareTotal: Nz([Primary1Share]+[Primary2Share]+[Primary3Share]+[Primary4Share])

but this doesn't work.

View 1 Replies View Related

Queries :: Create A Query That Can Sum Values Of Different Fields In Different Tables

Apr 11, 2013

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?

View 3 Replies View Related

Queries :: Time Calculation Query

Aug 6, 2014

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?

View 5 Replies View Related

Queries :: Percentage Calculation In A Query?

Aug 5, 2014

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?

View 3 Replies View Related

Queries :: Finding Top Three Values In Six Fields?

Oct 3, 2013

I'm doing a medical db, where I'm trying to calculate ISS

Code:
en.wikipedia.org/wiki/Injury_Severity_Score

There are 6 values, and i have to sum the largest three (squares to be precise). How can I get three highest values from 6 fields in the same record?

View 1 Replies View Related

Queries :: Cancel Age Calculation Query For One Record

Mar 26, 2013

I have the following age calculation query:

Age: (Now()-[DOB])365

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?

View 8 Replies View Related

Queries :: Using Parameter In Query With Calculation From Date

Apr 13, 2015

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.

View 5 Replies View Related

Queries :: Combining Fields - Spacing Between Values

Mar 6, 2014

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.

Code:

IIf(IsNull([zam_adm]),"","ADM") & [zam_adm] & " " &
IIf(IsNull([zam_cts]),"","CTS") & [zam_cts] & " " &
IIf(IsNull([zam_crs]),"","CRS") & [zam_crs]& " " &
IIf(IsNull([zam_IPB]),"","IPB") & [zam_IPB]& " " &
IIf(IsNull([zam_opr]),"","OPR") & [zam_opr]& " " &
IIf(IsNull([zam_owl]),"","OWL") & [zam_owl]& " " &
IIf(IsNull([zam_pmi]),"","PMI") & [zam_pmi]& " " &
IIf(IsNull([zam_wl]),"","WL") & [zam_wl]& " " &
IIf(IsNull([zam_sgt]),"","SGT") & [zam_sgt]

The output, unfortunately, is putting spaces between empty fields and I'm not sure how to prevent this in the syntax?

View 3 Replies View Related

Queries :: Adding Totals For Fields With Same Values

Aug 13, 2015

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.

View 2 Replies View Related

Queries :: Query Records With Both Duplicate Values And Different Values?

Jun 18, 2013

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:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable

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').

View 5 Replies View Related

Queries :: Get A Query To Perform A Calculation And Round The Results?

Feb 20, 2015

I'm trying to get a query to perform a calculation and round the results.

The fields that I am running the calculation and am trying to round are Data Type Number, properties Field size Single and decimal places Auto.

I have tried the built in function described in Allen Browne's site to no avail.

[URL]

I have tried rounding the individual fields and then adding result.

I have also tried rounding the result (as attached).

View 11 Replies View Related

Queries :: Filter Query With DateSerial Calculation Field?

Nov 21, 2014

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.

View 1 Replies View Related

Queries :: Create A Query To Make Calculation From Form

May 16, 2013

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.

View 3 Replies View Related

Queries :: Creating Particular Calculation In Access 2010 Query

Dec 9, 2013

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.

View 2 Replies View Related

Queries :: Query For A Specific Date Range / Calculation

Jan 22, 2014

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.

View 14 Replies View Related

Queries :: Query Calculation Based On Current Time

Apr 24, 2013

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.

View 3 Replies View Related

Queries :: Create Calculation Query That Uses Different Equations Under Certain Conditions

May 12, 2013

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:

Total_Cost: IIf([Cost_Category]=Full Price,Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])),
IIf(Cost_Category]=BOCES,Sum(nz([BOCES_Number_of_Participants])*nz([Cost_Per_Person]))

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.

View 2 Replies View Related

Queries :: Criteria To Display Records With Values >0 In Different Fields

Sep 24, 2014

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?

View 1 Replies View Related

Queries :: Give All Null Values 0 In Calculated Fields?

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

Queries :: Pull Data From Fields That Have Values With Decimal Places

May 9, 2013

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.

View 2 Replies View Related

Queries :: Script To Do Calculation For Every Record And Place Result As Field In Query

Dec 21, 2013

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.

View 2 Replies View Related

Queries :: Convert Boolean Fields To Integers / Blank For Null Values

May 22, 2015

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.

View 14 Replies View Related

Calculation Depends On Different Values..

Mar 13, 2007

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.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved