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 Replies


ADVERTISEMENT

Summing Different Multiple Amounts With Grouping

Jul 13, 2006

hey all, thanks to anyone that can help

i have 5 fields facid custid empid amount invdate.

i need to be able to get totals on the amount based on fac, cust & emp based on a date range given by user, but here's the kicker.. i need to get the current month totals, month totals - 1year (so same month but for last year)
the date range totals, last yeardate range
current ytd, last ytd

i did it using code, but the performance isn't that great. there are 2800 customers it has to cycle through and figured one of the resident experts can help with a query.
i've used dsum() but not quite getting the results i should.
i think one of the problems is the user has the option of filtering the data based on the 3 fields.

anyways if you can help i'd greatly appreciate it..

View 2 Replies View Related

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 3 Replies View Related

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 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 Other Fields Per Record

Jun 28, 2006

Is there a way to use a query to sum totals of several fields per record? In the db I have designed, I have 5 seperate fields for estimates from different departments. There is also a field for Total estimates. What I would like to do is have the Total Estimates field autocalculate the total sum of the other 5 estimate fields. Is this possible to do through a query? If so how? If not - is it possible at all?

Thanks!

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

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

Grouping With Particular Records At The Top

Feb 24, 2006

Hi all,
I have a db for logging meeting action points. Each action point has a description and owner. I'd like a query/report which will produce a list of action points grouped by owner (easy), but with a particular owner's action points at the top.

Essentially, rather than do a GroupBy and ascending sort, i need to specify the first group that is displayed. I don't care in what order the other groups appear.

Eg.

Action Point Owner
1. Task 1 DC
6. Task 6 DC
8. Task 8 DC
3. Task 3 AG
4. Task 4 AG

So, above if i just did an ascending sort, the DC records wouldn't be at the top.

any ideas?

El.

View 3 Replies View Related

Grouping Records Using A Query

Aug 2, 2005

How can I group records based on values within a particular field? For example, if I have records of people with different annual incomes, what is the easiest way to allocate the records to income bands? I assume the starting point will be to create a band classification in Excel and import as a table. How do I subsequently run a query to achieve the desired effect?

View 1 Replies View Related

Reports :: Grouping With Only One Record

Jan 26, 2014

I have a report showing products and their selling prices

Most products have only one selling price, however a few have more than one selling price

To cater for this I have created a group called "Products"

The selling prices are then in the reports' "details" section.

Works well, except that every product now spans 2 lines. One for the product description and another for the price. This doubles the number of pages in the report.

As products with only one price is the norm, is there a method that I can adopt to show products:

* with one selling price - product description and selling price on one line
* with more than one selling price - product description and first selling price on one line, with subsequent selling prices on line below?

View 1 Replies View Related

Grouping Records By Month Query

Oct 27, 2005

This is probably an easy question for you all:

I want to run a query that does a GroupBy with DATE and Counts the number of records associated with that DATE.

I can do this, but I also want the dates to group together by Month so I can count the number of records per month for multiple months in the same query. How do I do this?

Thanks very much

View 1 Replies View Related

Forms :: Grouping Individual Records As A Set?

Apr 16, 2013

I wish to make an Access DB that will work as a HUB for a few different people in our company to work off of as a means of putting analytical results in tables where a SampleID is given to each sample and is unique. I want to know if there is a way to group any given number of SampleID records together into a 'ProjectGrp' and produce something along the lines of a work order form so the other people in the company can look at a ProjectGrp, see what SampleIDs are part of that group, and then fill in the data for each SampleID.

View 3 Replies View Related

General :: Grouping Records - Query To Gather Information From Tables

Mar 7, 2013

I have a query that gathers information from the tables in my database and returns something like this:

Code:
Field Pest
1 Insect1
1 Insect2
1 Insect3
2 Insect1
2 Insect3
2 Insect4

Field and Pest are fields in two separate (but joined) tables. Field exists only once in its table, and multiple pests are attributed to each field. I'm looking to create a table or query in which the values would be listed as follows:

Code:
Field Pest
1 Insect1, Insect2, Insect3
2 Insect1, Insect3, Insect4

The purpose for this is to create a legend for a map used by another program based on the information gathered in the database. I do have other criteria for which fields and pests show up in this query.

View 11 Replies View Related

Queries :: How To Create Auto-grouping Based On Change In Previous Record

Jun 29, 2015

I have a column "CAT" each time that CAT switches between 0 and 1, I would like my query to auto-create a "grouping" and increment the group by 1. What I am looking for is the output as shown below.

ID CAT GROUP
67 0 1
68 0 1
69 0 1
70 1 2
71 1 2
72 0 3
73 0 3
74 0 3
75 0 3
76 1 4
77 1 4

View 6 Replies View Related

Summing Data Between Two Dates (When Dates Are Different Per Record)

Nov 15, 2011

I have a master table which shows all transactions per record (person) over a financial year.

Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.

I need to be able to create a field which sums all expenditure per record between the start and end dates

Name Start Date End Date Invoice Date Amount

Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200

In this case I would only want to sum 7/7/11 as this is between the start and end dates

I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly

(The start date and end date will always be the same per person)

Is this possible in access?

View 10 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

Modules & VBA :: Delete Of A Record From A List Of Records In A Subform Deletes Wrong Record

Jun 28, 2013

When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.

Code:

Option Compare Database
Dim FocusBln As Boolean
Private Sub Identificeer()
Me.[Datum Aangemaakt].Visible = True
Me.[Datum Aangemaakt].SetFocus
If Me.[Datum Aangemaakt].Text = "" Then

[code]...

View 11 Replies View Related

Queries :: List Of Dates And Records With No Matching Record OR Existing Record With Higher Date

Nov 24, 2014

I've been asked to get some information from my database and I'm a bit stuck.

I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field

My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.

So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)

I can manage a query that looks at a certain date that it prompts for on each run:

Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));

I would like a query that lists all dates in a range, and shows the same information for each day listed.

View 9 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







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