Group By - Select Columns Questions

I have a view which uses group by - containing 2 aggragate functions, Sum() 
and Count().

What I want to do is include in my Select portion of my view , a column that 
I do not want to be included in the Group by.

Is there a simple way to do this?

Example:

Select A.Field1,B.Field2,A.Field3,A.Field4,A.Field5,Sum(Field2),Count(*)
From tablename A
Left Outer Join Tablename2 B on
B.Rec_ID = A.Rec_ID

Group by
A.Field1,
B.Field2
A.Field3
A.Field4

(Note: In this case A.Field5 is EXCLUDED in the Group by)

Your help is greatly appreciated.

-Ray



0
ray
6/14/2010 5:43:50 PM
sqlserver.programming 1873 articles. 0 followers. Follow

10 Replies
1105 Views

Similar Articles

[PageSpeed] 36

Field2 is in the GROUP BY, but Field5 is not.

If you group by Field2, your SUM will be affected as it will only
include like Field2 values in that SUM.  This does not seem likely to
be what you would want.

You have to GROUP BY Field5 or use another aggregate function on
Field5, because differing values for Field5 cannot be displayed with
aggregates from the other columns.  This will cause an error.  You
could do an aggregate function on Field5, however, rather than a GROUP
BY if that would solve the problem.  Perhaps selecting MAX(Field5) or
MIN(Field5), assuming that Field5 probably has only one important
value would do the trick?

-Eric Isaacs
0
Eric
6/14/2010 9:57:38 PM
ray (judy@yahoo.com) writes:
> I have a view which uses group by - containing 2 aggragate functions,
> Sum() and Count(). 
> 
> What I want to do is include in my Select portion of my view , a column
> that I do not want to be included in the Group by. 
> 
> Is there a simple way to do this?

Yes, use SQL Server 4.x. It was permitted then. The results rarely
made any sense.
 
> Select A.Field1,B.Field2,A.Field3,A.Field4,A.Field5,Sum(Field2),Count(*)
> From tablename A
> Left Outer Join Tablename2 B on
> B.Rec_ID = A.Rec_ID
> 
> Group by
> A.Field1,
> B.Field2
> A.Field3
> A.Field4
> 
> (Note: In this case A.Field5 is EXCLUDED in the Group by)
 
And if there are different values in A.Field5 what do you want to
happen?


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/14/2010 10:12:54 PM
Eric,

Thanks for your input on this. Hopefully I can put some more clarity on this 
to where I might have been a bit general.

The reason I don't want to include Field5 in the Group By is because its a 
date field.  I don't want my result set further grouped by Dates, but, on 
the otherhand, I want to be able to select a subset of records using a where 
clause on this view.

(ie Select * from View_Sales Where Field5 Between '1/1/2010' and 
'1/30/2010')

As an Example I'm selling Blue, Green and Red Hats.  My view WITHOUT Field5 
(Date) shows the following:

Description      Qty          Item Price     Total

Blue Hat            2            10.00            20.00
Red Hat            1             10.00            10.00
Green Hat         4             10.00            40.00

Example With  Field5 (Date) is in Select and Group by:

Description      Qty          Item Price     Total

Blue Hat            1            10.00            10.00
Blue Hat            1             10.00           10.00
Red Hat            1             10.00            10.00
Green Hat         2             10.00            20.00
Green Hat         1             10.00            10.00
Green Hat         1             10.00            10.00

As can be seen since the Blue Hats and Green Hats were sold on different 
dates, it didn't group by them (the way I want) as in the First Example.

Again your comments and input on this are greatly appreciated.

-Ray

Hope this example shows my dilema.
"Eric Isaacs" <eisaacs@gmail.com> wrote in message 
news:7b091961-4f10-4869-b88d-a466a55df3aa@n37g2000prc.googlegroups.com...
> Field2 is in the GROUP BY, but Field5 is not.
>
> If you group by Field2, your SUM will be affected as it will only
> include like Field2 values in that SUM.  This does not seem likely to
> be what you would want.
>
> You have to GROUP BY Field5 or use another aggregate function on
> Field5, because differing values for Field5 cannot be displayed with
> aggregates from the other columns.  This will cause an error.  You
> could do an aggregate function on Field5, however, rather than a GROUP
> BY if that would solve the problem.  Perhaps selecting MAX(Field5) or
> MIN(Field5), assuming that Field5 probably has only one important
> value would do the trick?
>
> -Eric Isaacs 


0
ray
6/15/2010 1:01:25 AM
Hi Erland,

Thank you for your comments and questions.  I just posted a full breakdown 
of what I'm looking at.  I was probably a bit too general in my first 
posting.  The second one lays it out fully on what I'm tying to accomplish.

thanks

-Ray

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D98230255EYazorman@127.0.0.1...
> ray (judy@yahoo.com) writes:
>> I have a view which uses group by - containing 2 aggragate functions,
>> Sum() and Count().
>>
>> What I want to do is include in my Select portion of my view , a column
>> that I do not want to be included in the Group by.
>>
>> Is there a simple way to do this?
>
> Yes, use SQL Server 4.x. It was permitted then. The results rarely
> made any sense.
>
>> Select A.Field1,B.Field2,A.Field3,A.Field4,A.Field5,Sum(Field2),Count(*)
>> From tablename A
>> Left Outer Join Tablename2 B on
>> B.Rec_ID = A.Rec_ID
>>
>> Group by
>> A.Field1,
>> B.Field2
>> A.Field3
>> A.Field4
>>
>> (Note: In this case A.Field5 is EXCLUDED in the Group by)
>
> And if there are different values in A.Field5 what do you want to
> happen?
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
ray
6/15/2010 1:03:35 AM
Except you haven't shown your desired results. What values do you wish to 
see in that Field5 column? Think about it. Show us the rows of data you wish 
to see with Field5 included, given that you only wish to see three rows of 
data in the results and there seems to be 6 values in that Field5 column.
Read Eric's reply a litle more carefully ...

ray wrote:
> Hi Erland,
>
> Thank you for your comments and questions.  I just posted a full
> breakdown of what I'm looking at.  I was probably a bit too general
> in my first posting.  The second one lays it out fully on what I'm
> tying to accomplish.
> thanks
>
> -Ray
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns9D98230255EYazorman@127.0.0.1...
>> ray (judy@yahoo.com) writes:
>>> I have a view which uses group by - containing 2 aggragate
>>> functions, Sum() and Count().
>>>
>>> What I want to do is include in my Select portion of my view , a
>>> column that I do not want to be included in the Group by.
>>>
>>> Is there a simple way to do this?
>>
>> Yes, use SQL Server 4.x. It was permitted then. The results rarely
>> made any sense.
>>
>>> Select
>>> A.Field1,B.Field2,A.Field3,A.Field4,A.Field5,Sum(Field2),Count(*)
>>> From tablename A Left Outer Join Tablename2 B on
>>> B.Rec_ID = A.Rec_ID
>>>
>>> Group by
>>> A.Field1,
>>> B.Field2
>>> A.Field3
>>> A.Field4
>>>
>>> (Note: In this case A.Field5 is EXCLUDED in the Group by)
>>
>> And if there are different values in A.Field5 what do you want to
>> happen?
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

-- 
Bob Barrows 


0
Bob
6/15/2010 1:31:39 AM
Hi Bob,

Yes, Thanks.

<What values do you wish to see in that Field5 column?>

None.  Only want a reference to use when creating a where clause - on date 
range. If the column doesn't exist in select, I cannot use it.

Here's the result set with Field 5:

Example With  Field5 (Date) is in Select and Group by:

Description      Qty          Item Price     Sale Date    Total

Blue Hat            1            10.00            1/3/2010    10.00
Blue Hat            1             10.00           1/15/2010   10.00
Red Hat            1             10.00            1/4/2010    10.00
Green Hat         2             10.00            1/4/2010    20.00
Green Hat         1             10.00            1/15/2010  10.00
Green Hat         1             10.00            1/23/2010  10.00

I only want to maintain the accurate group counts  and totals.  (ie Blue 
Hats Qty= 2, Total 20.00 -- Green Hat Qty = 4, Total 40.00.)

I just reread Erics reply.  He mentioned the aggregates Min() and Max(). 
These, of course, will not be include in the group by.   I'll try those to 
see what result
I can obtain.


Your reply is greatly appreciated.

-Ray



"Bob Barrows" <reb01501@yahoo.com> wrote in message 
news:CJARn.126038$gv4.51524@newsfe09.iad...
> Except you haven't shown your desired results. What values do you wish to 
> see in that Field5 column? Think about it. Show us the rows of data you 
> wish to see with Field5 included, given that you only wish to see three 
> rows of data in the results and there seems to be 6 values in that Field5 
> column.
> Read Eric's reply a litle more carefully ...
>
> ray wrote:
>> Hi Erland,
>>
>> Thank you for your comments and questions.  I just posted a full
>> breakdown of what I'm looking at.  I was probably a bit too general
>> in my first posting.  The second one lays it out fully on what I'm
>> tying to accomplish.
>> thanks
>>
>> -Ray
>>
>> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
>> news:Xns9D98230255EYazorman@127.0.0.1...
>>> ray (judy@yahoo.com) writes:
>>>> I have a view which uses group by - containing 2 aggragate
>>>> functions, Sum() and Count().
>>>>
>>>> What I want to do is include in my Select portion of my view , a
>>>> column that I do not want to be included in the Group by.
>>>>
>>>> Is there a simple way to do this?
>>>
>>> Yes, use SQL Server 4.x. It was permitted then. The results rarely
>>> made any sense.
>>>
>>>> Select
>>>> A.Field1,B.Field2,A.Field3,A.Field4,A.Field5,Sum(Field2),Count(*)
>>>> From tablename A Left Outer Join Tablename2 B on
>>>> B.Rec_ID = A.Rec_ID
>>>>
>>>> Group by
>>>> A.Field1,
>>>> B.Field2
>>>> A.Field3
>>>> A.Field4
>>>>
>>>> (Note: In this case A.Field5 is EXCLUDED in the Group by)
>>>
>>> And if there are different values in A.Field5 what do you want to
>>> happen?
>>>
>>>
>>> --
>>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>>
>>> Links for SQL Server Books Online:
>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>> SQL 2000:
>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> -- 
> Bob Barrows
> 


0
ray
6/15/2010 1:55:34 AM
ray wrote:
> Hi Bob,
>
> Yes, Thanks.
>
> <What values do you wish to see in that Field5 column?>
>
> None.  Only want a reference to use when creating a where clause - on
> date range. If the column doesn't exist in select, I cannot use it.

All right, now you've lost me.  The date can certanly be filtered in 
theWHERE clause without including it in the group by if you don't need it to 
appear in the final result ...

>
> Here's the result set with Field 5:
>
> Example With  Field5 (Date) is in Select and Group by:
>
> Description      Qty          Item Price     Sale Date    Total
>
> Blue Hat            1            10.00            1/3/2010    10.00
> Blue Hat            1             10.00           1/15/2010   10.00
> Red Hat            1             10.00            1/4/2010    10.00
> Green Hat         2             10.00            1/4/2010    20.00
> Green Hat         1             10.00            1/15/2010  10.00
> Green Hat         1             10.00            1/23/2010  10.00

This appears to be the source data, not the desired result.
>
> I only want to maintain the accurate group counts  and totals.  (ie
> Blue Hats Qty= 2, Total 20.00 -- Green Hat Qty = 4, Total 40.00.)
>
> I just reread Erics reply.  He mentioned the aggregates Min() and
> Max(). These, of course, will not be include in the group by.   I'll
> try those to see what result
> I can obtain.
>
>
I'm lost. Is this the desired result (from your previous reply?):
Description      Qty          Item Price     Total

Blue Hat            2            10.00            20.00
Red Hat            1             10.00            10.00
Green Hat         4             10.00            40.00

If so, and there is data outside the date range that you haven't shown us, 
then it is simply a matter of:(and I'm guessing how to handle the Price 
field - do you want to group by price or aggregate it? I will assume the 
latter)

select description,count(*) Qty, Avg([Item Price]) as [Item Price], 
Sum(Total) As Total from yourdata WHERE Field5 between ... GROUP BY 
Description

Why is this not sufficient?
..


-- 
Bob Barrows 


0
Bob
6/15/2010 10:17:15 AM
If the date is not in the final results, you do not need to include it
in the SELECT portion or the GROUP BY portion of the statement to use
it in the WHERE clause.

SELECT
    A.Field1,
    B.Field2,
    A.Field3,
    A.Field4,
    SUM(Field2),
    COUNT(*)
FROM
    tablename A
    Left Outer Join Tablename2 B on B.Rec_ID = A.Rec_ID
WHERE
    A.Field5 < '20100101' and A.Field5 >= '20090101'
GROUP BY
    A.Field1,
    B.Field2,
    A.Field3,
    A.Field4

-Eric Isaacs
0
Eric
6/15/2010 9:07:11 PM
Also, in my last SQL example, you could use other functions on the
date column and include them in the results and still possibly get
what you want.  In the example above, I selected all the dates in
2009, but I also could have done it by year this way...

SELECT
    A.Field1,
    B.Field2,
    A.Field3,
    A.Field4,
    YEAR(A.Field5),
    SUM(Field2),
    COUNT(*)
FROM
    tablename A
    Left Outer Join Tablename2 B on B.Rec_ID = A.Rec_ID
GROUP BY
    A.Field1,
    B.Field2,
    A.Field3,
    A.Field4,
    YEAR(A.Field5)

I could include Year(A.Field5) and Month(A.Field5) to breakdown by
month as well. This just illustrates how you can still include
portions of the date column in the group by and still possibly return
the results you want.

-Eric Isaacs
0
Eric
6/15/2010 9:18:41 PM
Eric,

These are awesome!  Thank you very very much.  I've implemented a few of 
your examples and they work like a charm!

Thanks again

-Ray

"Eric Isaacs" <eisaacs@gmail.com> wrote in message 
news:f08fe2ee-756d-4f11-bc4f-4b5527075f10@34g2000prs.googlegroups.com...
> Also, in my last SQL example, you could use other functions on the
> date column and include them in the results and still possibly get
> what you want.  In the example above, I selected all the dates in
> 2009, but I also could have done it by year this way...
>
> SELECT
>    A.Field1,
>    B.Field2,
>    A.Field3,
>    A.Field4,
>    YEAR(A.Field5),
>    SUM(Field2),
>    COUNT(*)
> FROM
>    tablename A
>    Left Outer Join Tablename2 B on B.Rec_ID = A.Rec_ID
> GROUP BY
>    A.Field1,
>    B.Field2,
>    A.Field3,
>    A.Field4,
>    YEAR(A.Field5)
>
> I could include Year(A.Field5) and Month(A.Field5) to breakdown by
> month as well. This just illustrates how you can still include
> portions of the date column in the group by and still possibly return
> the results you want.
>
> -Eric Isaacs 


0
ray
6/16/2010 12:20:53 AM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

asap utilities question
I have Msft. Excel Vsn 2002 and latest vsn. of ASAP Utilities. I used ASAP Utilities to merge single rows of data together so that more than one row will fit on a single line of text. My question now, is, is it possible using ASAP Utilities to separate the data that has been merged back into single rows of data? All information welcomed! Aaron ...

Isinteg Warning Message Question
I ran isinteg -pri -test alltests. I came up with 506 warnings. There are basically 4 types of warnings. They are below. Are these something I need to worry about? What should I do? thanks stewart sschwartz@nal.usda.gov ================================== Warning: MsgFolder 165 (Fid=0001-0000000D1864, Mid=0001- 0000000D559D, Inid=0001-000000F33436): PR_READ_RECEIPT_REQUESTED(0029000B) prop in Messages table and MsgFolder table do not match. Warning: MsgFolder 4 (Fid=0001-0000086DC911, Mid=0001- 00000436F972, Inid=0001-000004A583F1): Error JET_errRecordNotFound seeking to INID for this ...

How to select series in chart?
I know I asked this question before, but (sigh) I cannot find the answer now, when I need it of course. How can I select a series in an Excel chart (XY Scatter) using the keyboard, not the mouse? The issue is: I have overlapping series, so it is difficult for me to select a series by moving the mouse cursor to a point in one series and right-clicking it, as I normally do. Someone once mentioned a ctrl and/or shift key combination (I think) that would allow me to select each series explicit in round-robin fashion. That is what I am looking for again. More generally, how could I have found...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

OLK 2k7
Outlook is behaving strangly with the "through the selected account" option. Each time I restart Outlook the rule fails. When I go in to check on the rules I get told that the rule is "invalid". and the "SELECTED" account is no longer selected. Each time the criteria the account needs to be selected by changes. For example with the following data Account Name Email Account mailserver.domain1.com user@domain1.com mailserver.domain2.com user@domain2.com One time I go in and it's asking me to select the account ...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Dotfuscator question
Hello Everybody !!! When i create programm using C# i must understand that my programm from exe - module can be converted to the source file by Reflector. In Order do not allow do this i must use dotfuscator, but i did not find any free normal dotfuscator. May be someone tell me what can i do cause now as think we have interesting paradox. We 've got free technology but if we want to protect our products we must buy expensive commercial dotfuscators. Alex Dmitriev You should post this message in a newsgroup which discusses managed code. This newsgroup does not have much managed traff...

question on the rules wizard
When clikcing on the Rules Wizard, Outlook locks up - consistently. Has anyone got a remedy for this? Thank you, rich rpage@concerto.com ...

OE questions
Ok, I have two questions: 1. With Thunderbird I can use this with a hotkey manager to compose a new message with Tbird "D:\Program Files\Mozilla Thunderbird \thunderbird.exe -compose" -- how can I do the same for Outlook Express and Outlook 2003? 2. When I go to load Outlook Express I get this error message, followed by the error message below it: http://i180.photobucket.com/albums/x296/gwar_1/Untitled-3.png how can I fix this and use Outlook Express? Thanks! posted to the outlookexpress newsgroup via crosspost --=20 Peter Please Reply to Newsgroup for the benefit of others Re...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

Getting rid of selection
How can I get rid of the selection rectangle? It seems that it's always there, with a heavy black rectangle, or there's a light black rectangle marking where it was. I'm trying to get rid of it altogether, so I can capture an image of the sheet for use in a webpage. I can achieve the effect that I want by selecting a cell which is outside the area that I'm trying to capture, but now that I've found that I cannot get rid of it entirely, it is driving me nuts trying to do so. -- Steve Swift http://www.swiftys.org.uk/swifty.html http://www.ringers.org.uk You could al...

HQ Licensing question V.2
I have a client running HQ client at 8 stores + HQ server at the warehouse. They would like to be able to use transfers from the warehouse to the stores, so I would have to setup a new PC running Store Ops and a new database, however, in order for the transfers to take place, I need a HQ Client License correct? Do I also need a POS key? I appreciate any insights you may have. Thanks in advance Hi Phil yes - you'll need an HQ Client license so it can exchange with your headquarters - "export" the wh database as you would any store from HQ no - you won't need a PO...

total group & max function
I am using Access 2003 I created a Db for Machine Mtce. The table consists of 3 tables, (1) Basic Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists of different prev mtce (PM) that need to be completed at various time frames. Ex: "Mach 100" could have 3 different operations; Oper # 100A to change the oil due every 90 days, Oper # 100B to check the belts due every 180 days, Oper # 100C to grease the machine due every 365 days. The 3rd table is the "PM History" table which includes the history of all of the PM's completed with Work Order...

Selecting the Right Text Alignment for a edit box doesn't work
When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong Thanks Dan Dan, "Dan" <anonymous@discussions.microsoft.com> a �crit dans le message de news:DECFE605-A130-416B-9924-60BA0C79D684@microsoft.com... > When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong? > I've no idea :-))) You can open your RC-file as text, and make sure it has the ES_RIGHT style set, thus: EDITTEXT IDC...

Macro
Hi, i need an macro to select all filled cells in range C10:K90. Can this be done? Thanks!!! Range("C10:K90").SpecialCells(xlCellTypeConstants, _ xlNumbers + xlTextValues).Select If you want to select xlErrors and xlLogical add those to xlNumbers + xlTextValues -- Jacob (MVP - Excel) "puiuluipui" wrote: > Hi, i need an macro to select all filled cells in range C10:K90. > Can this be done? > Thanks!!! It's perfect! Thanks! "Jacob Skaria" a scris: > Range("C10:K90").SpecialCells(xlCellTypeConstants, _ >...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Sum a group of numbers
When I first started using Excel, if I had a bunch of different totals on a worksheet, I could hold down the right mouse button and cover the group I wanted to add. The sum of these numbers would appear on the bottom of either the worksheet or a task bar. I have lost the ability to do this. Or I have lost the task bar. How do I get it back? Hi Barbie, That would be the status bar. If it is not showing, go to View>Status Bar to turn it on. If the sum function is still not showing, right click anywhere on the status bar and select which option you want. HTH Martin "Barbie...

MS Project 2007 question
Hello, I am developing a simple schedule with no resources but 2 tasks out of 100 are hazardous and I would like to make the schedule so that when these tasks occur, no other tasks can occur. In other words, even though all tasks are moving around as the schedule is being developed, I would like to make sure no other tasks can be scheduled wherever these 2 hazardous tasks fall. These 2 tasks are serial to every other task in the schedule. Is there a way to do this? Thanks for your help. Mike Create two milestones. Use milestone 1 as the successor task to all tasks th...

basic pivot table group
I have looked at some basic Pivot Table tutorials, but I have a very basic question not answered by their examples. . . . I have data in a spreadsheet formatted like this, each row representing an event: Timestamp Process Message ID 11:23:45:123 processA message123 11:23:45:124 processB message123 11:23:45:136 processC message123 11:23:46:123 processA message456 11:23:47:123 processB message456 11:23:47:678 processC message456 Can a pivot table help me reformat the data like below? ...

Column searching problem
I have a worksheet called "net" containing the following: NETWORK AVG MIN MAX STD SAMPLES ABC 17.17 16.26 17.71 0.4469 19 CBS 12.99 11.69 14.56 0.6524 30 NBC 15.39 14.08 16.70 0.7323 38 NBC* 15.12 14.08 15.99 0.4910 31 Fox 10.63 9.07 12.09 0.9374 8 HBO 10.94 9.07 13.22 1.2507 19 Showtime 11.52 6.84 13.23 1.9017 11 HDNet 18.14 14.22 18.85 1.1619 14 HDNet* 18.45 18.13 18.85 0.2847 13 HDNet-Movies 1...

Column help
I have a seating chart I am trying to design for our graduation ceremony. Right now I have the report set up using columns. I have a rectange box with the graduates first, middle and last name in the box. I need 16 columns across the page for the seats which I have working. My problems are that I need a bold line going down the middle to divide the 8th and 9th rows. ( Students come in from 2 lines and meet in the middle of each row.) My next problem is that I need to have another column to the left of the 1st column and one to the right of the 16th column that will count the row num...