Count or sum help on query

This is my SQL code for a query I'm running that is working great for my 
report. What I need now is that I need the total of Y's and N's for each 
column. So I need to know how to make my sql put at the bottom on report 
Total count for how many Y's for conductor casing,and how many N's, how many 
Y's for surface casing, How many N's, How many Y's for Prod casing, how many 
N's, How many CMPS Y's and how many N's and How many Wells on Prod Y's and 
N's count. Then my report will be complete. This is my code so far and my 
query is running exactly how I need it to. I just need totals to complete it. 
Thanks.

SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME, 
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS, 
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG, 
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG, 
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![CONDUCTOR_CSG])=True,"N","T") AS 
Conductor_Csg__Set, 
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T") AS 
Surface_Csg_Set, 
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![PROD_CSG_SET])=True,"N","T") AS 
Prod_Csg_Set, 
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![FIRST_GAS_TOW])=True,"N","T") AS 
First_Gas, IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![RELEASE_RIG])=True,"N","T") 
AS Rig_Release
FROM MANUAL_INPUT_DRILL_DATES
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME, 
MANUAL_INPUT_DRILL_DATES.WELL_NAME;

Jannie
0
Utf
2/3/2010 1:04:02 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
632 Views

Similar Articles

[PageSpeed] 35

Since this is for a report, it is easiest to add totals/aggregates in the 
report footer section. To count values, you only need to create a true/false 
calculation. For instance:
  [Surface Casing]="Y"
This will return -1 for true or 0 for false. Change the value to positive 
using Abs() and then Sum it.
  =Sum( Abs( [Surface Casing]="Y" ) )
and
  =Sum( Abs( [Surface Casing]="N" ) )
This assumes you have a text field named [Surface Casing] with values of "Y" 
and "N".


-- 
Duane Hookom
Microsoft Access MVP


"jannie" wrote:

> This is my SQL code for a query I'm running that is working great for my 
> report. What I need now is that I need the total of Y's and N's for each 
> column. So I need to know how to make my sql put at the bottom on report 
> Total count for how many Y's for conductor casing,and how many N's, how many 
> Y's for surface casing, How many N's, How many Y's for Prod casing, how many 
> N's, How many CMPS Y's and how many N's and How many Wells on Prod Y's and 
> N's count. Then my report will be complete. This is my code so far and my 
> query is running exactly how I need it to. I just need totals to complete it. 
> Thanks.
> 
> SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME, 
> MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS, 
> MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG, 
> MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG, 
> IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![CONDUCTOR_CSG])=True,"N","T") AS 
> Conductor_Csg__Set, 
> IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T") AS 
> Surface_Csg_Set, 
> IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![PROD_CSG_SET])=True,"N","T") AS 
> Prod_Csg_Set, 
> IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![FIRST_GAS_TOW])=True,"N","T") AS 
> First_Gas, IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![RELEASE_RIG])=True,"N","T") 
> AS Rig_Release
> FROM MANUAL_INPUT_DRILL_DATES
> ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME, 
> MANUAL_INPUT_DRILL_DATES.WELL_NAME;
> 
> Jannie
0
Utf
2/3/2010 3:31:02 PM
Reply:

Similar Artilces:

web address help
whats the web address for outlook so i can sign on? I have it stored on my home computer but dont have it here thanks Outlook doesn't have a web address unless you use an Exchange server. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com <anonymous@discussions.microsoft.com> wrote in message news:64da01c3e61b$58282fc0$a401280a@phx.gbl... > whats the web address for outlook...

help installing exchange 2000 in one of 3 sites
I have a domain cotoso.com installed and configured as AD for 3 companies interlinked together. and + a total of 3 DC in 3 different sites that are connected by VPN. i have exchange installed in each site connected on the same domain contoso.Local(+ E-mail -->contoso.com) + hosting their own companie domain name such as x.com or y.co or z.com for each company. I have a routing link configured + 3 administrative groups in exchange system manger for each company. one DC in one site that also has exchange 2000 installed on it has to be changed because of poor performance. now i'm ...

HELP!! Viewing email images
I have just recently switched to using Outlook rather than Outlook Express as my email program. I now find that any image attachments (.jpg, .bmp etc.) I receive are not visible onscreen as they used to be in Outlook Express. I have to click on each attachment icon to view... It's annoying, especially if I get a series of pics...I have to manually ope each one... Does anyone know how to set Outlook so that it shows me these images onscreen??? Many Thanks, Darren. ...

Need help getting this code to compile under VS.NET 2003 (compiles under VS6.0).
I'm tasked with converting a LARGE project from VS6.0 to VS.NET 2003. Here is a VERY scaled down version of an error I'm getting. I must be getting rusty, but the way to get around this compiler error is just not coming to me. If you need to compile this, just create a Win32 console app and specify to add support for MFC, then copy this code into the cpp file and press compile. class CLogItem : public CObject { protected: virtual BOOL CheckConflict(CLogItem* pItem) const; }; struct MapEntry; //forward declaration.. typedef CArray<MapEntry, MapEntry&> CMapEntryArray;...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

help!! smtp protocol error occurred
exchange 2000 srv/windows 2k srv smtp protocol error occurred when trying to send mails. receives fine. Still waiting for some relavent information so we can help you solve your problem. >-----Original Message----- >exchange 2000 srv/windows 2k srv >smtp protocol error occurred when trying to send mails. >receives fine. > > >. > I had that problem, I telneted into the servers that the error occurred and found I was on a blacklist. Had to sumit to an open relay test, and bam, no more problems. ...

Help !!!!
I was migration my Exchange 2000 to 2003 I do my forest prep and domain prep But i can't create mailboxes ??? Why??? some documentation please... What actually happens? What error do you get? -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "A.dian�" <Adian@discussions.microsoft.com> wrote in message news:31CB593F-F4DD-4CAB-9F4E-C2131BF7CF95@microsoft.com... > I was migration my Exchange 2000 to 2003 I do my forest prep and domain prep > But i can't create mailboxes ??? > Why??? > some documentation...

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

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

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

Pulling counts out of query results
I have a query that has one field Type which is set to Count The query results are used in a report. The report has the fields in the detail section as TYPE and CountOfType (1 line only in the detail section) The report looks like this when displayed AS 28 AV 17 OR 5 I need to be able to get the individual AS No (28) and add it to another number elswhere in the report. How can I do this? I have tried using a textbox with an if function (if [type]="AS",CountOfType,0 but that did not work. Any help appreciated. Ray I think you will need to do it in the query. But try addi...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...

Conditional sum
Hi Can you help? I would like to sum a matrix according to a condition in a row and in different condition in a column. I know how to do this one dimensionally (either in the row or the column) using sumproduct, but what about two dimensions? Let me give an example, number of widgets produced each week by each type of machine A B C D 1 weeks>>1 2 3 2 Type 1 10 15 11 3 Type 2 20 5 10 4 Type 1 5 12 21 Etc So, I’d like the formula to be a...

Newbe help with a lookup
Hi, I&#8217;m trying to make some modifications to a salary matrix and I&#8217;m having trouble with a lookup. In column A I have a series of numbers from 0 to 30 representing pay grade steps where 0 represents a new employee and 30 represents an employee having completed 30 years of service. In column B I have an annual salary for each of the corresponding pay grade steps. I want to take a new salary and find the closest salary in column B and return the corresponding step number. For example if the new salary is $73,415.13 and the matrix shows step 15 is 73,205 and step 16 is 73...

HELP
We have an application that is used by over 8000 people worldwide. One of our users is just starting to have the following problem: Run-time error '-2147467259(80004005)': Method 'Add' of object 'CommandBarControls' failed I have read that this problem can be caused by trying to open the file in Internet Explorer. The user has told me that she has tried opening MS Excel and then the .xls file and she still gets the same problem when the file is trying to open. She is the only one that has had this problem. Could there be something wrong with her Excel settings or som...

Count Unique Items with Multiple Criteria
I am trying to get a list of how many lots a particular car model is on. For example, say we have a spreadsheet that looks like: Model License Lot Ford xjd-394 1 Chevy gwg-394 2 Ford sdf-333 1 Ford lkj-111 3 Toyota skd-333 4 Toyota shk-584 4 I am loking for a way to get data that says how many unique lots each car is on, so for example: Ford: 2 Chevy: 1 Toyota: 1 I was trying to do this with Pivot tables and the count functionality, but it isn't quite getting me the results I want. I can get the results with a pivot table ...

Help with keeping inventory.
Hello Everyone. I was wondering if someone might be able to help me. I'm trying t come up with a way to keep inventory of product. Basically what I need is to keep track of the current inventory of items... and what I'd like is for different users to be able to ope the worksheet and enter the NUMBER OF PRODUCT they are removing at tha time.... and for the total inventory of each item to be adjuste accordingly. The only catch is - I'd like to be able to use the SAM CELL always -- that is - I want only one cell to be the one that i used to enter the amount of inventory being o...

Help with Outlook Automation
I launch Outlook form MS Access 2003 using the code under the double line. When I send the first email it works fine. But if I send another email everything freezes up. I have to use the task manager to Kill OUTLOOK. And then it is immediatly replace by another OUTLOOK session and the email window comes up. I was wondering if my code could be improved to prevent that from happening. Any suggestions please! RBollinger ======================================================= Private Sub Command11_Click() On Error GoTo Err_Command11_Click Dim stDocName, mTO, mCC, mSu...

Forwarding balance for running sum
I created this query with a running sum, which is sorted by date then by transaction number. I put this into a form for entering transactions for an investment money market account. It works very well. However, now that I have one years worth of data in there, I would like to limit the data for the form as well as for reports. As soon as I limit the data range, I loose the beginning balance. Can anybody give me an idea on how I could create a beginning balance when I select a date range or a starting date? Anne wrote: >I created this query with a running sum, which is sor...

Help on CString to char*
Being a newbie to MFC, I am having problems on copying CString to a char* Below is my attempt but I getting this error below: ===================================================== Debug Assertion Failed! Program: C:\geac\GEACstatusptt.exe File: dbgheap.c Line: 1044 Expression: _CrtIsValidHeapPointer(pUserData) Below is my code the way that I am doing now, look at switch 4 and this is where I call a DLL library "McaAddPageMember" ====================================================== char cPAGER_CODE[16]; CPsnstatuspttApp::m_LogTrace.WriteLine("CPsnstatuspttView::Call_Page_M...

Help replacing text with Yes or No
I have a field formated as general. The field contains either 1 or is left blank. If the field has a 1 I want to replace it with Yes and if the field is blank I want to replace it with No. any help is appreciated. -- Jerry Save your data and use a copy for this exercize........... Assuming your data in Column A, put this in B1 and copy down........ =IF(A1=1,"Yes","No") Then highlight the column and do Copy > PasteSpecial > Values to get rid of the formulas..........then delete column A if you wish....... Vaya con Dios, Chuck, CABGx3 "Jerry Arnone, ...

Pub2K Help does not work
I have Pub2K. Recently I've updated my Office 2K with SP1 and, I think, SP3 (whatever was available on MS site). Today, I noticed that Help in Publisher does not work. Every time I try to see Help, I'm getting this Internet Explorer Script Error Message: <An error has occurred in the Script on this page. Line 1, Char 1, Error: Object doesn't support the property or method, Code 0, URL mk: @MSITStore:C:/Program...> What's going on? Did Office update caused this problem or something else is a culprit? How can I get Pub2K Help work again? Thanks for any suggestions. P.S. Hel...

help needed on multiple option buttons
Hi, I am trying to use option buttons in a few different locations in Excel. In one cell i want three options and three different buttons. In another locations, two options with their own two buttons. However, the 5 buttons act as 5 linked options, so i can only have one or another button selected at a time. I would like one of the first three selected and one of the next two and so on. Can anybody help me work out how to resolve this. Thanks, If you used optionbuttons from the Forms toolbar, put a nice groupbox (also from the forms toolbar) around each group. If you use optionbuttons ...

Strange query results/Wild characters???
Hello. My query is showing me strange values from whem loading a value from a form. It's very strange because if you have a form named FormA and inside a field named Field1 and if the field has a default value of 2 and if you run it. Then if you'll make a query with any table or query and put this in the column: Test:([forms]![FormA]![Field1]) your result will be 2, tha same as the the Form Field. My problem is that my query instead of showing me the value of 2 is showing another thing very strange, such as wild characters or value that has nothing to do with it. If I use t...