How to get 2 Max values?I have following 2 tables (T1, T2) and rows:
T1 (Column: Id)
----
1
2
3
4
T2 (Columns: Id, Value)
----
1, 1
1, 2
1, 3
1, 4
2, 1
2, 2
2, 3
2, 4
3, 1
3, 2
3, 3
3, 4
4, 1
4, 2
4, 3
4, 4
The result-set I need is:
---------------------------------------
1, 3
1, 4
2, 3
2, 4
3, 3
3, 4
4, 3
4, 4
i.e for each Id from T1, I need 2 max values from T2.
How do I write the query?
Will this work?
Select t1.Id,
( Select t2.Value
From t2
Where t2.Id = t1.Id
Order By Value Desc
Limit 2
)
From t1
I keep getting subquery returns more than one...
Display fields Limited on ValuesI'm pretty fluent in MySQL but I ran into a problem. I have a database that
consists mainly of booleans. It tracks whether people did certain things and
the layout is pretty simple. However, I cannot figure out how to write a
query that will display the person's name and what requirements they still
must meet. I have created a rather lengthy query to display everyone who has
not, but I want to be able to generate a form that will show the people what
they still need to do.
Thanks,
Paul
Well if you want us to think about it give us something to look at (what do
you have, ...
selected value is not saved in combo box
I have problem with saving a combo box selected values in excel file.
I've created a simple excel file using Office 2003. I've added a
combo-box to one of worksheets. After I am selecting any value in the
combo-box, saving the excel file and closing it. After opening the
file there is no value selected in the combo-box.
What I need to do in order to be able to keep selected values in the
combo-box after closing and opening the excel file.
Thanks in advance!
--
MarkDev
------------------------------------------------------------------------
MarkDev's Profile: http://www.excel...
Delete cell value but keep formula in cell.After the formula in a cell has calculated a value I would like to reset the
cell for the next use by deleting the value but keeping the formula in the
cell.
Is this possible in Excel 2007?
You need to explain that a bit.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Lofred" <Lofred@discussions.microsoft.com> wrote in message
news:D3DE79C2-C41E-46C7-8F6C-EAD9960EC528@microsoft.com...
> After the formula in a cell has calculated a value I would like to reset
> the
> cell for the next use by deleting the value but...
Is Not Null?I have a query where I want to use Is Not Null in the criteria to check
whether there is data in a text field. However when I run the query I still
get all the records. Anyone tell me why?
Thanks
tony
"Tony Williams" <tw@invalid.com> wrote in message
news:uLLUXloYIHA.984@TK2MSFTNGP06.phx.gbl...
>I have a query where I want to use Is Not Null in the criteria to check
>whether there is data in a text field. However when I run the query I still
>get all the records. Anyone tell me why?
> Thanks
> tony
>
If the field contains an empty string (""...
dummy alerti have this formula =SUM(E3:E11/B3:B11), i want to divide the sum of the E
column by the sum of the B column but am obviously missing something that
will seem hideously obvious when pointed out
thanks
Micayla,
Nice name.
=Sum(E3:E11)/Sum(B3:B11) would work if you are looking for a single output
of the one column sum divided by the other.
OTOH if you want to divide each row and then sum the results, you could put
=E3/B3 in the C3 cell and copy down. Then sum the numbers in Column C.
If you are doing the latter, you can do it in one formula that looks like
the one you made, but when you bu...
insert an image based on a cell valuei am trying to create a model of genetics within a spreadsheet where the
male and female genetic contribution to their offspring is displayed in a
cell either as A or a and using " &" to conbine in the offspring cell ,
chromosmes are displayed as AA,Aa or aa is it possible ....ifor this
resultant cell content to then control an image to be viewed to show the
shape of the offspring with that resultant genetic code.....eg IF cell
content =AA 2 antennae / Aa 1 antenna / and aa 0 antenna
i have a very basic drawing created but i would like to import better ,
spe...
Summing LOOKUP valuesI am pulling info from one sheet and putting it in another, via a macro
I am using the lookup funtion to get three values of data from separat
worksheets. I then put those three values on a new worksheet and woul
like to sum them.
Unfortunitely, the values do not sum. A way that works would be to do
paste-special values only, but I do not know how to do that via
marco.
Any suggestions?
Thanks,
Mik
--
Message posted from http://www.ExcelForum.com
i got the following after recording a macro (the first place to look fo
solutions):
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=x...
2nd RequestThe two tck_201_ fields are text boxes on a form that are being set by to
calculated text boxes on a different form!subform. As you can see by the
debug statements and results, the values will just not transfer. I have set
the format for both of the TCK _201_ fields to Fixed! Any Ideas?
Me.TCK_201_Done = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual
Me.TCK_201_Tot = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates
Debug.Print Me.TCK_201_Done
Debug.Print Me.TCK_201_Tot
Debug.Print Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual
Debug.Print Forms!frmPT...
Checking Date for NullI'm passing a date from a form control to a public function. What is good
technique for having the function check the date for Null? Simple example:
Calling statement (from Control Source) = MyFunction([txbDate])
Function MyFunction(dateDate as Date) As String
If IsNull(dateDate) Then
MyFunction = "something"
Else
MyFunction = "something else"
End If
End Function
This won't work, since a date variable cannot be Null. I've got it working
by declaring the date variable as Variant, but can this really be the right
answer?
Thanks,
...
Macro to add value if cell has a color :-sI don't know if this exists, but is it possible to check if a certain
cell has a color?
Normally a cell is blank, but someone asked me to see if it is
possible to check if a cell is colored..
Any suggestions?
Thanks!!!
Is this sufficient?
Sub ifcolor()
If Range("b2").Interior.ColorIndex > 0 Then MsgBox "yep"
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
<test@testnospam.nl> wrote in message
news:obht5456aq6ur5gbqmasacqke9p2g13o7i@4ax.com...
>I don't know if this exists, but is it possible to check if a cer...
Using parameter in query to specify null/not null recordsI have this query:
Select Max(SessionID) FROM Session WHERE ObjectID = [Enter CatID] And
LevelID = 2 And [EndTime] Is Null
I want to be able to use a parameter to be able to switch at will from
AND [EndTime] Is Null
to
AND [EndTime] Is NOT Null
Obviously, this is to be able to return values from only closed sessions or
from only open sessions. I haven't encountered this syntax before in my
work with Access, so I am a bit unsure as to how to phrase this.
Does anyone have any ideas?
Thanks;
Amy
You M_I_G_H_T be able to do that with the following: Enter anything at
all ...
Max of value from DataGroup2 within DataGroup1Hello -
I have a table with data as follows below.
I am trying to build a query that will give me the record with MAX of
specDiffMax [value] for each TestFreq [DataGroup 2] within each TestNum
[DataGroup1].
ID TestNum TestFreq specDiffMax
--------------------------------------------
4889683 Test 1 1710 3.669998
5123289 Test 1 1710 2.882999
4817314 Test 1 1710 3.102001
5134007 Test 1 1710.2 3.573002
4896056 Test 1 1710.2 3.355
4914480 Test 1 1710.2 3.515999
4889685 Test 2 1710.4 3.333
4896057 Test 2 1710.4 3.450001
4914481 Test 2 ...
How do I filter rows based upon a column valueI have a spreadsheet that contains multiple agency id's in a column. When
generating reports, I would like to filter per agency and display only the
rows associated with that agency.
Is there a tutorial or sample on how to do this?
Hi
It sounds like you are looking for Data / Filter / AutoFilter. Have a look
here for some basics:
http://www.contextures.com/xlautofilter01.html
--
Andy.
"Jack" <nfr@nospam.com> wrote in message
news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl...
> I have a spreadsheet that contains multiple agency id's in a column. When
> generati...
Wont show Values, just formula
Have some formulas like the following:-
=IF(B10=0," ",LOOKUP(B10,Menu!$A$1:$A$146,Menu!$B$1:$B$146))
wanted to change the lookup range, but every time i change the formula
it just displayes the formula, and not the resulting value. WHY??
I have checked the View Options and they are OK. Tried deleting an
retyping the formula. Nothing helps
Can some1 help me please
-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
Actually what you want to make sure you have on ...
returning zero for a nullI am performing a count function in a query to give me total accounts
grouped by state, region, district. Problem is some districts do not
have account #s in them and they are not on the results of the query.
I've been trying to get the results to return a zero for the null
districts, but I cannot structure it correctly. Can anyone give me
more insight as to the best way to do this and how/where I should
insert the function.
On Feb 20, 2:29=A0pm, TheReallyWhiteRunner <donald.quint...@gmail.com>
wrote:
> I am performing a count function in a query to give me total accounts
> ...
excel to send email when value manually put inHi, I would like to make a macro, i believe it is called, to send an email
when A1 is below or equal to a certain value, say 3. I have looked at the
site http://www.rondebruin.nl/
but this is complicated. I put in the text:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 200 Then
YourMacroName
End If
Sub Mail_CDO()
Dim iMsg As Object
Dim iConf As Object
' Dim Flds As Variant
Set iMsg = CreateObject("CDO....
Improperly nested XML when column contains NULL #2I'm getting improperly nested xml when a column contains null.
I modified the first row (customer ALFKI) to have a NULL Address
column.
I'm using the following XSD Annotated Schema against the NorthWind
database:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="customer" sql:relation="Customers"
sql:key-fields="CustomerID">
<xs:complexType>
<xs:sequence>
<xs:element name="name" sql:field="ContactN...
Print values dynamically on a formI have a form that needs to display values from an array on the form; how do
I achieve the following?
If item count in array is < 20 then print
Val1 Val2
Val3 Val4
Val5 Val6
and so on
If items in array is > 20 then print
Val1 Val2 Val3
Val4 Val5 Val6
Val7 Val8 Val9
and so on
The following code works fine and prints values in two columns butI need to
modified it to print in three columns if nItemsCount > 20
for i=1 to nItemsCount
If i Mod 2 Then
nX = nX * 25
nY = nY - 15
End If
myVal.Name = "myVal&...
Add Records based on ValueHere is a tricky situation that I've run into. I have a table with
about 100 records, and each record has an sku and the quantity on
hand. I want to put one record into a new table for as many pieces of
inventory I have. For example:
SKU Quantity
001 2
002 4
I need the new table to have
SKU
001
001
002
002
002
002
Any ideas?
You've described 'how' you want to do something.
If you describe a bit more about "why" you want to, and "what" you expect
you'll be able to do as a result, the newsgroup readers may be able to offer ...
excel same value in two cellsHow can 2 cells have the same values, such that when I edit either cell the
change is reflected on both?
It is not possible without using some sophisticated features of event
listening and macros. But one should ask the question: what would be the
benefits? You can refer the second cell from the first one -- something like
A2: =A1, so A1 and A2 will both have the same value and each time you change
A1, A2 will change.
pls help wrote:
>How can 2 cells have the same values, such that when I edit either cell the
>change is reflected on both?
This macro will do as you describe............
chart x-axis values appear as a seriesThe x-axis values do not appear on the x-axis but appear as another series on
the chart. How do I specify the x-axis values for a chart?
Existing chart:
Chart menu > Source Data > Series tab.
For a new chart, fix the data first. Put the X values in the first column
and Y values in the columns to the right. Put a label (series name) in the
row above the first row of data for each set of Y values, but keep the cell
atop the X values blank. Select the entire range, including X values, series
names, and blank cell, then start the chart wizard. The blank cell helps
Excel interpret t...
Search and display based on cell valueOn page one. I have various full names listed throughout the worksheet
in various places.
When a user clicks on a cell with a name in it, I want them to be
directed to sheet 2 where the same name (cell values) is found in
column A. (There will be profile info starting with their name in
column A.)
I think the following may do what you are looking for. Right click the tab
at the bottom of Sheet1 (which is what I assumed you meant by "page one"),
select View Code from the popup menu that appears and then copy/paste the
following into the code window that appeared...
...
Pie Chart not to display 0 ValuesGood day,
I have an excel Table something like below.
Column A Column B
Item 1 24%
Item 2 13%
Item 3 0%
Item 4 3%
Item 5 0%
The Values in Column B are being automatically updated from another table.
There are 24 Items in the table and usually about 10 of them are 0, in a
given period of time.
Is there any way that the chart can be intelligent enough to skip the 0
Values and display only values greater than 0.
This would clear the clutter on the chart.
I am using a pie chart for the purpose.
Thanks for Help
_________________
Best Regards
Khawar
Andy Pope has instructions on his web sit...
Excel macro to insert rows if a cell does not equal the value above that cellI have a spreadsheet listing columns of information for many different
people. If more than one row exists for the same person, I'd like to
insert 2 rows after the row so that the group is separated from the
next group. For example, here's how the data appears now:
LastName Account# Balance
Martin 1 500.00
Martin 2 750.00
Smith 5 100.00
Thomas 9 900.00
Here's what I'd like it to look like after running the macro:
LastName Account# Balance
Martin 1 500.00
Martin 2 75...