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
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
Can somebody  please help me in writing this query.

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

1 Replies

Similar Articles

[PageSpeed] 34

On Tue, 19 Feb 2008 10:13:14 -0800 (PST), subs <> 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
>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
>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
             John W. Vinson [MVP]
2/19/2008 9:25:06 PM

Similar Artilces:

Help I think I lost most of my records
I ran a filter, changed one field in one of the filtered records and clicked the save, thinking I was saving the change to that record. The filter icon went off when it saved and now my 1000+ records are only 17. What do I do??? If i understand your question correctly, then all it requires is that you go up to the top of your window there and click on the "remove filter" button. So far as i know, a filter never removes records, it only hides them. a Query removes or changes which records actually exist in a table. "Rondo" wrote: > I ran a filter, changed one...

Beginner needs help to make chart or graph or pie
I only know a *little* about Excel, and a *little* about presenting data on some kind of chart. I don't know how to do formulas or anything but a basic spreadsheet in Excel. My Project: I need to show that my Natural Gas usage spiked during a gas leak involving multiple pipes leaking small amounts at the same time; the leak wasn't noticed for 4 to 6 months. I have 3 years of gas bills-- 1 year before the leak, the year involving the leak, and 1 year after the leaks were noticed AND fixed. I'd like a visual rendering to make it easy for a 4th grader (i.e. my landlord - LO...

Problem when converting to UNICODE, please help
Hello, I didn't think it would be so much pain to convert to UNICODE. The problems I have so far are: 1. I can't use atof() to convert a string to float number. Someone said to use _tstof() (wtof()). But my version of VS6 doesn't have that (not in my MSDN either - July2001). Someone must have done unicode at that time. What was the solution then? 2. It is so odd that I can't use _tcsncpy() or lstrcpyn() to copy 2 strings, eg. CHARFORMAT cfThis; LPCTSTR sFaceName;// from calling function _tcsncpy(cfThis.szFaceName, sFaceName, LF_FACESIZE); The message is {cannot conv...

Help needed changing date formats from m/d/y to d/m/y
I have a spreadsheet with a column of dates in the format ddd m/d/yyyy, e.g. "Mon 9/29/2003". The data has come in as text as I have cut and pasted it from another source. I sort of have something working, but it only works for dates with 2 digits (i.e. from October (10th month) on and from the 10th of each month on), e.g. "Wed 29/10/2003". The formula I have is: =DATE(RIGHT(E3,4),MID(E3,6,2),MID(E3,9,2)) Can anyone help me get this to work for all dates? Thanks in advance. Craig An alternative: Select your column. Choose Data/Text To Columns. Select the Delimited ...

calculated total in query
I have 2 fields in query. Qty1 and Qty 2. I added Total:qty1+qty2. However, if qty1 has some number but qty2 is blank, my total is blank, instead of just show the sum. In Access You need to use the NZ function to force a ZERO. Or use an IIF clause. Total: Nz(Qty1,0) + Nz(Qty2,0) or Total: IIF(Qty1 is Null,0,Qty1) + IIF(Qty2 is Null,0,Qty2) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Song wrote: > I have 2 fields in query. Qty1 and Qty 2. I added Total:qty1+qty2. > However, if qty1 has some number b...

SQL help!
Can somebody tell me how to custom my price list report in SO and HQ so that i can turn on the Re-order number column? Its the re-order number that is in the supplier tab that i need to be availalbe on my reports. Thanks v much! Hi Philip, it is possible but make sure the following: ONe item attached to one supplier else report will repeat the same item twice/trice if the same item is purchase from mulitple supplier and you different kind of re-order no. However if one item is attached to one supplier then following query you can add in Items - Item Price List.qrp file to accompl...

help determining vba algorithm
Hi I have a problem at work I thought would be nice to solve with a vba macro, unfortunately I'm not too familiar with Outlook's object model and so forth so I'm wondering what is the proper way to solve this problem, the problem is as follows: we have particular rules about archiving emails, these rules require that we print them out in order with the last email on a subject received printed first, only emails that I have acted on are required to be archived - essentially meaning emails I have replied to. Another thing is if an email has all the content of an earlier email in it,...

HELP! Information Stores Will Not Mount
We had an issue today where the server locked up for no known reason. After reboot, Exchange 2003 would not start up. We're getting the following errors in the Event Log: Event Type: Error Event Source: MSExchangeIS Event Category: General Event ID: 9518 Date: 6/7/2005 Time: 5:56:43 PM User: N/A Computer: CRC2K3 Description: Error Log file is corrupt starting Storage Group /DC=local/DC=cr/CN=Configuration/CN=Services/CN=Microsoft Exchange/CN=CityRealtyMail/CN=Administrative Groups/CN=First Administrative Group/CN=Servers/CN=CRC2K3/CN=InformationStore/CN=First Storage Group on the Mic...

Field Level Help/Notes
I'd like to create some field-level help (notes) for my users. For example, I have created a picklist with several priority values (Business Critical, Urgent, Important, Low, Not Applicable), and would like to clearly define what each of these means. Any suggestions on how this can be accomplished? ------=_NextPart_0001_7724D8DB Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Cindy, Thanks for the post. And thanks for not giving up on the newsgroups! Neither the CRM customization nor the SDK customization can do field level "Tool Tips" They are available...

DGView Binding (Really need help on this one, please!)
I've posted this issue a couple of days ago, but I wasn't clear, so I'm trying again. I want to be able to bind a collection of "Rows" to a DataGridView para the public fields aren't fixed. If each "row" was made out of fixed public fields I know I could easily achieve this using BindingList. My class structure is as follows: I have a Field that exposes Name and Value attributes. Now, I also have a class Fields which is basically a collection of class Field as you can see: Fields : KeyedCollection<string, Field>. So far everything ...

Help! Can MONEY Import a MSN online Deluxe Portfolio Backup?
Is there a MONEY version that could import a web based Deluxe MSN Portfolio Backup file? Microsoft is going to Kill thir great portfoloio manager, and many of us have tax lot data stored there, and so far this data is lost in the nep beta programs. I have Money 2002 and I just loaded a moneycentral .mny and a moneycentral ..mbf backup into it with no problem. I have seen that message also and they are crazy. I have been using moneycentral for too many years to remember how far back I started with it. At least early 1990s. During the years I have compared it to many other stock portf...

Query On Pulling Reports
This weekend, I migrated a Store Operations (v1.1) from NT to a new machine running Small Business Server 2003. The database backup and restore went smoothly, and so far I have not had any major issues. An upgrade to v1.2 is scheduled for next weekend ... I just wanted to get it up and running first. One thing that has cropped up is pulling reports ... ie, reviewing and printing Purchase Orders while logged on under Store Manager on a workstation machine. I can pull the reports up fine while logged directly on the server ... the data is there. However, when you are logged onto a wo...

Still in need of help
I posted this prior with no final answer to my question. I'm looking for a formula that can help sort calendar from fiscal year. I'm hoping this is enough info. If not please let me know. mePenny HEADINGS ONLY: A4 - DATE B4 - TYPE OF FUEL (U = UNLEADED, D = DIESEL) C4 - GALLON AMOUNT OF FUEL DELIVERED D4 - PRICE PER GALLON AT TIME OF DELIVERY ENTRIES ONLY: A6 - A24 ARE THE ENTRIES OF FUEL DELIVERED G5 - CALENDAR YEAR (HEADING ONLY) G6 - (WORD ONLY) UNLEADED H6 - (NEED FORMULA HERE) G7 - (WORD ONLY) DIESEL H7 - (NEED FORUMLA HERE) G9 - FISCAL YEAR ...

Help with pie chart
I want to see if there is a way to create a pie or other chart that shows the percentage of total yes that come from each individual with the below data table... Thanks in advance for your assistance... # of Yes # of Calls % Yes % of Total Yes Frank - - - 0% Leo 3 3 100% 60% Shammy - - - 0% Rosalia - - - 0% Katie - - - 0% Hector 2 2 100% 40% Arash - - - 0% Abdool - - - 0% Alvaro - - - 0% Totals 5 5 100% You can either plot the percentages, or plot the #Yes column and select the data labels option to show percentages. - Jon -------...

Query Too Complex...Sometimes
Hello, This seems to be one of those issues I feel like I am just missing. I have a report that runs off of a query. When the report is called directly from the report objects list, runs just fine. However, when the report is called using a button in a form, I get the infamous "This expression is typed incorrectly, or it is too complex to be evaluated..." Any quick thoughts before I go through a long troubleshooting process? Thanks! B -- Message posted via Why didn't you post the code so that we can help troubleshoot it? On Aug 7, 4:12 ...

Need Help with this MSN MonyCenteral Stock Quote
About a year ago when the stock market was falling, I wrote a worksheet to show my gains or losses. First I listed each of my stock and mutual funds holdings. For each security I compare my cost basis against the current value of the security and place that gain/loss in a column. The column to then totaled ($3230.07) with the resultant value equal to my total gain or loss for the day. Gain/Loss -$814.83 $1,497.13 $2,892.72 -$153.67 -$191.27 $3,230.07 $3,230.07 Max/Gain/Loss -$5...

Need help with formula!!!!
A B 9 Right ear % =VLOOKUP($F$4,Sheet2!A2:B65,2) 10 Left ear % =VLOOKUP($F$5,Sheet2!A3:B66,2) 11 Enter lesser %: =IF(B9>B10,B10,B9) 12 Enter greater %: =IF(B10>B9,B10,B9) 13 Bilateral Hearing Loss: =(((B12*5)+B13)/6)/100 This formula calcualtes the % of hearing loss in the right ear and the left ear then uses a formula to calculate the % of total bilatearl hearing loss. The formula in B13 multiplies the lesser % of the...

Please help I'm a computertard
My computer crashed and I got my friend to get it to startup and get online but all my programs like Word and Access are gone, and my ex-husband has the disks to install them. Is there anyway to get them back? Unfortunately that probably depends upon how big of a jerk your ex-husband is. :) If your machine requires the software to be reinstalled you're going to have to have the disks (CDs) for it. Unless you perhaps have a full backup of your disk somewhere that you could restore. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP **I apologize but I am ...

Query AD
Hy, there is some tools that i can use to query the active directory? I need to know if users are members of some groups or not. Thanks Kentucky On 21 Nov 2006 05:56:58 -0800, "Kentucky" <> wrote: >Hy, there is some tools that i can use to query the active directory? >I need to know if users are members of some groups or not. >Thanks >Kentucky ldp.exe (Windows Support Tools Download) adfind (you can google that) are two popular tools On 21 Nov 2006 05:56:58 -0800, "Kentucky" <> wrote: >Hy, there is some to...

Customer not appearing in Collection Query
Hi, I am on GP9 SP3 and have created a collection query, however some customers do not appear in that query even though they should. I have done/checked the following 1. Statement to field is populated with a valid email address 2. Customer is not on hold 3. The customer does not have do not send letter box marked 4. Run checklinks on collections 5. Reconciled the debtor 6. Run checklinks on debtor master and Transaction Open tables 7.Created a new query for that one debtor and it says 0 customers found Can you please assist and let me know where I am going wrong. Thank V can you se...

iFrame help
Hey all, I've been working on my first iFrame implementation within CRM 3.0. I created a simple ASP .NET page that reads data out of the MSCRM db and displays it. The user can then check boxes next to the records to indicate whether or not that record was used. Upon submitting the page, the data is written back to the db. The page works as expected in Visual Studio 2005. However, I'm having a couple problems within CRM. The page shows up in the frame as expected, which is fine. Question one is, how do I access methods within the iFrame? For example, I'd like to be able to ca...

Help! Deleted Budget
I inadvertently deleted the Budget in GP 10. I went into Card|Budget, and exported the budget to an Excel file just to have a look at it. When I went back to the Budget Selection window, I hit Delete thinking I was purging the exported budget file. In my hope of undoing the deletion, I imported the Excel budget file that I had exported back into GP, and it is again listed in the Budget Selection window. What I need to know is what damage I did by deleting the entry that was there originally? For example, what other GP functions may have been dependent on the budget before I ...

Help with "Alias emails w/ Exch2K3/Win2K3"
Being new with Exch2K3 I can't see how to setup an alias (or at least this is what I think I need). I've pointed the MX records TO the correct ISP for both the '' AND the '' names via Network Solutions. Here's the scenario: 1) I need the emails sent to '' to be received ON and IN the SAME Exch2K3 AND mailbox as ''. That is, anything send to 'jdoe' at either address is received into his email box in Exch2K3 and on Outlook 2003. How do I do this? 2) I'll then s...

Trouble with Xpath query
All, I'm brand new to XML and have been given a deadline to deliver some new functionality part of which requires stepping through an xml doc. I have hit a wall with the way I've structured an xpath query to select nodes from the xml and would be most appreciative of some assistance. The beginning of the xml doc looks like this: <?xml version="1.0" encoding="UTF-8"?> <ClliOutput xmlns="" xmlns:xsi="" xmlns:schemaLocation="

Error in sql server query on network.
Hi, We have written an application which uses SQL Server 2005 as its database. The database is located on a server and clients connect to the database server using our application. Here is two small tables in the database: ============================================================= create table tkelasman ( [kelasman] int identity primary key not null, [nam] nvarchar(50), family nvarchar(50), father_nam nvarchar(50), identit_number nvarchar(50), shm nvarchar(50), adr ntext, ) create table tkel...