Returning Last Instance of a Duplicate

I have a table with Multiple records for the same serial number.  I need to
return only the latest record and the next previous record based on the date
of the occurence.  Can any one help me wit this scenario?

-- 
Message posted via http://www.accessmonster.com

0
Dopple444
5/16/2007 8:22:49 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
626 Views

Similar Articles

[PageSpeed] 35

You can use one of the four methods presented at 
http://www.mvps.org/access/queries/qry0020.htm



Hoping it may help,
Vanderghast, Access MVP


"Dopple444 via AccessMonster.com" <u33839@uwe> wrote in message 
news:724722e065db5@uwe...
>I have a table with Multiple records for the same serial number.  I need to
> return only the latest record and the next previous record based on the 
> date
> of the occurence.  Can any one help me wit this scenario?
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Michel
5/16/2007 8:27:41 PM
Reply:

Similar Artilces:

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

Eliminating Duplicates from a Query Access 2007
I've run a query that captures the data set I want. Now I want to eliminate Duplicate names and retain only the unique names. These are text fields, can I do this in the Criteria in Design View? Many thanks. (B^>)-]=[ hi, On 01.04.2010 16:46, WSR wrote: > I've run a query that captures the data set I want. Now I want to eliminate > Duplicate names and retain only the unique names. > > These are text fields, can I do this in the Criteria in Design View? Take a look at the Unique Values property of your query. mfG --> stefan <-- Und...

Inserting Date/Time of Last Update
Is there a way to insert a date and time in a cell(s) that will only be updated if there is a change to the worksheet. I'm looking to have the date/time of the last update so that it is easy to tell how current the worksheet is. Thanks -- Lionel B. Dyck <>< AIM ID: lbdyck Yahoo IM: lbdyck Homepage http://www.lbdsoftware.com/ Blog: http://randommgmt.blogspot.com/ Lionel You could put some code in a worksheet_Change() event like so (uses A1) Private Sub Worksheet_Change(ByVal Target As Range) Range("A1").Value = Now() End Sub To implement right click the sheet t...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Duplicate personal folders #11
I am using Outlook 2003. I recently noticed that I have two personal folders in my folders list containing exactly the same sub-folders How con I get rid of the duplicate There are countless posts in this group that address this exhaustively documented issue. Feel free to read any of them. -- Russ Valentine [MVP-Outlook] "Nick C" <nickcwpg@hotmail.com> wrote in message news:uBoS2tg$GHA.1224@TK2MSFTNGP04.phx.gbl... >I am using Outlook 2003. I recently noticed that I have two personal >folders in my folders list containing exactly the same sub-folders > > Ho...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Search for DUplicates
I have excel spreadsheet that has 3000 names in one column . Is there a way to have it search for duplicates and move the duplicates to another sheet ? Thanks Ken Take a look at http://cpearson.com/excel/duplicat.htm In article <ODy0fzZkDHA.360@TK2MSFTNGP12.phx.gbl>, "Ken" <kenmal@hotmail.com> wrote: > I have excel spreadsheet that has 3000 names in one column . Is there a way > to have it search for duplicates and move the duplicates to another sheet ? > Exactly What I wanted Thank you very Much "J.E. McGimpsey" <jemcgimpsey@mvps.or...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- 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 the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

Return
When returning an item, can you return the item by selecting the matrix rather than the individual item and changing the quantity to -1? Hi KIm, Normally this won't work as it works in sale where for each matrix type you can put the qunatites. HOWEVER when you select the matrix item it bring the matrix window but allow only one item at a time to be returned and make the -1 qty automatically in return document type. It work but one by one. Regards Akber "Kim" wrote: > When returning an item, can you return the item by selecting the matrix > rather than the individua...

Returning Multiple Values in a Single Cell
Hello, I posted this same question a couple of weeks ago but forgot to mention a key point. My question was as follows: I have read a few posts regarding this subject but am still somewhat lost. I have a two sheet workbook (Excel 2000). On the first sheet is a list of projects that my department will complete during the year. A description of each project is given, a start date, an end date, as well as which crew will be completing the work (Crew 1 or Crew 2). What I am trying to do on the second sheet is to be able to type in any date, and have the description of the project on that da...

Returns immediate function
Hi all, I know that the function CeRapiInitEx returns immediately. No wait for processing. I want to implement such a function. Returns immediate. no wait. How do I implement such a function? Thanks Ko You might create a UI thread that do the work, launch it in the "non-wait" function and return immediatly to the caller. When the thread done it's works, you can post an application message to return the results.... Greetings, Gaetano Sferra "PPC DEV" <ppc_dev@yahoo.com> ha scritto nel messaggio news:033101c3b388$a7098910$a301280a@phx.gbl... > Hi all...

way to open a form in mutliple instances?
Is there a way to open up a form in more than one instance. i.e. the same form but different data? If so, what is the methos? Thanks for any help. Yes, there is. See the sample code at http://www.daiglenet.com/msaccess.htm The Multi_Instance Forms download is the last one on this page. HTH, Rob "ThorBo" <ThorBo@discussions.microsoft.com> wrote in message news:0296148F-5664-4584-90D6-CDE1BCCC3DED@microsoft.com... > Is there a way to open up a form in more than one instance. i.e. the same > form but different data? If so, what is the methos? > > Thanks for ...

how to hide blank duplicates
I want to find duplicate entries in column A of a spreadsheet and have entered the following in col B =IF(COUNTIF(A1:A$1,A1)>"Y","") This finds the duplicates and enters a "Y" where appropriate but unfortunately it also enters "Y" when it finds a blank cell in Col A Is there a way around this? TIA Use the example on this page Mighty http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Mighty Magpie" <mighty.magpie@home> wrote in message news:F...

Statement Header on last page only
I am using Statement on Blank Paper and I need a header that only prints on the last page for each customer statement. Is there any way to do this. First of all, it would be called a footer. Select the footer tied to the customer number. You can format it to look like a header but you will place the fields in a footer. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Mike Signal" wrote: > I am using Statement on Blank Paper ...

Lookup Values, return multiple.
I need help please. The formula I require has to in Worksheet A :- [B1] lookup DATA in Worksheet A [A1] SP001 in worksheet B [A] [B] [C] SP001 PL001 16 SP001 PL002 5 SP001 DR001 10 CR001 PL001 2 Search for all Text String starting with "PL" in Worksheet B [B] only for SP001 in [A] and return with values from Worksheet B [C] 16 and 5 I need the values to be seperated and not summed. Tx. Appreciate assistance. Try this... In the formulas: Rng1 refers to worksheet B $A$2:$A$5 Rng2 refers ...

% Return Line graph?
Hi- Can someone show me how to customize a graph in Money 2005 to show my stock portfolio's % change over time? I just want a simple line chart showing my percentage change since the beginning - as a way to just figure out relative performance but I can't seem to find the right graph to customize. I can't just chart the value of the portfolio as I have been adding money to it over time which has drastically changed the % returns. I am not sure if what I want is possible or if this is clear but I also have the plugin for MS Excel that I can import money data so if there is...

kit item return
There should be an option in the Store Op Manager --> Configuration --> Option Tab to allow the ability when returning a Kit Item to either return the quantity back to the Kit Items Inventory or to the Kit Item COMPONENTS Inventory. Effectively breaking out the Kit Item automatically when the item is returned to inventory. ---------------- 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 the message pane. If you do not see the button, follow this link to op...

formula to count instances of something
Using Excel 2007. I want to create a series of cells with a formula that will count the number of cells elsewhere that contain a certain word. "[ ]" means a cell. Example: [Category 1] [sub category 2] [#of instances] [sub category 3] [#of instances] Elsewhere on that XL spread sheet, I would have rows that have [sub category 2] [data...] [data...] [sub category 2] [data..] [data..] [sub category 2] [data....] [data....] [sub category 3] [data] [data] [sub category 3] [data] [data] [sub category 3] [data] [data] [sub category ...

How do I stop last number from changing to a 0 in excel
I have a worksheet that has one column that has account numbers. If I enter 16 or more digits the last numbers change to a 0. I have set, cell format to numbers with no decimals. Try setting cell format to text before entering or use an apostrophe then enter the number HTH Regards, Howard "dan12" <dan12@discussions.microsoft.com> wrote in message news:426335E4-F08B-4C23-B6E2-EB45F36101F5@microsoft.com... >I have a worksheet that has one column that has account numbers. If I enter > 16 or more digits the last numbers change to a 0. I have set, cell format > to &...

Last cell in row range with a result
I need to see if anyone can tell me if it's possible to find the last cell in a range that has a result and not just a formulas. We have this file that has a row of formulas as each cell it a different day of the month. In another file I need to pull the current cell and at this time I have to change the cell reference. Please let me know, thanks if you have a row of data , say row 3, with no included blanks, then the last value in that row is: =INDEX($3:$3,COUNTA($3:$3)) -- Gary''s Student - gsnu200713 Gary''s Student wrote: > if you have a row of data , say ...

Open worksheets in same Excel instance
I want worksheets I open to be in the same Excel instance instead of a new instance for each worksheet. I can't find the setting for this. The default is for the same instance of Excel to be used. Something changed it. Try re-registering Excel. From Start->Run enter "Excel.exe /regserver" (no quotes). There is a space before the slash. -- Jim Rech Excel MVP Apologies if a silly question, but you're sure you don't just have 'Windows in taskbar' selected yes? -- Regards Ken....................... Microsoft MVP - Excel ...

Formula that returns the sheetname
Does anyone know of a formula that would return the active sheetname? http://www.mcgimpsey.com/excel/formulae/cell_function.html "TimT" wrote: > Does anyone know of a formula that would return the active sheetname? Great Link! The formula was =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) ....yeah, like I was gonna figure that one out on my own. "David Hepner" wrote: > http://www.mcgimpsey.com/excel/formulae/cell_function.html > > > "TimT" wrote: > > > Does anyone know of a form...