2nd Posting Select Value In Query

My data are as follow

Myvalue   value1   value2   value3
100           20         50          70
200           null        100       210

my goal is to divide myvalue with either value 1,2 or 3 whichever is higher

how to put the expression in query
0
Utf
2/4/2010 1:54:01 PM
access 16762 articles. 2 followers. Follow

3 Replies
514 Views

Similar Articles

[PageSpeed] 34

Zyus -

Here is what will go in the SELECT field of your query:

=Iif(nz(value1,0)>nz(value2), 
Iif(nz(value1,0)>nz(value3,0),Myvalue/value1,Myvalue/value3), 
Iif(nz(value2,0)>nz(value3,0),Myvalue/value2,Myvalue/value3))

If value1 and value2 and value3 could all be null or zero, then you must 
test for that (I am assuming they cannot be negative numbers):

=Iif(nz(value1,0) + nz(value2,0) + nz(value3,0) = 
0,0,Iif(nz(value1,0)>nz(value2), 
Iif(nz(value1,0)>nz(value3,0),Myvalue/value1,Myvalue/value3), 
Iif(nz(value2,0)>nz(value3,0),Myvalue/value2,Myvalue/value3)))
-- 
Daryl S


"zyus" wrote:

> My data are as follow
> 
> Myvalue   value1   value2   value3
> 100           20         50          70
> 200           null        100       210
> 
> my goal is to divide myvalue with either value 1,2 or 3 whichever is higher
> 
> how to put the expression in query
0
Utf
2/4/2010 2:41:03 PM
On Thu, 4 Feb 2010 05:54:01 -0800, zyus <zyus@discussions.microsoft.com>
wrote:

>My data are as follow
>
>Myvalue   value1   value2   value3
>100           20         50          70
>200           null        100       210
>
>my goal is to divide myvalue with either value 1,2 or 3 whichever is higher
>
>how to put the expression in query

You're being REALLY RUDE, zyus. We're all unpaid volunteers here, donating our
time, and costing you *nothing*.

Posting a "2nd posting" less than 30 minutes after your first one, before 6 in
the morning at that, is simply unreasonable.

I see you've gotten two good answers. I hope the service meets your
expectations; if not, I'll see that your fee is promptly refunded.
-- 

             John W. Vinson [MVP]
0
John
2/4/2010 4:47:53 PM
Dear John,

Sorry if my posting offended you. For your info , i can't even see my first 
posting in the news group after i confirmed it and i thought it was not 
successful after few attempt of retrieving. That's the reason why i put 2nd 
posting in my header hoping that other group of member will not be confused

I've been with this group for sometimes and i never expect a quick & good 
service from the member as i knew it's a voluntarily service.

I really appreciate those who responded to my posting professionally 
including yourself which has helped me to improve my knowledge in access.

Regards

zyus

5/2/2010 3.15pm

"John W. Vinson" wrote:

> On Thu, 4 Feb 2010 05:54:01 -0800, zyus <zyus@discussions.microsoft.com>
> wrote:
> 
> >My data are as follow
> >
> >Myvalue   value1   value2   value3
> >100           20         50          70
> >200           null        100       210
> >
> >my goal is to divide myvalue with either value 1,2 or 3 whichever is higher
> >
> >how to put the expression in query
> 
> You're being REALLY RUDE, zyus. We're all unpaid volunteers here, donating our
> time, and costing you *nothing*.
> 
> Posting a "2nd posting" less than 30 minutes after your first one, before 6 in
> the morning at that, is simply unreasonable.
> 
> I see you've gotten two good answers. I hope the service meets your
> expectations; if not, I'll see that your fee is promptly refunded.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
2/5/2010 7:15:01 AM
Reply:

Similar Artilces:

How to increase the value of the cell by some percent
I am trying to increase the value of cells by certain percentage. example i have a worksheet with some data in cells C6 to F20 with different numbers. i want to increase all the cells by 5% , is it possible?. Thanks in advance. -- Deepwater ------------------------------------------------------------------------ Deepwater's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22231 View this thread: http://www.excelforum.com/showthread.php?threadid=467826 Suppose Column A has the values. Set Column B = A*(1+Percent) Find a cell somewhere, name it Percent. If y...

Cannot post time through Business Portal
When I go to Project>Project time in business portal, I get the message 'You are not authorized to view this page. Contact your Great Plains administrator for assistance.' This is my local demo copy. I have given myself sa permissions. I am the administrator on my machine. Your Business Portal administrator must add the Project Timesheet Entry role to your user account within Business Portal to allow you to view this page. Remember that access to areas within Business Portal is defined by the roles that a user is assigned to. BP keeps track of a user through your domain...

How to make an http Post with XML and retrieve the response?
I simply want to post to a URL via HTTP Post then get the response Text of that. I have this done in classic asp like this: postdata = "param="&server.urlencode(param) Set objXML = Server.CreateObject("MSXML2.ServerXMLHTTP.3.0") objXML.open "POST", "http://somesite.com/cgi-bin/output.cgi", False objXML.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" objXML.send postdata strXML1 = objXML.responseText Set mydoc=Server.CreateObject("Microsoft.XMLDOM") mydoc.async = False mydoc.loadXML(strXML1) ...

query problem 12-01-09
I've got a table of Organizations and a table of Memberships. Memberships have an OrgID and one Org can be part of many Memberships. Memberships have an expiration date. I'm trying to build a list of Organizations that have at least one non- expired Membership associated with it. I'd like each current Org to show once in a list (for an unbound combo box). I tried: SELECT tblOrganizations.OrganizationID, tblOrganizations.Name, [tblMemberships.Anniversary]>(Date()-60) AS [Current] FROM tblOrganizations INNER JOIN tblMemberships ON tblOrganizations.OrganizationID ...

out of balance posting
I am haveing a problem with an out of balance batch when posting directly to QB. It only happens when I have recieved a payment on account at the POS. After looking at the batch I see tht there is an entry for the amount posting to Acct Rec. but no balancing entry to Undeposited funds. In the acct assignment journal I have the paid to and paid from accts set up and RMS says it will make a corresponding entry for each when needed but it only makes the one entry in the batch. Any batch posting without an acc. rec. payment posts without issue. ...

Global Address Book query
After being thrown in at the deep end..........I am having difficulty adding certain email contacts into the global address book, basically it won't let me do anything !!! I suppose I could create a public address book and do it that way- but am curious, is there a solution ? Dachwa. ...

Test to post
I sent a question and it didn't post. Trying a test. Rick Rick, http://www.mvps.org/dmcritchie/excel/xlnews.htm John "Rick" <ricky.sather@si-intl.com> wrote in message news:7c55e5aa.0401280809.7c6c758e@posting.google.com... > I sent a question and it didn't post. Trying a test. > > Rick ...

added 2nd domain in default policy, now how add 2nd user in outloo
I've added the 2nd domain in default policy, now how do I get outlook to use the 2nd domain? I want the user to be able to choose which company she is responding from. I've tried setting up a 2nd profile but it says email address already exists.. On Fri, 26 Jan 2007 13:26:01 -0800, E2 <E2@discussions.microsoft.com> wrote: >I've added the 2nd domain in default policy, now how do I get outlook to use >the 2nd domain? >I want the user to be able to choose which company she is responding from. >I've tried setting up a 2nd profile but it says email address al...

catergory on the X axis; values on the Y axis
does anyone know how to display a category on the X axis and the values on the Y axis? You can use a line or a column chart. If you want something else, please come back and ask for more assistance. "100green" <100green@discussions.microsoft.com> wrote in message news:73786BA3-C621-4AB9-955F-5F1626C9F591@microsoft.com... > does anyone know how to display a category on the X axis and the values on > the Y axis? ...

selecting several items from a drop down list
Ok, i have my drop-down list but what if I want to select several item from it? Anyone know how -- Message posted from http://www.ExcelForum.com There are a couple of answers to your later posting of this question. robparkes < wrote: > Ok, i have my drop-down list but what if I want to select several items > from it? > > Anyone know how? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

How to exclude conditional calculated no or zero values from a cha
Picture this: a chart for 30 days and 30 values. The values come from a different table (sheet1!A:G) using the following formula =IF(VLOOKUP(J11;Sheet1!A:G;2;FALSE)="";"";VLOOKUP(J11;Sheet1!A:G;2;FALSE)). If the table on sheet1 has no value (""), then the a.m. formula also displays no value (""). The chart however does show a zero value, which i do not need. Is there a possibility to not show this zero or no value in a chart? Thanks for your help! Hi, Use the formula NA() instead of "". Here are a couple of pages on the subject. http...

Additional Query
I am trying to restrict an integration to only one document or range of documents. I have created an ODBC connection to the database that is returning all data in the tables I have selected. I am using a before integration query of: dBeginPO = InputBox("Please enter beginning Purchase Order number") dEndPO=InputBox("Please enter ending Purchase Order Number") SetVariable "dBeginPO",dBeginPO SetVariable "dEndPO", dEndPO On my datasource I have the following: dBeginPO = GetVariable("dBeginPO") dEndPO = GetVariable("dEndPO") Quer...

Disable selection on CListCtrl.
Hi all, I need a help on the CListCtrl dialog box. I would like to disable the selection the CListCtrl dialog box but do not know how to do it on VC++ 6. I hope that someone can give the simple coding. Thanks and have a good weekend. -HS Phuah By "disable the selection" do you mean deselect an item in the control, or do you want to disable the control itself? "Hon Seng Phuah" <hsphuah@usa.com> wrote in message news:3898598f.0308150814.615b4302@posting.google.com... > Hi all, > > I need a help on the CListCtrl dialog box. I would like to disable the > s...

How do i hide cell if the value is zero?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, so i have a simple time log in which the end column is to the right the start column, and the start column is filled down such that it equals the end time from the row above. My question is: how do i make it so that if there is no end time in the above row, the start column will not list a bunch of &quot;0:00&quot;'s? Thanks in advance! On 4/7/10 2:23 PM, Steppenwolf@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Hi, so i have...

Select Members in a DL in OL 2000.
Hi, I am new to OL 2000 and DL. I am using OL 2000 with XP SP2. I have a couple of questions: 1. I have created several DLs and each one of them consists of 50. However my mail server is not big enough to send all 50 in one email. Is there a way I can select only 15 of the 50 in the DL to send out? If so, how please. 2. Is there a way I can create another DL from an existing DL (in reference to the # 1) as I need to reduce the number of members from 50 to 15. Or do I have to manually create another DL? 3. I have accidentally deleted some members from a certain DL. I did backup my .pst ...

Need Post-SP2 Hotfix for OL2002
I am having problems sending emails with large attachments (which I need to be able to do) which are described in KB articles 827149 and 827349. The articles go on to say I need to apply a hotfix, but this is nowhere to be found, and I have spent many hours on the phone with Dell tech support, and they can't get me the hotfix either. Can anyone out there please tell me where to download this hotfix, or email it to me? Any assistance is greatly appreciated. I have tried everything to no avail. PG You need to contact Microsoft Product Support Services. Odds are you won't be c...

Finding the corresponding value in a range?
Hi, I appreciate if somebody can help me with a formula. In one spread sheet I have a list of products and their weights. In that same sheet I want to create a formula that brings up the corresponding applicable freight $ based on the range in which the weight value falls in the table below. i.e. if the weight is 129 lbs then it should bring up a freight cost of $227 if I am using the Bronze plan. Additionally IF the weight is over 1000, THEN divide the weight by 100 and multiply by the per pound $amount of the selected plan. Weights and Corresponding Freight rates ...

Link a Query
Hello, I want to link a query from a seperate database into my database. I found how to link a table, but can't find any way to link queries. Can this be done? Thank you On Wed, 19 Mar 2008 08:38:01 -0700, Nightshade <Nightshade@discussions.microsoft.com> wrote: >Hello, > >I want to link a query from a seperate database into my database. I found >how to link a table, but can't find any way to link queries. Can this be >done? > >Thank you Not to my knowledge. You can use File... Get External Data... Import to import the query, or copy and paste th...

How to post a sales transaction in RMS ?
Hi everyone, I'm trying to post a sale using the classes contained in the QSRules.dll, however I can't get this. How can I do this operation?. Thanks..! -- Miguel Ortiz Falcón ...

How to make catalog selection relink DLL
I added a driver to my catalog in CE 6.0 R2, with radio buttons for different platforms. Based on the platform selected, I have different SYSGEN variables that cause the inclusion of the right library to link for this driver DLL. I found I have to go to the Solution view and manually build the components whenever I make a change to the source code. If you have an idea for automating the build of these components, that would be cool. But the main frustration I have right now is that I wasted several hours last week when I switched from one catalog option to another, and chose ...

installation of 2nd DHCP server
Hi, We have a windows 2003 DHCP server. I am trying to activate another DHCP server (on windows 2003). So that we have to server handling out IP's - what issues should I be aware of, apart from configuring different scopes? Can I preform this during normal working hours Thanks Hello Vishal, I assume you have used the name "biren" in a posting from 18:38 from today so please check the previous posting about. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ** Please do NOT...

Default value in Lookup field (pricelist)
I know it's possible to set a default value for a lookup field. I know it has to be done with Javascript and I know I have to use the OnLoad even of the concerning form. But I can't find the Javascript-code I have to use... I found this: var lookupItem = new Array; lookupItem = crmForm.all.pricelevelid.DataValue(); // If there is data in the field, show it in a series of alerts if (lookupItem[0] != null) { // The text value of the lookup alert(lookupItem[0].name); // The entity type name alert(lookupItem[0].typename); // The GUID of the lookup alert(lookupIte...

Sum column based upon corresponding values
I know this is probably simple but I can't get anything to work. I have a formula that I took from another document to use in a new project. The formula looks at the values of a specific range in another workbook and then counts the values of a corresponding column. However, I want to change this formula so that it sums the values instead of counting. =SUMPRODUCT((QM03_template.xls!$A$2:$A$2000={"FEMA Manual Determination"})*(QM03_template.xls!$E$2:$E$2000<>"")) What column to SUM, I will assume F =SUMPRODUCT((QM03_template.xls!$A$2:$A$2000={"FEMA Manual...

Query not totaling correctly
I have a totals query that is supposed to sum the balance due for all receipts from any given customer. The field type is currency. Negative balance due amounts are stored as ($12.00) for example as opposed to positive balance due amounts which are stored as $12.00. For example Customer 1 has a balance due on receipt A of $12.00 and they did not pay it when the receipt was generated. Then they later paid that balance - tracked with a different receipt - receipt B. The second receipt then shows a negative ($12.00) balance since that receipt had no balance due (was only a payment on ...

Datagridview selected rows
I want to get the first selected row of DataGridView Selected Rows. The goal is that I would hold down CTRL or SHIFT and select multiple rows, then I could click a button or menuitem and update all selected rows with a value from the first selected row. For example in crystal reports you hold down shift and click multiple control and select Same Width and it resizes all to Width of the first one you clicked. Datagridview seems to works if you the select in order going down the grid, but from bottom to top its different. If I could find out the direction, then i could take the first ...