Calculate sum of a field within the query

I have a query that returns the details for a failure record during a 
specified time period. Where if I search between 1/1/08 and 1/31/08 the query 
returns the records of all failure records during that period. The row shows 
the part number and the total parts failed for that record. 
I need the query to then sum the total part failures for the previous 12 
months. So I would see the following:
Record # | part # | parts failed | Origination Date| Total Parts failed past 
12 months 
44444    | x          | 4                   | 1/2/08                | 200
44445    | Y          |1                    | 1/23/08             | 25

the total parts failed past 12 months would have to reference field part #, 
origination date and parts failed fields. The only criteria I enter is the 
origination date range. 

0
Utf
3/6/2008 4:55:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
2828 Views

Similar Articles

[PageSpeed] 28

auto correct is the devil wrote:

>I have a query that returns the details for a failure record during a 
>specified time period. Where if I search between 1/1/08 and 1/31/08 the query 
>returns the records of all failure records during that period. The row shows 
>the part number and the total parts failed for that record. 
>I need the query to then sum the total part failures for the previous 12 
>months. So I would see the following:
>Record # | part # | parts failed | Origination Date| Total Parts failed past 
>12 months 
>44444    | x          | 4                   | 1/2/08                | 200
>44445    | Y          |1                    | 1/23/08             | 25
>
>the total parts failed past 12 months would have to reference field part #, 
>origination date and parts failed fields. The only criteria I enter is the 
>origination date range. 


You can use a subquery to do that calculation:

SELECT [Record#]. [Part#], [parts failed], 
				[Origination Date],
				(SELECT Sum([parts failed])
				 FROM thetable Ax X
				 WHERE X.[Part#] = T.[Part#]
						And [Origination Date] Between
							DateAdd("yyyy", -1, [end date]) And [end date]
				) As yearfailed
FROM thetable As T
WHERE [Origination Date] Between [start date] And [end date]

-- 
Marsh
MVP [MS Access]
0
Marshall
3/6/2008 6:33:36 PM
Reply:

Similar Artilces:

Field Service Inventory Transfer
Good Afternoon, I go to Transactions--> Service Call Management--> Inventory Transfers. Then I bring up a document and change the 'To Site ID' . When I put in a 'QTY Picked' and save the document. The document also changes the 'Qty Ordered' to zero. Why does it change the quantity ordered field? I am told that in past versions the quantity ordered field didn't change (we are using 9.00.0294). If this is true can any setting be changed so that the quantity order field doesn't change when changing the 'To site ID' and the entering a 'Qty...

duplicate results in query
Hi, I am getting duplicate results when I run a query. Here are the details. Bothe tables have the same data only dates have changed. what I am trying to do is find out which dates have changed. The problem is this. Some purhcase orders are split so the same PO and line has 2 dates. when i combine the 2 tables I get 4 lines for these instead of 2. example Vendor PO Line Qty Date X 1234 1 2 7/1/2010 X 1234 1 2 8/1/2010 When i combine this with the other table which is the same for these I get the followin...

automatic "from" field modification
The issue has something to do with security why I can not seem to accomplish the following. I would like to have a different address then the account from which I'm sending. Manually adding the name is no problem, but doing this 20 times a day is annoying. Via a macro I can get it to create a new email with the proper "from" filled in, but this is no use when replying to emails. Does anyone know how to have all outbound emails automaticaly sent with a predifined name in the from field? =CF'm affraid this only works on a POP account, not MAPI.... >-----Original ...

Adding Date to the table name in a Make Table Query
Before I delete the data in a particular table, I need to copy it into a table with the prefix of today's date. Is there a way to create a make table query that does this? Is there a better way? I've tried things like: SELECT tblCO_Order_20.* INTO Format(Now(),"yyyymmdd") & tblCO_Order_Level20 FROM tblCO_Order_20; But no luck. I can't find a macro command to copy a table and save it with a different name. What other options do I have? On Tue, 25 Sep 2007 16:30:01 -0700, Cathy wrote: > Before I delete the data in a particular table, I need to copy it in...

Summing in an array
I would like to write an array formula that sums a column based upon th values in another column but in addition to just providing a sum i then needs to perform another mathematical function. For example: 10 $100 50 $1000 40 $500 Given the above data I would like to create a formula that adds th dollar values in the second column if the value in the first column i greater than 30 but in addition to adding those values it multiplie each of the dollar figures by 25% of the value in the first column s that the value for this proposed formula would be (.25*50*1000) (.2...

Pivot table field list missing
I'm using 2007 and have created several pivot tables from this particular database but now, suddenly when I create a pivot table from scratch, the field list is not displayed although the show/hide buttons on the ribbon are lit up. My active cell is inside the table, what have I done wrong??? Regards Diane Hi Diane Options tab>Field List -- Regards Roger Govier Dianeg wrote: > I'm using 2007 and have created several pivot tables from this particular > database but now, suddenly when I create a pivot table from scratch, the > field list is not di...

qry return no results in given field
I have a query that contains a union query and 2 other subqueries "A" and "B". A field that is contained in "A" subquery is returned blank in my query. In a separate query this same field is returned properly. This separate query also contains a union query (same as above) and 2 other queries (one of them is same as above "A" and "C" different from above). What would prevent this field from displaying its values in 1 query and not another ? Like I said, the values for the field are part of the subquery "A" and should be dis...

IF / SUM
I have a spreadsheet with 17 rows, each representing a project name. In the columns, I have 2 cells for each week: Est and Actual. At the end of each row, I would like a row total for Est and another for Actual. As there are 20+ weeks represented, I cannot select the cells that have the criteria I'm looking for without using a more complex formula than SUM. Any ideas? The spreadsheet looks like this: Project Name Apr 3-9 Apr 10-16 Apr 17-23 Total Est | Actual Est | Actual Est | Actual E | A Project A 20 | 22 40 | 37 25 | 26 85...

calculate headcount
I have a large dataset consisting of start- and endtimes of personel shifts. I'd like to make a query that calculates the number of shifts per time period. I used to perform this task in Excel using sumpruduct or array formulas quite easily, but I can't figure out a way to to the same in Access. Is it possible? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1 Post sample data with table and field names plus datatype. -- Build a little, test a little. "bertusavius via AccessMonster.com" w...

Excel Showing a Calculation and a Value
I would like to know if it is possible to take the value from two cells and calculated the value and have the information displayed in the calculating cell as both what is being calculated and the calculated value. Example C3 = 5 C2 = 10 C6 = C3/C2 and would show 50%, i would like it to show "(10/5) 50%" With 3 in A1 and 12 in B1, this formula =B1&"/"&A1&" = "&B1/A1 displays 12/3 = 4 However, you will not be able to do arithmetic with the result best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "C...

Creating a calculation based on a grouping
I have a database used to record invoices (vendor, date, number, service period beginning and end, amount, etc.). My report shows the records for a specific time frame. (In the database, the service period is entered as: SvPeriod: 1/01/2008, SvPeriodEnd: 1/31/2008.) The records are grouped by vendor and then by service period. However, when creating the report, I chose to use SvPeriod by Month, in which case Access turned the SvPeriod date into the actual month and year and created the "SvPeriod by Month" field. (For example, it took 1/01/2008 and created January 2008.) ...

db design to calculate overlap in group membership in time
Hi all, I am a biologist studying the social structure of birds. I am currently brainstorming ideas for constructing a database that will allow me to easily calculate the number of seconds that one bird is in the same group as another bird. Because group membership changes frequently with birds arriving and departing at different times, and often arriving, departing, and re-arriving on the scale of seconds, I will have many thousands of records. I am trying to determine how to best set up this database before entering all the data. I have 2 goals with this database: 1) I need to f...

Highlight ing of active line within excel
is it possible to when using the find option when locating something within a spreadsheet can the search resulting line can that be highlighted to provide easy reading Take a look at Chip Pearson's RowLiner: http://cpearson.com/excel/RowLiner.htm In article <40AD1702-43AB-48F4-9222-4D42C983B893@microsoft.com>, "glen2351" <glen2351@discussions.microsoft.com> wrote: > is it possible to when using the find option when locating something within a > spreadsheet can the search resulting line can that be highlighted to provide > easy reading ...

Query in a subform field
Hello, I have a database that tracks vacation time. The main form has the basic employee information and the subform has a list of all the days taken. If someone takes off Nov. 23-24, the calculated field at the end of the row will list 1 day. However, this calculation is not working. (I'm using DateDiff). So I decided to write a query which does work, but I'm not sure how to attach this query to the calculated field. Is this possible to do? Thanks very much. Post your DateDiff calulation for suggestions. -- Build a little, test a little. "Joanne&qu...

Query Help 09-24-07
I have two fields TITLE and SUB TITLE which are both COMBO in a form. In the TITLE field i have this records. FURNITURE,STATIONERY ETC ETC In the SUB TITLE field i have this records. CHAIRS,TABLES ETC ETC I need to create one query that if i choose as a title FURNITURE and sub title (leaves it empty), all the furniture will be displayed If i choose title FURNITURE and sub title CHAIRS, it will display all CHAIRS that are under FURNITURE. Any help pls???? thanks a lot Try setting the default on the subtitle combo to "*" and your criteria Like [Forms]![YourForm]![YourCom...

Calculating percent of times a value is in a column and total #
Ok so I have a column with values in it. Say A4:A12, the values entered will always be 1, 2, 3, or 4. I need a formula that will calculate the % that say a 1 appears in that range and I need a formula that will calculate the total number of 1s in that range. Thanks! =countif(a4:a12,1)/count(a4:a12) Format as percent Regards, Fred "clbritt76" <clbritt76@discussions.microsoft.com> wrote in message news:28AFAD8A-88BC-4151-AE7F-5463AA6E9B68@microsoft.com... > Ok so I have a column with values in it. Say A4:A12, the values entered > will > alwa...

Zeros Showing in Form Field
I have currency and number fields on my form that are showing zeros. I removed the zeros from the Default Value in the Table. The Default Value for each field on the form is empty. The zeros are still showing. I appreciate all help provided. Linda Are they showing zeros on new records? Or just on the old records? Previously saved records may still be holding those zeros in the table. On Tue, 9 Feb 2010 13:20:03 -0800, ADB_Seeker <ADB_Seeker@discussions.microsoft.com> wrote: >I have currency and number fields on my form that are showing zeros. >I removed the zeros fr...

pivot tables-data field formatting
How do I change the default data field type from Count to Sum? I don't think you can. If all the entries are numeric, you'll get Sum, but if any are text (or empty), you get count. popp25 wrote: > > How do I change the default data field type from Count to Sum? -- Dave Peterson All of my entires are numeric, but my default settings do not give me Sum, they default and give me "Count". I have to change each to Sum. "Dave Peterson" wrote: > I don't think you can. > > If all the entries are numeric, you'll get Sum, but if any are...

Using a command button to open an image based on field data
Hello. I have a form with a command button on it. This form is linked to a query with a field called "ID". What I am trying to do is make it so that when the user clicks on the command button, a separate form (frm_Image)pops up with the corresponding picture to field ID. I have the images in a folder on my C: drive. Any help on this is most appreciated. I'm going to do a little experimenting now.... Check out openargs in the VB help section. HTH Damon <rsfishel@gmail.com> wrote in message news:1174046910.691059.94060@e1g2000hsg.googlegroups.com... > Hello. I ...

Calculating Software Maintenance and Support Renewals
We are a software company that sells annual maintenance and support contracts. These contracts are typically purchased in years, however they can also be purchased in month increments - Does anyone know if this system can automate this so the reps don't have to figure out fractions. ...

Queries making multiple records
Hi, I have just started writing my database all over again, cause i was advised that it was incorrect the first time eg, relationships, tables etc. Well i think the penny has finally dropped and now i think i know what i am doing (not really sure though), i have fixed all the relationships and tables etc, I have used "normalization forms" and with that i have put on one table description and qty and the other table i have put description qty and price. I have linked these with a query using the Primary Key which i have used what type of part it relates to so that a...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

Scripting mass folder creation within Exchange 5.5 mailbox
Hi all, I am wondering if anyone has heard of a way to script the creation of a folder within every user's mailbox on Exchange server 5.5? I am trying to put filtered SPAM into a "Junk" folder within a users mailbox and then set a rule to delete all content with 30 days if unread. Of course there can be no user interaction for the process, and I'd like to do it from the server side. All ideas are welcome, except calling MS, already did. Thanks in advance, John Just an FYI that if you "ever" go to Exchange 2003/Outlook 2003 a "Junk E-mail" folder is...

creating autofill fields
So I need to create an automatically generated record in a table when the previous fields time goes into the next day. For example. If a employee input his time as 9Pm-2am on 1/1/08, I need to automatically generate a record that has the time from 00-2am with the next days date:1/2/08 I also have to cap each days time at 12 midnight. I am not sure where to start with this entire sequence, can someone give me some guidance? Thanks. Christie You've described "what", but not "why". You've described "how", but not explained what having this kind...

Pivot Table-Calculate Percentage of Parent Row Item
I have been using Excel to produce Pivot tables, however I canno calculate percentages that depend on their parent rows. Example: I have on the row three levels:Let's say Level 1:Service Level 2:Pacakge type Level 3:Insurance I am counting the # of customers belonging to those levels and want t have the percentage calculated against Level 2 If Level 3/Insurance values and counts were: Premium: 10 Low:15 Regular:20 So, the total would be :10+15+20=35 and the percentages calculate should be Premium: (10/35) Low: (15/35) Regular (20/35) I can do that on Ms-Access but cant find a way to d...