Need a query that does this...

Consider the following table:

ID        dateField        intFieldA        intFieldB        stringFieldA 
stringFieldB

I want a query that will give me the row with MAX(dateField) grouped by ID. 
If I say:

SELECT Max(dateField), ID
FROM table
GROUP BY ID;

Then that will give me each ID in the table and its maximum dateField.  I 
want the entire ROW that contains this maximum dateField, and I want it for 
each ID.  How do I write this query? 


0
0to60
11/2/2007 7:03:36 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
847 Views

Similar Articles

[PageSpeed] 29

Do it in 2 seperate queries ( or in a union ).

Part 1 finds the max date and ID, part 2 links the date/ID back to the table 
and pulls the row that matches those values.

"0to60" wrote:

> Consider the following table:
> 
> ID        dateField        intFieldA        intFieldB        stringFieldA 
> stringFieldB
> 
> I want a query that will give me the row with MAX(dateField) grouped by ID. 
> If I say:
> 
> SELECT Max(dateField), ID
> FROM table
> GROUP BY ID;
> 
> Then that will give me each ID in the table and its maximum dateField.  I 
> want the entire ROW that contains this maximum dateField, and I want it for 
> each ID.  How do I write this query? 
> 
> 
> 
0
Utf
11/2/2007 7:20:08 PM
SELECT table.ID, table.dateField, table.intFieldA,
table.intFieldB, table.stringFieldA, table.stringFieldB
FROM table
INNER JOIN
(SELECT Max(DateField), ID
FROM table
GROUP BY ID) AS Sub
ON table.ID = Sub.ID


-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"0to60" <holeshot60@yahoo.nospam> wrote in message 
news:%23KiSPMYHIHA.3980@TK2MSFTNGP03.phx.gbl...
> Consider the following table:
>
> ID        dateField        intFieldA        intFieldB        stringFieldA 
> stringFieldB
>
> I want a query that will give me the row with MAX(dateField) grouped by 
> ID. If I say:
>
> SELECT Max(dateField), ID
> FROM table
> GROUP BY ID;
>
> Then that will give me each ID in the table and its maximum dateField.  I 
> want the entire ROW that contains this maximum dateField, and I want it 
> for each ID.  How do I write this query?
> 


0
Douglas
11/2/2007 7:28:42 PM
Reply:

Similar Artilces:

XPath query that gets elements whose subelements have an attribute with a particular value
Hi all, I'm having some trouble with a XPath query I would like to make I have the following simplified XML <A><B><C name="Test"></C><C name="Test2"></C></B><B><C name="Test"></C></B></A I would like to return all B elements that have a C sub-element that have a name with a particular value For example - I would like to retrieve all B elements that have a C sub-element with a name of Test. This should return 2 B elements. Likewise, if I wanted all B elements with a sub-element C with the...

Need a program that works with Outlook Express
I know there os a program for downloading that works with Outlook Express. Fixes such things as missing < at the start of reply lines. Can't remember the name nor came I find it searching the Internet. Do you know the name of a program like that. Thanks OE-QuoteFix: http://home.in.tum.de/~jain/software/oe-quotefix/ -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA " Cal Who" <CalWhoQQ@roadrunner.com> wrote in message news:OTTBNIKbKHA.5796@TK2MSFTNGP06.phx.gbl... >I know there os a program for downloa...

need some basic help
I have a basic daily sales sheet that has a number value in column one for the day of the month from 1 to 31, column 2 has the daily sales that I put in manually and column 3 has the total sales for the month =SUM(B2+C1). At the bottom on row 31 I calculate daily average sales with a basic =C3/C1 and I have to change on a daily basis by hand... all extremely basic stuff. But I'm looking for a way to calculate the daily average sales automatically as I put in the daily sales each day without having to edit the row 31 formula every day. I tried googling for daily average sales but th...

Need help with displaying 0's after decimals
Hi, I have 2 cells. One with the number that has 2 decimals and one with text. When I use the "&" function to combine the 2 into 1 cell, all of the numbers with the 0 at the end disappear. Example: cell 1: 3.80, cell 2: +80. cell1&cell2 resulted in 3.8 +80 instead of 3.80 +80. Any help would be appreciated. mike You'll have to provide the formatting: =text(a1,"0.00")&... same with dates and times, too =...&text(a2,"mm/dd/yyyy") mike wrote: > > Hi, > I have 2 cells. One with the number that has 2 decimals and one with t...

UDF on query
How to create any user define function that can be called from query like Ms Access standart function? If possible give one simple function. TIA Yanto If you have a function (has to be in a module) that get a parameter and return a value, you can call this function from the query just as you use a build in function in Access e.g Function FunctionName(MyNumber as Double) FunctionName = MyNumber * 100 End Function Select TableNAme.*, FunctionName([FieldNameToPass]) As NewFieldName From TableName -- Good Luck BS"D "yanto" wrote: > How to create any user define fun...

Before Query and After Query script in Integration Manager
I have a Before Query script for a single source Inventory transaction that renames the file from "A" to "A.csv" and an After Query script that copies A.csv to a backup folder and adds the date to the filename. It works great. I copied the scripts to a Payables Transaction with three sources. I changed the file names to "B" for the payables data file but I am getting the error "Permission Denied" and I read something about this being because I copied the script? Doesn't make sense to me. Because it has three sources (PM Transaction; PM Detail;...

List Paragraph Style needs a List Style?
In creating a list paragraph style ...... well with John McGhie creating a list paragraph style and I'm following his directions in his post... I created a list style. (Is "list paragraph style" the correct terminology?) John's summary of his directions is quoted at the bottom of this post. So now I'm curious, why doesn't my list paragraph style definition indicate that it is using that list style that I/we created? I assume I must have a list style for each list paragraph style and if I deleted that list style there would be problems? Thanks. ...

Date selection criteria expression help needed
i have a database with dates listed as the general date (date and time). some of the cells have null (empty) values. I would like to query for null values and today's date expressed as a long date (mm/dd/yyyy). I am having trouble. any help would be greatly appreciated. Field: YourDateField Criteria: Is Null Or = Date() OR Field: YourDateField Criteria (Line1): Is Null Criteria (Line2): = Date() '==================================================== John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland B...

Follow up to previous post: Need code to import into access/Autonumber
I was trying to tihnk of solutions to the table in access being out of order when i use: DoCmd.TransferSpreadsheet in code to import an excel file. 1. I could read the excel file from access. Does anyody have access code to open an exel sheet and read it into a table in access in code? 2. have the user maintain an autonumber like field. THis way, I can have this to sort on when I import it. Does excel have an autonumber field? Thanks Some sample code for reading EXCEL spreadsheet and writing to recordset that may get you started: Dim lngColumn As Long Dim xlx As Object, xlw As O...

Need help with Outllook Laptop client install error
Attempting to install the laptop client and the SQL Server 2005 Express Edition fails with the follow message, "Unknown exit code 29559". Any help with problem would very much be appreciated. Bob At what point in the installation does it fail? Was SQL Express installed before the CRM client? -- Richard Riddle CRM Developer Autonomix "Bob" wrote: > Attempting to install the laptop client and the SQL Server 2005 Express > Edition fails with the follow message, "Unknown exit code 29559". > > Any help with problem would very much be apprec...

New Report Needed
The Boss needs an end-of-year report. One of the custom reports comes close, but yields screwy results. What we need is a report that shows the customer information (name, address, etc) and the total amount of purchases for the period. It would be nice to be able to input the date range, but YTD is the minimum requirement. If we could specify how many (top 10, top 50, top 100) customers to show, that would be icing on the cake. The goal is to output to Excel. The problem is, the current custom report shows the customer info (address etc) on the report DETAIL line, which makes no sense...

I need to disable browser in outlook2003
Hi, My name is stalin.I am working as a system adminstrator. I need to disable browser in outlook2003.In our office lot of people access the internet through the same Illegally. Kindly do the needful. Regards, Stalin.P ...

Need to retrieve a deleted Excel File
On Saturday I hide an excel sheet and then protected the workbook. When I got into work today to open the file it was non-existent. What do I have to do to retrieve the file? Does it make a difference if the file extension ended with .csv? Thanks in advance! Search in Windows to make sure it's still on your compute -or go to the folder the file is in and look to make sure it's still ther -or If you didn't do this, you might need to to text, which includes *.cs Excel is not set to open this type of file by defaul tj Did you (try to) open the file with Excel. If so : did ...

Outlook 2003 Problem-Need immediate help
Hi, I have been using Outlook 2003 and XP for many years. For last two days I am facing huge problem with Outlook as it does not download new message and when I click send/receive the CPU usage surges to 100%. I tried safe mode etc but it does not work. I shall highly appreciate immediate advice to solve this problem. Thanx Do you have a virus scanner installed which integrates itself with Outlook? Uninstall this integration part of your virus scanner and try again; you'd still be sufficiently protected by your on-access scanner part of the virus scanner. For more details s...

assistance need with excel doc
Hi guys I am trying to copy rows which has minor function. These 5 rows have titles and a functions displaying items from another sheet. I tried to multiply (copy) these rows buy dragging the mouse down the sheet, but it makes copies with the function adding 5 instead of + 1. e.g "=Evaluation!C5&Evaluation!AY5&Evaluation!D5", the next set would be "=Evaluation!C10&Evaluation!AY10&Evaluation!D10" instead of "=Evaluation!C6&Evaluation!AY6&Evaluation!D6". I would like to know if I could copy these 5 rows a set adding + 1 for the formul...

Help! Need a Query or something!
I have a few credit cards in my MS Money file. My assistant keeps track of 2 of them. I would like her to be able to enter the credit card transactions into MS Money (at least), but I don't want her to see the private information I have littered throughout the rest of the .mny file.... Is there a way someone can write a VB procedure to push data from excel to money? My best preference would be a query that updates every time I open money, but I know I'm dreaming. I've tried the few products out there that change .xls to .qif, but! The problem is I'd like to have ...

Using a cell to determine a value in a database query.
Hi folks, I've run into a problem recently - maybe there's no good answer, then again perhaps you can help me come to one. I have a spreadsheet on which There are three entry fields: start date end date store number I've managed to map start date to [startdate] in a query and end date to [enddate] - that seems to work fine. The problem I'm running into now has to do with the store number field. In the database, store number is a 4 character field, as of now the numbers of the store are like 01,02,03,04..HQ,RO. When I try to map store number to [storenum] in the query, I ...

I just deleted an excel file I need. Can I get it back? Thanks!
I just deleted an excel file I need. Can I get it back? Thanks! is it in your recycle bin? "DebbieT" <DebbieT@discussions.microsoft.com> wrote in message news:1BD426FF-C959-4918-8343-95ABDCAA7C96@microsoft.com... >I just deleted an excel file I need. Can I get it back? Thanks! ...

New Reporting, Budgeting & Query Tool
For those of you that haven't yet looked at XL Reporter (XLR) I encourage you to check it out. XLR has been resolving a lot of reporting & budgeting issues for customers. XLR is an Excel-based tool that has powerful analysis of queries as well as an easy to use report writer for reporting on the GL & Sub-ledger modules (Also MDA, Fixed Assets, Inv & PA for GP). XLR works on Axapa, Great Plains, Solomon & Navision (Nov-04). www.xlreporter.com for more information. ...

How to know which mfc.dlls are needed to run my app
When I build a .exe using MFC, it won't run unless it can access the proper .dlls (mfc32d.dll, etc.). How can I find which .dlls I need to include when I want to install my software on another computer? First of all, if your application is complaining about a missing mfc DLL that has a "d" on the end of the name then you need to rebuild your application using the Release build. The debug DLLs are not, nor should they be, redistributable. Next, you can run the depends.exe utility to identify what DLLs your application requires. It will show you ALL the DLLs that are needed to...

Update query
I have two tables with all most the same information. How can I update from one table to another using an update query. I am trying to replace missing data from one table to another. What kind of results did you get? This generally requires the tables be joined on the primary keys from the tables. -- Duane Hookom Microsoft Access MVP "Floyd Forbes" wrote: > I have two tables with all most the same information. How can I update from > one table to another > using an update query. I am trying to replace missing data from one table to > another. > > >...

query for financial year
I would like to write a query where I do not have to 'hard code' a date range for a financial year. The financial year begins 1 July through to 30 June. The column name I am using is [data awarded]. I assume the the code would be part of the WHERE clause in the SQL statement. In query design, type an expression like this into the Field row: FinYearEnd: Year(DateAdd("m", 6, [InvoiceDate])) substituting your date field for InvoiceDate. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, ...

Need Outlook Calendar ONLY
I am using 2002 Outlook, but ONLY for the calendar, only user on desktop, no internet cocnnectivity required. In order to upgrade, I would have to purchase the the whole Outlook program afaik --- is there a cheaper way to obtain the calendar portion of Outlook? I have tried using the google and hotmail calendars, I prefer Outlook. tia No cheaper way, the calendar is an intrinsical part of the whole program <shel123@DELETETHISjuno.com> wrote in message news:iuaj85dbkiq2doul48n2pku7jg78kiuc5i@4ax.com... >I am using 2002 Outlook, but ONLY for the calendar, only user on > desktop, n...

Need help with signature font changing
Hello there. Any help on this is greatly appreciated. I have a windows xp client in a networked/exchange environment. I have a remote user who may not be all patched up for Office 2003. Anyway, the user sent an email to an oustide address and when the recipient opened the email he saw that the senders signature was in size 75 font. The signature is supposed to be a size 8 font. This, according to the client is the only time this has happened. As a matter of fact when she sent me an email the font was fine. Is there something that could have happened to cause this? ...

Unable to join 3rd subquery in nested query
I am able to correctly nest 2 queries into one, but am unable to successfully get the third query to work. I do this all the time in Pass-Through, but this is a straight Access query. So this works: SELECT R.AUID, L.Attainment_Desc_Abbrv, R.Attainment_Desc_Abbrv FROM (SELECT [Assessment Table].AUID, ADB_Attainment_Abbrv.Attainment_Desc_Abbrv FROM ADB_Attainment_Abbrv RIGHT JOIN [Assessment Table] ON ADB_Attainment_Abbrv.Attainment_Desc = [Assessment Table].ARUS) AS R INNER JOIN (SELECT [Assessment Table].AUID, ADB_Attainment_Abbrv.Attainment_Desc_Abbrv FROM ADB_Attainment...