Last Records

I need to query the last records for each date in a table.  The table data 
has three fields - Id (Auto Number), Date and Amount.  Each Date field can 
have one or several records.  Example data:
1,7/1/07,5
2,7/1/07,9
3,7/2/07,2
4,7/3/07,4
5,7/3/07,10
6,7/3/07,6
7,7/3/07,3
I need to query the last record (determined by the Id field) for each date.  
The Id field may or may not be included in the results.  The query should 
return:
7/1/07,9
7/2/07,2
7/3/07,3
How do I do this?

Thanks,
Scott
0
Utf
8/7/2007 5:56:02 PM
access 16762 articles. 3 followers. Follow

2 Replies
797 Views

Similar Articles

[PageSpeed] 20

SELECT TblScott.Date, TblScott.Amount
FROM TblScott
WHERE TblScott.ID In (SELECT Max(TblScott.ID) 
  FROM TblScott
  GROUP BY TblScott.Date);
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Scott" wrote:

> I need to query the last records for each date in a table.  The table data 
> has three fields - Id (Auto Number), Date and Amount.  Each Date field can 
> have one or several records.  Example data:
> 1,7/1/07,5
> 2,7/1/07,9
> 3,7/2/07,2
> 4,7/3/07,4
> 5,7/3/07,10
> 6,7/3/07,6
> 7,7/3/07,3
> I need to query the last record (determined by the Id field) for each date.  
> The Id field may or may not be included in the results.  The query should 
> return:
> 7/1/07,9
> 7/2/07,2
> 7/3/07,3
> How do I do this?
> 
> Thanks,
> Scott
0
Utf
8/7/2007 6:42:09 PM
One way is to set up 2 queries.  

Call the first one qryMaxID:

SELECT Max(Table2.ID) AS MaxOfID
FROM Table2
GROUP BY Table2.dtDate;

Then do another query, usign qryMaxID and Table2 (or whatever the name of 
your table is) and join it on ID:
SELECT Table2.dtDate, Table2.Qty
FROM Table2 INNER JOIN qrymax ON Table2.ID = qrymax.MaxOfID;

This should produce the results you want.





"Scott" wrote:

> I need to query the last records for each date in a table.  The table data 
> has three fields - Id (Auto Number), Date and Amount.  Each Date field can 
> have one or several records.  Example data:
> 1,7/1/07,5
> 2,7/1/07,9
> 3,7/2/07,2
> 4,7/3/07,4
> 5,7/3/07,10
> 6,7/3/07,6
> 7,7/3/07,3
> I need to query the last record (determined by the Id field) for each date.  
> The Id field may or may not be included in the results.  The query should 
> return:
> 7/1/07,9
> 7/2/07,2
> 7/3/07,3
> How do I do this?
> 
> Thanks,
> Scott
0
Utf
8/7/2007 6:44:00 PM
Reply:

Similar Artilces:

select for last record for each customer
I have table with many recodrs as this table: customerID Date Stock1 Stock2 Stock3 Stock4 112105222 22/01/2006 5 6 2 1 112105222 23/05/2006 4 8 6 7 112105222 01/02/2007 6 9 4 6 112101024 12/12/2007 7 8 9 1 112101024 15/02/2007 7 9 2 1 . . . . . . by select qury I want show just the last record for each customerID as...

New User
I am creating a new user. When I save & close I get an error. The details of the error as follows. How can I solve this issue? Error Number: 0x80040E2F Query String /biz/users/edit.aspx_CreateFromType=10&_CreateFromId={1B63542C-F92D-46D7-B643-AD25E04B8362} User Message: Duplicate Record A record of this type with this name already exists. Please choose a different name and try again. Is there another user record in the system already with the same network login name? Matt Parks MVP - Microsoft CRM ---------------------------------------- ----------------------------------------...

MS Query and SQL
I am trying to write a query where the following columns are present: Item.Description Item.Price Department.Name Supplier.Name The problem is that when I set up the joins in MS Query, the records that don't have an associated supplier assigned are not returned. When I try to change the join type on Item.SupplieID-Supplier.ID, it says outer joins are not allowed with more than two tables in the query. Any ideas on how I can get it to return all records even if there is no supplier? Thanks, Jason I'm not familiar with MSQuery, but if it will let you insert the SQL command ...

Marking Customers Inactive based on last transaction date
Is there a way to mark customers inactive based on the date of their last transaction. For example make all the customers that have not ordered within the last year would be flagged inactive. If there is not an automatic way to do this, is there a last transaction date in the DB that I could use to create a sql script on the customers that meet the criteria? Regards, Bill Bill: I looked at the RM00103 table (summary table) and it holds a field called LSTTRXDT which is last transaction date so I would think you could go ahead and build an update statement on the RM00101 and set the INA...

add hours flown last 12 months
i'd like to add the total hours flown the last 12 months(sheet: totals) counting from the last entry (sheet: logbook). See attachement.. Attachment filename: logbook.xls Download attachment: http://www.excelforum.com/attachment.php?postid=62816 -- Message posted from http://www.ExcelForum.com Hi not really sure which column you want to sum (looking briefly at your attachment). You may try to explain your problem in plain text (most people won't open an attachment) -- Regards Frank Kabel Frankfurt, Germany > i'd like to add the total hours flo...

Have data with multiple records associated to same ID
My Data Situation: A B 33007014220000 33007014220000 DLL 33007014220000 CND 33007014220000 CBL Need to get it like this: A B 33007014220000 CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan How many subsets are you likely to have (maximum)? Are the values DLL, CND etc in column C, or are they tagged on to the end of the I...

Last Exchange Server 2003 Academy
I apologize in advance for making a semi-commercial announcement, but a colleague has asked me to post an announcement that there are seats still available in HP's Exchange Server 2003 Customer Academy in Philadelphia from the 3rd through the 7th of October. These have been very well received by customers in the past, and this one will be the last to be given. If you are interested, please visit www.hpexchange2003.com and fill in the nomination form using Roberta Hanlon and roberta.hanlon@hp.com as the Nomination Information. You will then be sent an email detailing how to complete...

verify all subforms have records
Access 2003 I have a form called f001ProjectReview (PK ProjectID) it has several subforms linked by ProjectID. Sub1, Sub2, Sub3. There is a button that activates a pop up form called fClosureApprovalPopUp linked by ProjectID. On fClosureApprovalPopUp I have an approval button. When user clicks the approval button I need to verify that all subforms on f001ProjectReview have records. If there is a subform that does not have a record then display message that required data is missing and exit without saving. Your help is greatly appreciated!! -- deb You could u...

Money Deluxe 2007
The Last Paid date in Bills Summary, does not always update properly with the latest paid date of some of the bills. I have tried Quick and Standard Repair and the some of the dates were corrected and some were not. Basically MSM works perfectly, but this has been a recurring problem for me over the last 3-4 years and I have addressed this Issue with MS on several occassions, but the software never seems to get fixed. I do not believe that the problem has anything to do with me becasue I have used MSM for a long time and with this exception, the program works perfectly. Does anyone h...

minimal payroll records
Hello: I'm pretty sure that I'm correct on this. But, I want to check with you all first. I know that as a means of disaster recovery (server gets blown away) that a client can restore backups of their DYNAMICS and comapany databases onto a new server. I have done that before. But, let's say the client just wants to restore a minimal set of data just to gbe able to cut paychecks out for employees. Can they restore just their DYNAMICS databases and the UPR master tables ? Thanks! childofthe1980s no, that would not work. It may be possible to achieve what you want; You s...

Record Locator
I have posted this in the past, but no one was brave enough to take it on: How can I build a record locator similar to the way it works in MSOutlook, where the more characters you type, the closer it moves to your record. And if you type a string that doesn't exist in the table, it will still move as close as it can, based on the first part of the string. IE: You type MARY. The record with Mary comes up. You type MARRY (by mistake), and there is no MARRY record existing, so: The first record that starts with MAR comes up. TIA, Kathy On Apr 18, 1:54 pm, "Kathy Webster&quo...

Suppress Autonumber record from displaying on the form
I have a query that feeds the form. I would like to only show the query results without the Autonumber record showing up at the very end. Is it possible to suppress it. Thanks MSA MSA wrote: > I have a query that feeds the form. I would like to only show the > query results without the Autonumber record showing up at the very > end. Is it possible to suppress it. > > Thanks > MSA Set the Form's AllowAdditions property to No. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

How to unconcatenate a record in Access
I have a table with a fields that contains both a date and time. I am having trouble pulling a query using a Between (this) And (That) criteria. Can I do something like I can in Excel where I can seperate the two pieces into two seperate fields? -- Bill1967 On Mon, 25 Jun 2007 09:46:05 -0700, Bill1967 <Bill1967@discussions.microsoft.com> wrote: >I have a table with a fields that contains both a date and time. I am having >trouble pulling a query using a Between (this) And (That) criteria. Can I do >something like I can in Excel where I can seperate the two pieces int...

How to stop acces from saving the record
Hi I am using main form and subform and when I focus on the sub form Access saves the record and I need to know how to stop acces from saving the record when the subform is on focus? I understand that I need to unbound the main form and then bound it on before update event. I tried this code but I get some errors. On Error GoTo Error_Handler Dim db As DAO.Database Dim rst As DAO.Recordset Set db = CurrentDb Set rst = db.OpenRecordset("tblWhatever", dbOpenDynaset) With rst .AddNew !WhateverID = Me.txtWhateverID !FirstName = Me.tx...

Go to the last-new row in a datasheet view
What is the code to go to the last row where is added a new record when you type something? I tryed adding a record to the form recordset but I don't want to leave that record empty, I just want it to be ready to load data into. Greetings from Paraguay. Claudio Bogado Pompa. Use the GotoRecord method. It is explained in VBA Help. If you are using a macro, then it is the GotoRecord action, also in VBA Help. -- Dave Hargis, Microsoft Access MVP "Claudio Bogado Pompa" wrote: > What is the code to go to the last row where is added a new record > when you type something...

Recording a command button name
I have an application where the user is prompted to save when they leave the the data entry form either by previewing a report of the data or by closing the form completely. There are two buttons a user can use to do this cmdPreview or cmdClose. I use a module to promt the user for svae. What I would like to do is differentiate to the module which button was clicked. Is there a way to do this? thanks You could take a look at Screen.ActiveControl.Name -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "E" <...

Adding new numbers in last row
Hi everyone I want to run a macro to do the following. When I run the macro, it should increase a row and record th following. Let me explain. In cell A1 the value is 1 , in B1 value of $F$10 In C1 value of $G$1 (In B1 & C1 it should do copy- paste value from F10 & G10) when I run the macro the following to be recorded automatically. In A2=A1+1, in B2 paste value of $F$10 In C2 paste value of $G$10 When I run the macro next time it should record in ROW#3 like In A3=A2+1, in B3 paste value of $F$10 In C3 paste value of $G$10 Same time in $H$10 always the value in the last r...

internet explorer 8 slow for about the last week
Any one else having a slow response with Internet explorer 8.... only for the last week this has been happening. maybe there is an issue with my computer but i dont even know where to start.. any suggestions would help "Tips For Solving Problems with Internet Explorer" http://windows.microsoft.com/en-US/windows-vista/Tips-for-solving-problems-with-Internet-Explorer --- Leonard Grey Errare humanum est quilting2 wrote: > Any one else having a slow response with Internet explorer 8.... only for the > last week this has been happening. maybe there is an issue with...

Portfolio not updating "today's change" & "last updated" fields
Does anybody know why portfolio is not updating these fields? I don't know why and I am also getting the same problem. Frank "evy38" <evy38@discussions.microsoft.com> wrote in message news:5803A226-1FF0-4675-BD78-10BB49F6CD94@microsoft.com... > Does anybody know why portfolio is not updating these fields? ...

Linking an iframe to a unique intranet account record page
We have an internal client database website that I'd like to link to a custom tab within CRM (as an iframe). Each record/url in the client website has a client number. This client number is the same as the "AccountNumber" within CRM. So I want AccountNumber '7130' to display this url within my custom iframe tab: http://intranet.website.com/apps/company/body.asp?cno=7130 The account name also matches exactly, so if it is easier to build the url based on the name, that would work too. Thanks in advance... FYI, here's what I've done thus far: 1. Create a...

Unable to view all CRM records
Hello there, We had migrated a CRM v1.2 test implementation into a Production environment some time ago. Although not all the users (especially new users created) are able to view all records from the previous test implementation. We migrated records and recreated the user list and merged all existing UserID for the owners and all users from the old SID's to the new SID's. This has not been a problem for most old users, although we are now receiving problems with some users (some old, but especially new users) not being able to view the old records, only new records created since t...

count related records
2 tables - 1) tickets and 2) messages. Tickets contains the main data and messages contains all the comments made relating to that ticket. How do I count the number of records (or messages) in the related table? So, I have 1 ticket in the system and 4 comments have been made in that ticket. I want to be able to take that number and append it to another ticket table showing "4" in that field. Appending the count to another ticket table is not the thing to do! Say you append 4 and then add another message. The count is now 5 but you only have 4 in the other table....

Find Records that are NOT duplicates
Hello All: I have a list of numbers most of which are duplicates. However, some numbers are listed only once. I would like to create a list of the numbers that are only listed once. Eliminating duplicates is not a problem, but how can I isolate the numbers listed only once. Hopefully it is something easy that I am just not thinking of... Here is a quick example: 1111 1111 2222 3333 3333 4444 4444 5555 6666 6666 I would only want 2222 and 5555. Any help is greatly appreciated. cleech <cleech@gmail.com> wrote: > Hello All: > I have a list of numbers most of which are dupli...

Business Cards listing training records
I am using an Avery business card template in Access 2007 to show the name and list of training records for each individual. I created a query to gather the appropriate information and designed the card with a FullName Header, with the training topics listed in the detail section. Obviously each person will have a different number of training topics to list on the card. Is there a way to limit the number of lines printed in the detail section, so that any topics that don't fit for one individual would be printed on a second card, and constrain the size to fit the Avery tem...

Next Record Event
Hi,I am wanting to add the current date to each new form, as I advancethrough the recordset, using the buttons built in to the form at thebottom. I have currently the following code, but I am wanting toenable the date stamp on successive record entries in the samesession. Thanks, Louis---------Private Sub Form_Load() 'Set date stamp on current record Text50.Value = DateEnd Sub ll, First, you should name your objects something more meaningful than Text50... something CreateDate, or InvoiceDate, MyDate etc... Delete the code you have now in the OnLoad event. Next, is Text50 a D...