Query most recent records only

I am not having much luck with trying to write a query to find the last (most
recent dates) 5 records for ALL names listed in the 'Customers' table.

SELECT tblOrders.*, tblCustomers.ID
FROM tblOrders RIGHT JOIN tblCustomers ON tblOrders.Name = tblCustomers.Name
ORDER BY tblCustomers.ID, tblOrders.Date;

Appreciate any help,
Kevin

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

0
KevinE
3/5/2010 4:23:06 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1587 Views

Similar Articles

[PageSpeed] 2

hi Kevin,

On 05.03.2010 05:23, KevinE via AccessMonster.com wrote:
> I am not having much luck with trying to write a query to find the last (most
> recent dates) 5 records for ALL names listed in the 'Customers' table.

SELECT TOP 5 O.*, C.ID
FROM tblOrders O
RIGHT JOIN tblCustomers C
ON O.Name = C.Name
ORDER BY C.ID, O.Date DESC;

btw, I'm quite sure that you have normalization problem. Why do you need 
to join by name?


mfG
--> stefan <--
0
Stefan
3/5/2010 8:44:52 AM
Stefan Hoffman's solution gives you the top five by date in table orders over 
all customers (basically 5 orders).

If you mean you want the top 5 orders by date for each customer then that can 
also be done using a correlated sub-query in the WHERE clause.  That would 
look something like the following.  I guessed that tblOrders has a primary key 
named OrderID.  You do need a primary key in the tblOrders.

SELECT tblOrders.*, tblCustomers.ID
FROM tblOrders RIGHT JOIN tblCustomers
ON tblOrders.Name = tblCustomers.Name
WHERE tblOrders.OrderID in
    (SELECT TOP 5 OrderID
     FROM TblOrders as Temp
     WHERE Temp.Name =tblOrders.Name
     ORDER BY Temp.Date DESC, TEMP.OrderID)
ORDER BY tblCustomers.ID, tblOrders.Date

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Stefan Hoffmann wrote:
> hi Kevin,
> 
> On 05.03.2010 05:23, KevinE via AccessMonster.com wrote:
>> I am not having much luck with trying to write a query to find the 
>> last (most
>> recent dates) 5 records for ALL names listed in the 'Customers' table.
> 
> SELECT TOP 5 O.*, C.ID
> FROM tblOrders O
> RIGHT JOIN tblCustomers C
> ON O.Name = C.Name
> ORDER BY C.ID, O.Date DESC;
> 
> btw, I'm quite sure that you have normalization problem. Why do you need 
> to join by name?
> 
> 
> mfG
> --> stefan <--
0
John
3/5/2010 2:40:36 PM
Reply:

Similar Artilces:

automate adding records to a grid
I'm trying to automate adding records to a grid control (such as lines in a journal entry, for example). I'm able to fill in the fields on the current line using either macros or vba without difficulty, but the problem is when I try to move to the next line. I've tried macros, but they seem to need to know which line of the displayed lines in the grid we're filling in in order to move to the next line. For example, "line 1" is completed, then you do a move to "line 2", then move to "line 2" again, then "line 2", etc. in a grid ...

Arabic ODBC query
I am trying to read and write arabic data into Access2000 using MFC ODBC (CRecordset) , but I can only see '?' marks. after doing a research on the news groups , I understand that a tweak needs to be made on RFX_Text can anyone guide me what needs to be done. Appreciate a response. thnx P.Ekkoratha ...

Subquery select field from previous record
I have the following table setup Table Name - tblInventoryCounts Field Names - Store Number, Count Date, Item name, Count Amount Sample Data Store Number Count date Item Name Count Amount 9 1/4/2010 Gear 10 9 1/11/2010 Gear 18 9 1/18/2010 Gear 18 I have a parameter query where the user is asked for the store number and Count Date. I need for the query to return the information for the date entered and the Count Amount from 7 ...

Conditional formatting query #2
Hi, I thought I knew how to do this, but it turns out I don't. If I have names in column A (eg Alan, Bob, Cathy), and values in column B (see below), is there any way I can use conditional formatting to change a cell in A, based on a value in B? A B 1 Alan 4 2 Bob 1 3 Cathy 3 Essentially what I'd like is to highlight the name, if the value is 3 or over. I know how to change the cells in B using conditional formatting, but can't figure out how to change A cells. Thanks, Fiona Select all the cells you want Conditionally formatted. Try this with Formul...

Multiple Item query
I've read a few posts on this query and it looks like creating a table where I input the item numbers in it and linking it to the query seems to be a good way to get a lot of items in a query. Now what If I have hundreds of numbers I want to look up? do I input all 100 numbers into the table? Or what if it's like 10 numbers but I want create seperate queries for different groups of numbers, should I have 1 table for one group of numbers? What would be the easiest way for a novice to do this? It is easier to fill the table with just the values you need for the query you want to run, t...

display ratios as written in web query
I am using a web query to display data about screen contrasts, all written with the formatting of "700:1" or "3000:1" It displays corectly in the edit web query window, but everything I try to make it display as delivered fails. I tried text, custom formats with @ etc, but it seems to insist on doing the calculation before turning it back to text, or as a ratio it does the division. (The text formatting for this works OK if I type directly into the spread sheet) Is there a way to make a web query display as written? Any help appreciated. Keith ...

able to record & run macro but not edit !
All Excel97 . When I open a file with an existing macro (or create a new spreadshee and simply record an entry in a cell ) and go to Tools/Macro/Macros th macro list is visible and I can run the macro but if I try to edit it get a Microsoft Visual Basic pop-up 'Unexpected error ; quitting' o if I simply try to open the VBE i get a similar pop-up with ' Out o Memory'. Same files work fine at work (Excel 97) where I edited macros n problem. I've tried uninstalling/reinstalling Excel from the office 97 dis along with data access & Office tools (and their elements) ti...

Using a Check Box in a Subform to select records
I have a Form with a subform and two combo field are used to filter for records in the subform. The records in the subform have a bound check box. When I check one record on the subform and then move my forcus off the subform, every record becomes checked. Any suggestions? Thanks Are you sure the check box is a bound control? The behaviour you are getting suggests that its more likely to be unbound. What does it say in the control's ControlSource property in its properties sheet? If it is bound to a column in the underlying recordset then the behaviour could be explained by ...

Append Query Not Working 05-03-10
My Append query is not working and i am not sure why... Here is my SQL INSERT INTO tblContractPOTracking ( TrackingID ) SELECT tblDocTracking.TrackingID FROM tblDocTracking WHERE (((tblDocTracking.DocumentNumber)="5" Or (tblDocTracking.DocumentNumber)="6" Or (tblDocTracking.DocumentNumber)="7")); Please help! "not working" is a bit vague ... If you want more specific suggestions, please provide more specific description... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and s...

Display a count of unique records in a query in Access 2007
Hi, I'm not sure if I should be in this group, or Reports, but I'll start here. I have a user who would like to do a count of unique records and display the information in a query, or a report. Here are the basics: She has a list of donors, some of the donors donate more than once to the same cause. What she would like is a list of the number of donors, per cause. But, if the donor ID repeats for the same cause, she does not want that ID counted again. Example: We can get a query to return a count of the records per cause. It is returning a value of "7" for a cause,...

DNS issues, reverse lookup, MX records
Hello, I've done some searching in the groups, but I'd like to pose a specific scenario. We are getting these errors: #4.7.1 smtp;450 4.7.1 Client host rejected: cannot find your reverse hostname, This is the situation. Our domain name is registered with network solutions. Our website is hosted with Yahoo on a business account. Yahoo used to host our email as well. Recently we put in a local SBS 2003 server to host our Exchange locally. Originally our Network solutions account had all DNS pointing to Yahoo's name servers, as it should have. We wanted to switch the MX reco...

Form doesn't show any records.
I have a form that doesn't show all my records when I open it. For example, I open Access and then open the database. I click on my form to open it, and it shows no records. I know for a fact that there are several records entered into the database via my form, but the only way I can see them is to open the actual table to which my form is linked. Is there something I need to do to correct this problem that will allow me to see every record entered using my form? Thanks. Sounds like the DataEntry property of the form is set to Yes. Change it to No. DataEntry = Yes means the fo...

Query Date Help
I need to create a query that will do the following: these are my fields: resign date, hire date I need to subtract the resign date from hire date but I want it to return the answer as years, months & days ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days is there a way to use the DateDiff function to return years, monts & days? Look at this link on "A More Complete DateDiff Function" http://www.accessmvp.com/djsteele/Diff2Dates.html -- Good Luck BS"D "aldunford" wrote: > I need to create a query that will do the following: > > th...

Run query from date input on form
Hello all! I have what I hope is a easy situation. I have a table that has information that is entered daily with a field "Completed Date". I want to add a text box to a form where a user can enter a date and hit a button that will run a query for that specific completion date so I can have the query results exported to Excel. Would I use an unbound text box on the form? How would I set up the query to take the date entered in the form? Thank you very much! - Joker -- Message posted via http://www.accessmonster.com Yes, an unbound textbox would be one w...

Filter every nth record within a worksheet...
Hi, In the past, I have used the Data/Filter/Advance Filter tool to get unique records based on text in a column. Is there a simple way to obtain the nth rows in a worksheet? For example, I wish to display the 4th, 8th, 12th, 16th, etc. rows. Or perhaps the 3rd, 6th, 9th, 12th, etc rows only. I thought if I could specify a criteria, this might work but I don't know how to approach this task. Thanks in advance for any help you can provide. -Greg I use a helper column and Data|filter|autofilter. I use a formula like: =mod(row(),4) Or =mod(row(),3) Then filter to show the 0's, 1'...

Workflow Engine -- Firing on Change to a Field in Existing Record
Hi, Has anyone found an acceptable work-around to fire off an automated workflow rule when a value in a field of an EXISTING record changes? Out-of-the-box you can only trigger rules to run when a record is created, the status changes (active or inactive) or run manually. Alan ...

New record on a new day (re-post)
I seem to be having problems with this post, so I am going to try again. I need help in coding a form so that the following can happen: The scenario is that a user will open the form for the first time on Sept 21st, for example, and the form will be blank. Any subsequent times the user opens the form on Sept 21st it will default to the same record in the table for that day so that more information can be added to that record. On September 22nd, the form will be blank once again. I was given the following code to try: Private Sub Form_Load() On Error GoTo Error_Handler Dim db As DAO.Dat...

question on tables and queries
I've imported 2 tables from excel and ran a query against those two tables to determined the difference. This is something I would like to do each month. I would like to keep the same settings in my database, is there a way I can easily import/update the data in the new table without creating a new one each month? The query would stay the same as I'm comparing the same two files, but the the data will be changing every month. I don't know much about access and would like to know if there is an easier way of doing this besides creating a new table and query each month? I&...

Records are not deleted in the database
Hi everybody, today we noticed when we are deleting records in the web client, they stay in the database with the value 2 in the DeletionStateCode Column. I guess normally this is the markup for some sort of job or service to clean these records, but apparently this never happens. We have deployed Rollup Package 2 which should include a hotfix for this problem, but no luck. Can anyone point us in the right direction or is this a (huge) bug? Thanks Christian On Feb 3, 5:51=A0pm, CGuntsche <CGunts...@discussions.microsoft.com> wrote: > Hi everybody, > > today we noticed whe...

Query Needed 01-04-10
A sample of my data table looks as so: Underlying ID StrikePrice AAPL APVA10C155.00 155 AAPL APVM10P155.00 155 AAPL APVA10C160.00 160 AAPL APVM10P160.00 160 AAPL APVA10C165.00 165 AAPL APVM10P165.00 165 AAPL APVD10C160.00 160 AAPL APVP10P160.00 160 AAPL APVD10C165.00 165 AAPL APVP10P165.00 165 AAPL APVD10C155.00 155 AAPL APVP10P155.00 155 AAPL APVB10C155.00 155 AAPL APVN10P155.00 155 AAPL APVB10C160.00 160 AAPL APVN10P160.00 160 AAPL APVB10C165.00 165 AAPL APVN10P165.00 165 GS GPYA10C155.00 155 GS GPYM10P155.00 155 GS GPYA10C160.00 160 GS GPYM10P160.00 160 ...

Linq Query and Lambda
Hello, I am getting a list of regions as follows: return _context.Regions.Select(r => new RegionModel { Id = r.Id, Name = r.Name }).OrderBy(r => r.Name).AsQueryable(); However I need to get only the Regions which are related with Centers. So I need to check _context.Centers and get all used Regions Ids from each Center.Region.Id and then get all the Regions with those Ids ... Is this possible to do with a lambda expression ... I think it is possible but I am a little bit confused on this. Thank You, Miguel shapper wrote: > Hello...

3 queries into 1
Hello, I am going to try this again starting fresh from my other posts. I am trying to develop an all inclusive query on our inventory. Each fical term we start with new tables. I would like to take our Ending Inventory from our prior fiscal term and combine it with our current inventory balances. In years past I always took our ending inventory and posted them in our purchase order table to have our beginning balances. I thought it would be less time consuming to have a query to combine the 2. Below is a list of data and the query designs. PART # PRODUCT DESCRIPTION END B...

Query in Excell
I would like to create bolean statements in one or more of the columns that result from a query. Basically, I want to test a column for a factor and return a simple 'true' or 'false'. Using this I, hopefully, will eliminate the need to pass the data to Excel and perform the function there. ...

Lookup query value from Form
Hello. I have a form that I would like to lookup values in several taxt boxes. The values I want displayed are from several different Sum Queries. Example: I want to pull Total Working Blance from a query and monthly rent revenue from another query. The values all come from different tables. Is this possible? Thanks for the help. you can use a DLookup() function to retrieve the queries' values. read up on the function in Access Help, so you'll understand how it works. then add an expression to each textbox control's ControlSource property, as =DLookup("MyField", ...

Not in recent documents
My Visio 2000 document don't appear in my Win 2000 Recent Documenst list in the Start Menu ...