Summing Records Where Field Is Between

Jul 31, 2006

I am trying to create a sum of a field called ec2mo where mopp is between 0 and 2 and then another sum when mopp is between 2 and 4, etc... I've tried a number of different things, but it doesn't seem to work - instead i get a single value for each field (i'm assuming because there are different values for mopp and many other fields)... I've attached the db with just that table.
Thanks very much for your help!
P.S. I'm not really at all good with using SQL so anything that can be done in a regular query would be most helpful!

View Replies


ADVERTISEMENT

Modules & VBA :: Summing Multiple Queries And Summing Time

Oct 4, 2013

I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.I've created 3 queries;

1 - Counts Calls
2 - Counts Emails
3 - Counts Meetings

These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.I have a second query as well (no pun intended).

I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).

View 1 Replies View Related

Summing Records Based On Criteria...

Mar 25, 2006

I am not sure if this possible, the methods I have looked up on the Internet take ages for the query to open but I'll give it a shot.

What I need to do is count (and increment) how many records are returned in a query for each person.

So if 1 person returns 4 unique records, in these records it shows 1,2,3,4 in each record.

The way i have tried is by using DLookup to check the next record and evaluate it using IIF but this takes about 25 seconds to return circa 50 records.

View 5 Replies View Related

Plz Help! Query For Summing Individual Records On Their Own

Jun 27, 2006

Hello Everyone,I am trying to perform a sum of records for the table belowSeatType| Ext rent | |Internal rent | Work Transfer 2 2VendorOther 2 3LOB BRF GTI BRF 2 5Displacement so that when I run the query I get the following followingSeatType| Ext rent| |Internal rent| | Total |Work Transfer 2 2 4VendorOther 2 3 5LOB BRF GTI BRF 2 5 7Displaceme What I mean is that from the first table how is it possible to get the results below. The query would generate a new column and place the sum of the data in the each row(record) in the same row in the total column. I would be really grateful. Have been bashing my head all day over this. PS I dont know any VBA. Is it possible without VBA?Thank you.

View 5 Replies View Related

Summing/Counting Records In Access

Dec 29, 2004

Hello & thank you in advance.
I am fairly new to using Access and would appreciate any help.

I have a file with 50,000 records
Example:
NAME CATEGORY
Karen Shoes
Karen Sports
Karen Collectibles
Karen Cars
Bob Music
Bob Sports
Bob Cars


The results that I am looking for are a sum of the total category purchases by each name.
EXAMPLE:
NAME CATEGORY
Karen 4
Bob 3

Is that something that I can do via Acces?

Thank you

View 4 Replies View Related

Queries :: Summing Records In A Query?

Jul 24, 2013

I have a query which displays sales and cash deposits for the day. I want a sum of all cash deposits.

So it looks like this..

Date________________Amount_______________SumOfAmou nt____________Exp1
17/7/2013___________$55___________________$55__________ __________$55
17/7/2013___________$22___________________$22__________ __________$22

I tried doing it by two methods.. neither of which are working.

In the Amount field, I put it as a Total: Sum. I also tried creating a new field which is an expression which sums up the Amount field. Neither are summing it and just displays the value.

View 1 Replies View Related

Grouping And Summing Mltpl Records To One Record

Jun 19, 2006

:confused: Hi everyone,

I have one table that I want to query. It's a basic table that contains:

MYKEY, AMOUNT
1234430, 100
1234435, 200
1234435, 100

1264435, 300
1264430, 200

1154435, 100
1154435, 219


1294430, 983
2010, 845
........ so on and so forth


Now, I simply want to GROUP MyKEY ENDING with 4430 and 4435 into one line and SUM the AMOUNT, thus showing like this:

MYKEY, AMOUNT
1234430and1234435, 100+200+100 --> (123 being the first common group)

1264430and1264435, 300+200

1154435and1154435, 100+219

etc etc.

i tried using simple sum and group by but does NOT work:

SELECT MYKEY, SUM(AMOUNT)
FROM MYTABLE
WHERE MYKEY LIKE ('*4430') OR ('*4435')
GROUP BY MYKEY

It does NOT ROLL-UP the 4430 and 4435 together.

QS: do i need to use VBA for this?

QS: do i need to have subqueries?

QS: please help?

Thanks!

View 4 Replies View Related

Summing Number Of Records In A Group On Report

Mar 28, 2013

I have a report I am grouping by a category, and I want to put the total number of records in that category in a textbox in the group footer.

How would I go about doing this? I have searched high and low with no avail...

View 2 Replies View Related

Summing One Field Value To Another

Aug 6, 2012

I have 2 field in a table - CourseMarks and ExamMarks. I am making a query to sum these two values. My code entered through the expression builder is Total: [CourseMarks]+[ExamMarks].

When the query is run there are no results if both fields have a score, but If there is a score in the CourseMarks field and none in the ExamMarks field the CourseMark is returned.All the field value data types are interger.

View 6 Replies View Related

Summing A Calculated Field

Jan 17, 2005

Hi,

I want to sum a calculated field on one of my forms. The field is calculated by multiplying the cost of a good by its quantity. So far I have been unable to sum it. Does anyone know how I can do this?

Here is a link to what the field is related to just so you people know what I am talking about: Project (http://www.access-programmers.co.uk/forums/showthread.php?t=78815&page=1) ...basically I have fixed all the problems mention in that thread. All that remains is this field. I will attach my project in case that helps. (the calculated field is in the Treatment Form) Thanks in advance :)

View 14 Replies View Related

Summing One Field For Default Value Of Another

Nov 23, 2006

I have two Numeric fields in my Table, MCRefill and MCTot.
Actually MCtot is the sum of all MCRefill for a given Cust_ID

I have the following expression in the Default value of MCtot in my form showing both the fields

DSum("[MCRef]","Miracle_Cloth_Main","[Cust_ID]='" & [Cust_ID] & "'")

Somehow it doesn't up in the record when you load the Last record for a Cust_ID

Can someone please help ?

View 2 Replies View Related

Queries :: Summing A Field In A Sub-database?

Mar 27, 2013

I have a table called Products which has a field called "Product ID" and a few others. I have another table called "Sales", which has a field called "Sales Date".

There will be different dates where the products are sold. Here's my problem: I'm trying to make a query to find out which products make the most sales. However, doing so will also display the products separately by their sales dates. So for example, it'll show...

Product #10 / Jan 1, 2013 / 100 Sales
Product #10 / Jan 14, 2013 / 21 Sales
Product #10 / Feb 11, 2013 / 44 Sales
Product #11 / Jan 5, 2013 / 201 Sales
Product #11 / Mar 13, 2013 / 444 Sales

I'd like the products to be together so product #10 would give 165 sales for example. In the Query, Product ID from both tables are connected.

View 3 Replies View Related

Reports :: Summing Calculated Field In A Report?

Jul 17, 2015

I have a report that has four fields: Item, Qty, Price and TotalPrice for each line in the detail section. Total Price is calculated by multiplying Qty x Price. The text box name that holds the Total Price for each line is txt_TotalPrice. I want to have a Grand Total in the report footer. I placed a text box in the footer with the following expression: =sum([txt_TotalPrice]). When I run the report Access prompts me for the parameter value of txt_TotalPrice. I've been trying to solve this for quite a while now - but I'm totally baffled.

View 3 Replies View Related

General :: Geometric Mean - Summing Entire Field

Sep 1, 2013

Here is a link that explains a little bit about it: [URL] .....

However, I'm having a problem with summing an entire field. Access does not have a DProduct() function, so it makes the math of "taking the Nth root of a product of 'X' numbers" kind of impossible without writing a custom function.

View 4 Replies View Related

Queries :: Summing Records From Two Queries?

Apr 17, 2014

I have two queries which pull fuel uplift figures by airport from a master table, each query for a separate airline.

Each record is made up of Airport, Fuel Uplift, Month and Airline. I want to create a query that will show each Airport per month with the total fuel from both airlines. To do this I've created a new field titled AirportMonth and then applied a JOIN command. This works to a point but initially only returns records where the same AirportMonth value appears in both tables. I can alter the join properties to show all records from one of the tables but I need all records from both tables. Below is the SQL I have so far.

SELECT JAFyr.Airport, JAFyr.Month, [JAFyr]![Fuel Uplift USG]+[TFLyr]![Fuel Uplift USG] AS Expr2
FROM JAFyr LEFT JOIN TFLyr ON JAFyr.AirportMonth = TFLyr.AirportMonth;

View 3 Replies View Related

Summing Queries.

Jul 14, 2005

i'm going to start this by telling what i am working toward in the end as it is where the problem obviously stems from.

i am trying to create a report which will tell me how many times 'hcapp' is in a table. there are three different fields where 'hcapp' can be placed...i can easily create three different queries which return how many times 'hcapp' is mentioned in one field, but i have yet to figure out how to look through all three fields in one query...is it possible? if not, how can i have a report call all three of these queries and then sum the total?

View 4 Replies View Related

Summing Queries.

Aug 3, 2005

i've a question that hopefully has a simple answer.

i have a query that returns three different numbers (each in its own column)...anyway, how can i make it sum the number of these three numbers...the best case scenario would be if i could have a fourth column appear in the results of the query with the sum...

it would look something like this

num1 num2 num3 sum
1 2 3 6

View 1 Replies View Related

Summing Query.

Aug 18, 2005

hello there...ive a question...i have a summing query which searches for the word "processed" in a specific column of a table...currently if "processed" is not found within the column the query will not return anything...is there a way to have the query return a zero instead?

View 3 Replies View Related

Query On Summing

Feb 15, 2007

When i'm summing up values in a column like "Hours:[production]+[nonproduction]" how can i have access treat blanks as zeros? I tried
"Hours: IIf([nonproduction]="",0,[nonproduction])+IIf([production]="",0,[production])" and it just gave me a #value error

View 2 Replies View Related

Summing Many Yes/no Fields

Jan 9, 2008

Hello, I am a newbie access user, so this might be a stupid question, but here it is:

I have a lot of yes/no fields labeled "10/1", "10/3", "10/5", ... (dates)
They are to record attendance on particular days.
I want to make a query that gives me the sum of a student's attendance and I have no idea to go about that. Any help is much appreciated.

Thanks!!

View 1 Replies View Related

Sum Function Not Summing?

Mar 17, 2005

Can't believe I'm posting this :o Have tried every permutation I can find, including the previous requestes from other members, but it still won't work.
I have a subform on whose footer I've placed a text box to sum a valu, [SubTotal]. On the main form I've set the control source of a text box to the text box on the subform, and it just won't work :mad: Can someone please have a look at this and put me out of my misery?

Thanks, Lol :D

View 13 Replies View Related

Summing #s In A Query

Sep 8, 2004

Hello,

I'm trying to sum numbers in a query. The table that Im summing from is set up as follows. The 1st column is just an autonumber. The 2nd column is an individuals. The third column is a date. The fourth column is a transaction (Buy or Sell). The fifth column is an amount (in $). and the 6th column is an Account (RRSP or RESP).

I would like to sum all the buys for each individual for each account. However I'm finding this difficult to do. Here is what i have so far. It sums all the 'buys' for the entire table.

SELECT Transactions.[Customer ID], DSum("Amount","Transactions","Transactions.Transaction=1") AS Expr1, Accounts.[Account Type ID]
FROM Transactions INNER JOIN Accounts ON Transactions.[Account ID] = Accounts.[Account ID];

Thanks very much,

C

View 7 Replies View Related

Summing Rows?

Nov 13, 2006

Hello All

I am making an attendance program for my college. When a user swipes their ID a record is set in an Access database. The column headings are the users name, classID and so forth followed by a column for each week of class. What I need to do is write a dynamic query that will sum the the values through the weeks for a particular student. Any suggestions?

View 2 Replies View Related

Summing Fields In A Record

Aug 23, 2005

Hi,

Not sure if this is possible (sounds simple hopefully) but I was wondering if there was a way to sum certain fields in a record on a continuous form.

E.g. Field1 and Field2 in record 1.

Is it something simple like summing columns, i.e. Sum(Field1)?

Any help would be much appreciated.

View 1 Replies View Related

Summing Up 3 Fields In A Record

Nov 12, 2007

Hi,
I am trying to figure out the best way to design a table. The table is going to contain data concerning checks that have been given to a church. Each check could be broken down into 3 different categories; Tithes, Offering, Other. In other words one check for $100 dollars might be Tithes $40.00, Offering $30.00, Other $30.00. I need to be able to enter these three amounts into the record and then have them totaled in another field.

Is it best to do this all in one record or should I have two tables? If I have two tables how do I get one form to enter the break down into one table and the total in another table. Fyi, if I do this in one record I don't want them to have to enter in the total. I want the total that is entered to be calculated by the three previous fields.

Thanks for any help you can be on this.

View 14 Replies View Related

Summing Totals In A Query

Sep 22, 2005

SELECT Commissions.TransactionID, Commissions.OriginatorID, Commissions.AmountPaid, Commissions.DatePaid
FROM Commissions
WHERE ((Commissions.DatePaid)> DateAdd("d", -32, Date()));

I want to add up commissions paid in a month using a simple query and im not sure how to proceed from here. This example will pull records for the last 32 days, but now how do I do the sum commissions.amountpaid to work?

Thanks for any help.

Scott

View 5 Replies View Related







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