please help with this query

Ost  Ocity   Dstate  Dcity  Carrier  Price  Rank  Diff
A      B          C         D       X        1200    1       100
A      B          C         D      Y        1300    2        100
A      B         C          D       Z        1350    3        100
A     B           C        D        W       1789    4        100
A1    B1         C1      D1      X1          785   1
A1    B1         C1      D1      Y1          789   2
The rank for every carrier  is based on the price .  If  rank1
carrier
is not a pariticular carrier(say if  it is not X1 or
Y1  or Z1), then i want to calculate the difference between  Price
corresponding to rank1 and price corresponding to rank2. and fill the
Diff column with the value. I want a query which can create that
difference coln ( last col) as  in the above example.  The first four
rows is one lane and the last two rows is another lane. For the
second set of lane, the diff col will be empty because tthe carrier is
X1.
Can somebody  please help me in writing this query.

Thanks in advance
0
subs
2/19/2008 6:13:14 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
526 Views

Similar Articles

[PageSpeed] 9

On Tue, 19 Feb 2008 10:13:14 -0800 (PST), subs <subbu1678@gmail.com> wrote:

>Ost  Ocity   Dstate  Dcity  Carrier  Price  Rank  Diff
>A      B          C         D       X        1200    1       100
>A      B          C         D      Y        1300    2        100
>A      B         C          D       Z        1350    3        100
>A     B           C        D        W       1789    4        100
>A1    B1         C1      D1      X1          785   1
>A1    B1         C1      D1      Y1          789   2
>The rank for every carrier  is based on the price .  If  rank1
>carrier
>is not a pariticular carrier(say if  it is not X1 or
>Y1  or Z1), then i want to calculate the difference between  Price
>corresponding to rank1 and price corresponding to rank2. and fill the
>Diff column with the value. I want a query which can create that
>difference coln ( last col) as  in the above example.  The first four
>rows is one lane and the last two rows is another lane. For the
>second set of lane, the diff col will be empty because tthe carrier is
>X1.
>Can somebody  please help me in writing this query.
>
>Thanks in advance

Subbu, I think the reason nobody has posted a response is that your question
is very difficult to understand. You know what a "lane" is, but we don't. You
know how rank is determined, we don't. You understand how 1789 and 1350 can
produce a Diff of 100, but we don't. You know the special meaning of X1 but we
don't.  Could you explain a bit more about the nature of your data and the
calculations??
-- 
             John W. Vinson [MVP]
0
John
2/19/2008 9:25:06 PM
Reply:

Similar Artilces:

Address Labels
The situation: I work for a small business which has a large number of Excel wookboo files, each of which holds the details of a customer. Part of these details are - obviously - the name and address listin for that customer, with the name and each line of the address bein listed in seperate cells. The problem: The owner of the business has asked that I build up an Excel databas of every 2004-2005 customer (well over a thousand), which lists th name and address of each, in such a way that it can be printed directl onto sticky labels if necessary. Needless to say, trying to do this manua...

Carlos need help
setting up, just email me Using the same news.eternal.etc... "Kue2" <Kue2@hotmail.com> escribi� en el mensaje de noticias:i0fui7$69b$1@news.eternal-september.org... > setting up, just email me ...

Help with creating invoice in other currency
I am looking to set up multi-currency invoicing so that I can create an invoice in Great Plains 7.50g3 in a currency (British Pounds) other than the functional currency (US Dollars). I believe I have multi-currency access set up properly, have currencies and exchange rates set up, have accounts set up to receive postings in multiple currencies, and the customer set up with a default currency of British Pounds. However, when I create an invoice, all the financial values are in US Dollars, and the Currency ID field is disabled. What is causing the Currency ID field to remain disabled? Th...

Protect sheet help
I have a worksheet was setup to allow users to edit certain ranges when worksheet is protected. I deleted one of the ranges which users should not edit it anymore from the “Allow Users Edit Ranges…” window; however, the deleted range was editable even when the worksheet was protected. Just to experiment, I deleted all ranges from the “Allow Users Edit Ranges…” window, therefore, users should not be able to edit the entire worksheet when the worksheet is protected, and however, I was able to edit all ranges that were pervious setup to allow users to edit. Any ideas? Check that the cells are...

Help! #11
When I print my contact list from outlook it says my name on top of each contact, which is ok. What I wanted to do was change the name so it said my company name. I cannot seem to find where the "profile name" is stored so that I can change it. I tried changing the email name, but that's not it. Email me please, as to I don't regulary come on here to post. Thanks in advance. TGS ...

Help
Out of the blue today when I try to run Excel for Office97, I got "Required file MSO97.dll missing". So I looked at the PATH and copied MSO97.dll to a place in the path - "C:\windows\". Now I get the error message "Not enough memory to run Microsoft Excel". I have plenty of memory (almost 200MB). Word still works fine...but Access I get the same message. Previously Excel (and other Office programs) have run successfully. The only change is that recently i upgraded my Norton system works from version 2002 to 2004. My OS is windows 98. Of course I don'...

Chart Help -How ?
Hi, I'm a newbie with Excel & I am struggling to make this work. It's work related. I'm simply trying to show sales figures in a Pie-chart based upon the following information: Alex Frankie w/c 13/3/06 Calls made 45 70 No. not interested 10 10 No. interested 1 8 No. of call backs 34 52 Appointments made 0 1 But I can't work out how I'm going to show the legends but also including the 2 staff members. I'm sure somebody out there will have a better resolution. Thanks -- conks ----------------------------------------------------------------------...

New to RMS: Having trouble setting up 2nd terminal.....HELP
We have an existing customer who has 1 server and 1 POS terminal. I have recently installed a 2nd terminal and am trying to match the settings and templates of the register 1 to the new register 2. The problem is with the on-screen keyboard. I've chosen the option to use the touchscreen keyboard in manager for register 2 and picked the template for the pre-configured keyboard layout; however, register 2 just refuses to display the template like register 1 does. I know ive got to be missing something. By the way, i forgot to mention that yes, i have enabled the touchscreen keyboard ...

Public folder replication help!!
how can i replicate on epublic folder structure to another on CURRENT Exchange server (not between some) thank U. I'm not sure what you're asking. Are you wanting to add a second public folder hierarchy on the same server and replicate your PFs between hierarchies on the same server? missy "Topper" <alexander.rodenkov@bix.ru> wrote in message news:uped6nP5DHA.2720@TK2MSFTNGP09.phx.gbl... > how can i replicate on epublic folder structure to another on CURRENT > Exchange server (not between some) > thank U. > > "Topper" <alexander.roden...

help on adding a commandbar using VB com addin
Adding Commandbar to the New Mail message Inspector Window I was trying to add a new tool bar and also customize my new toolbar t automatically contain some commands programmatically. I wanna write COM add-in to OutLook that adds a new toolbar to the OutLook' Toolbar. This tool bar should apear only in new mail window. There are two commands(buttons) by name "Digitally sign Message" an "Encrypt Message Contents and Attachments". These are present in the Tools -> Customize -> Commands -> I categories select Standard -> on the Right side, if you scroll do...

a count field in a query?
Hello, I have a query in which I would like to create a field which increments by 1 for each record selected, so if there are 10 records selected by the query, this column would show numbers 1 to 10. Ideally I would like to have some text in front of each number, say invoice1, invoice2 etc. From a previous posting I realise that this is frowned upon but it would be the simplest solution for my problem! How can I do this? Thanks Geoff On Mon, 03 Dec 2007 08:54:00 +0000, Geoff Cox <<>> wrote: >Hello, > >I have a query in which I would like to create a field which >...

Formula help! #2
hi all can someone help me where i have a row of numbers that go 0, 0, 0, 0, 0, 5, 10, 5 etc... from cells D2:K2 i want a formula to put in A2 that returns the first non- zero number, ie. it would return 5. it would also be useful to have a formula that returns which column the first non-zero number occurs in. any ideas? thanks JohnQ one way: First non-zero (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =INDEX(rng,MATCH(TRUE,rng<>0,FALSE)) where rng is your range reference (e.g., D2:K2). Column the first-non-zero occurs in (also array-entered): =LEFT(ADDRESS(1,3+MA...

Online Publisher Help
Since the Office button does not appear in the Publisher 2007 interface, someone needs to change your "Save" file information in your online help for the Publisher SAVE function. I found it by trying to change the default Publisher file directory/folder, which apparently cannot be done (why was this removed for 2007 from earlier versions? It doesn't make sense since it save time!!!) ---------------- 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" button in ...

CRM customization query
Hi, I have a customization query, I want to see the number of records available before I am getting the data for reports. I need this because, I want to generate a report by applying some filter criteria but at the same time I want the resultant record number below a specific limit. On the advance find, after I am applying the filters we get hte resultant data by clicking on the Find button. This takes us to the page where we can see the data. Now I want to add a new button on the Advance find screen on click of which I can get the record count of the result set for the filter i have ...

Parameter query in Access 2003 report
I want to create a parameter query that asks the user to select one, many, or all values from a field when opening a report. I would like the selection to be available from a list, radio box, or pull down menu to minimize spelling errors, etc. Unfortunately the list is of names which number about 100. Can this be done? If so, could someone please point me to the help pages that describe this process? My solution seems clunky but it is the only way I can see how to do it. Any suggestions as to how else might I best approach this? Thanks in advance Debbie See my sample database that shows ...

Help needed on date()
Can anybody please tell me if, and how, I can use the date() funtion in excel for dates lying before 1900. The problem is that I'm trying to fill up columns with dates from a 19th century journal. thanks in advance D. Gerling Take a look at the following: http://j-walk.com/ss/excel/files/xdate.htm MRO "Delano Gerling" <delano@gerling.demon.nl> wrote in message news:vm99477u8k7mdc@corp.supernews.com... > Can anybody please tell me if, and how, I can use the date() funtion in > excel for dates lying before 1900. > > The problem is that I'm trying to f...

Urgent help request: How to find Journal Entry of Inventory Adjust
Hi folks, I have a urgent help request and question. How do I find out the Journal Entry number for an Inventory Adjustment transaction that did not post to G/L? You know when you post an Inventory Adjustment Batch, if you don't check the box "Post to General Ledger" the inventory adjustment will not post in G/L. So how do we find out what the Journal Entry number should have been? Here's the catch, don't have access to SQL Server, so we need a way to find out within GP. Is this possible? Please help...THANKS It sounds like you are trying to find out whether an in...

including dblink in a query to retrieve data
I connect to a table using a db link i want to execute an spl query and populate results in to a pivo table. My MS Query does not allow me to use a dblink and connect to th table. Please help -- Message posted from http://www.ExcelForum.com ...

Help with Exchange component of SBS2003
I have posted this to the Small Business Server forum and have not found any help, I thought I would try here... Okay, here is the problem....during an upgrade from SBS 2000 to SBS 2003, I experienced some sort of glitch that rendered the boot partition inaccessible. Since the partition could not be recovered through any method I tried, I decided to go ahead and do a clean install of SBS 2003 on that partition. SBS 2003 installed fine this time. Now the problem is the Exchange stores that were on the SBS 2000 server....the store database is still intact (I had it on a different pa...

Database degrag query
Hi , I am using exchange 2003 with SP1. I have calculated the total size of mailboxes , it is total 30 GB. But the size if edb file is 50 GB and stm file is 25 GB . I have defraged the database offline so that it will purge the white space if there is any. but after defrag no difference in size of the database. it is still 50gb edb and 25 gb stm file. can anuone explain if i have only 30gb total mailbox size then why it is taking so much space. there is no rules and othere things define. Waiting for your help. Regards, Abhi. On 25 Oct 2004 20:45:43 -0700, pandeyabhishek2000@yahoo.com (abhi...

Union Query with division
Hello, it would like to join two tables, Table1 and Table2, however when joining table2 the items listed in that table need to be divided by 2. I normally do this division in a query then join that query to the union query with table1. I wonder if I can accomplish the same thing directly from the union query only. Also, is there a way to rename a customer ID with the same query? (See Customer 9 as example, changing to Customer 15) Example: Table1: [Customer] [Hours] Customer 1 10 Customer 3 5 Table2: [Customer] [Hours] Customer 9 ...

Font and size help
In Excel 2007, I am updating a worksheet developed by someone else. I can't get a column of email addresses to display times new roman, 12. I have reset the page default, I have formatted just the column containing the email addresses and nothing seems to work. After entering an email address, it appears as ms sans serif, 10 with an underline (ie link address) I then change the individual cell to times ne roman, 12 and it is properly retained. Help appreciated. thanks Hi. This is due to your autocorrect options in Excel. To stop Excel taking over your formatting try the following:...

"ghost or blank" messages with date field of "none".....help?
using the ESEUTIL utility, we recently had to rebuild our message store because of a corrupted restore attempt. everything restored nicely...execpt now we have some users that have what seems to be blank messages. when outlook is opened, and messages are listed in the inbox or deleted items folder, these messages are blank except for the word "none" in the date field. they can not be accessed nor deleted. any ideas on how to resolve this issue?? thanks! On 26 Sep 2006 12:07:04 -0700, "kamor56" <kmorgan56@gmail.com> wrote: >using the ESEUTIL utility, we recentl...

Save query result in a variable
hi! hope you can help me AGAIN! i have a form that let's the user pick a name, job, between dates, tax(with or without) and percentage. this is for calculating the comision each employee has earned. i did this by a query that gets the info from the form adn then opens a report and in the report i make the calculations. but it turns out that several employees have 2 or more "jobs" and for each one diferent % of comission. the form and the reports handdle it fine, i'm looking for a way to add all of this reports for each person. i was thinking of saving the sum of each que...

AfxMessageBox not opening correct help topic?
In my application just after I call OpenDocumentFile on a doc template to instantiate a new doc/view frame window, I call AfxMessageBox under certain conditions. I add the MB_HELP style flag and pass an id for the help context. It seems however that when I hit the Help button, my application's help is opened to the section for the newly-created frame window and not the section I specified as the parameter in AfxMessageBox. I can't seem to figure out why. I put breakpoints all over the MFC code relating to help and none of them hit. In CWinApp::ShowAppMessageBox, either the owner frame&...