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
1575 Views

Similar Articles

[PageSpeed] 35

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:

Import from web query problem
When importing from a web query the cell value <10E 6/L or <10^ 6/L does not import ie target cell is blank. Same happens whether the middle space is left in or out. Cells such as <100 import OK. Any suggestions as to what is happening here would be appreciated. Thanks. ...

DLookup on a query??
Hello, Just wondering if it's possible to use the DLookup function on a query instead of a table? Alan Don't worry about it. I got it to work. Thanks anyway. Alan "Alan" wrote: > Hello, > > Just wondering if it's possible to use the DLookup function on a query > instead of a table? > > Alan ...

HELP with queries!!!
Hi all, I am a new user to Access and need some help with setting up a query. I am building the framework to an inventory database. Currently I have one table that has the following information: serial # Status (checked in or checked out) equipment type (pc, network printer, standalone printer, scanner, and monitor) EE# (equipment number) model manufacturer work order# department (where the equipment is going) equipment location (the room number) relocation date comments I understand how to run the query, but I am trying to include drop down menu's in the query ins...

Convert datatype using query
I have create a query which combines two tables; ASSETS and UNITINFO. The UNITINFO table is linked from another database and the ASSETS table has a field that looks up a field called UNIT # from the UNITINFO table. Both fields are called UNIT # but the datatype on the UNITINFO table is set to Text and the UNIT # field on the ASSETS table is set to Number since it is a lookup field. I cannot change the datatype on the UNITINFO table to Number as the field contains both numbers and text. So when I ran my query that I created to pull data from both tables it gave me an error bec...

Web Query Not Working
Up until yesterday, my web query in my Excel worksheet has been working perfectly. I have been using the saved query named "Microsoft Investor Stock Quotes.iqy". It looks like the server accessed by the URL has been down. When I ping the domain, I get the message that the ping timed out. The link used by the query is http://investor.msn.com/external/excel/quotes.asp?SYMBOL=AA,fdx... Is anyone else experiencing the same problem? Does anyone know how to contact MSN to jog them to get things up and running? Or, have they stopped supporting the query? Gary Mine went d...

assigning a unique query for each textbox on a form
I've got a table (tblMain) with the following (relevant) fields: tblMain .FirstName .LastName .Seat .RefNum I've got a form that has a graphical display of the seats to which the people listed in tblMain are assigned. I'd like to put a text box on each seat corresponding to first and last name of the person assigned to that seat (stored in tblMain.Seat). The same database is used for various seatings so I'd like only those associated with the RefNum at hand to be called. I can write a simple enough query which I can call from VBA and which relies on a...

MFC based client server application query?
Hi experts I have developed an client server application using MFC sockets support. The server is a dialog based application & the client is an SDI application. I am having problem with sending data from client to server. After starting the server application, i start the client and try to connect & send data to server using the following code. void CMainFrame::OnConnect() { CSimpleSDIsockclientApp *pApp = (CSimpleSDIsockclientApp*)AfxGetApp(); int res = pApp->ClientSocket.Create(40000); if(res) { MessageBox("Client Socket at port no. 40000 Successf...

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 ...

Query Based DL [WILDPACKET]
I want to configure a QBDL so it sends message to all the Active Accounts in our Domain, is this possible? I guess when a message is sent to QBDL it also sends messages to the Disabled Accounts too, right? I aonly want the QBDL to send messages to Enabled Users. Please advise. Thank you On Mon, 5 Dec 2005 08:44:04 -0800, "WILDPACKET" <WILDPACKET@discussions.microsoft.com> wrote: >I want to configure a QBDL so it sends message to all the Active Accounts in >our Domain, is this possible? > >I guess when a message is sent to QBDL it also sends messages to the ...

Queries
I have the following query (for example): countofstates color 3 red 8 blue 7 orange 2 purple 9 yellow I would like to display this table on a report, but when I do it repeats 6 times. How do I only get this information to appear once? Thanks in advance. Try a Totals query... Color States GroupBy Count -- hth Al Campagna . Candia Computer Consulting . Candia, NH USA Microsoft Access MVP http://home.comcast.ne...

print a record
I have a multi user application and I would like the user to be able to print a report anytime after they have submitted the data. The user inputs data on a form. The user may need to print a report of that data a week later. Is there anyway for the user to view and/or print just the record they submitted? Shari The line of code for a command button on the form with the record to be printed: DoCmd.OpenReport "ReportName", acViewPreviw, , "IDField = " & Me.txtIDField where ReoportName is the report's name, IDField matches the ID field name and txtIDField ...

how to select certain records in a report.
Hi, I want to run a report - but I only want to select certain dates (I have a date field). Like, maybe, I want to run a report based on sales from Dec 8 - 12, 2009, for example. I'm not quite sure how to go about it. thanks! You are probably opening your report with a commqnd button on a form so add two unbound textboxes on the form named StartDate and EndDate, Then make the recordsource of the form a query that contains the date field. Set the criteria of the date field to: Between Forms!NameOfYourForm!StartDate And Forms!NameOfYourForm!EndDate Note - your form must...

Continues Form records selection
In a continues form, how do I "hide" a row. Example, in the form there's 4 records and #3 is clicked, I do not want to delete that record but hide it from view. Thanks. Create a table called, say, HiddenRecs, with a single field that is the same type as the ID of the records you want to hide. Set the data source of your form to: SELECT Field1, Field2, etc. FROM YourTable WHERE ID NOT IN (SELECT ID FROM HiddenRecs). In your Form_Load event, delete all the records from this table. When a record is clicked, the click event should insert the ID of the current record into ...

Append New Record Via A Form?
Have never used Append Queries and therefore lack the knowledge of the same. I have a sample table "Students" with fields like: 1. ID (AutoNumber) 2. Name (Text) 3. Nationality (Lookup with Row Source being Selection from the table "Countries") 4. Date of Birth (Date) Now I want to create a form which would not be showing the records already in the database but simply present a one page layout with the 4 controls for each of the fields and a button which upon being clicked shall add the data entered in the same to the Table "Students". Please c ...

Where are the Forms and Queries in 2007
In Access 2003 if you wanted to open your existing Forms and Queries you clicked on the Forms button. In Access 2007 this option seems to be missing. My question is how do I access the Forms,Tables and queries. The only forms button I have found creates new forms, I want to access my existing forms. Many thanks for any advice. This is one of those times when a picture paints a thousand words, so here's a picture ... http://brenreyn.brinkster.net/navpane.jpg -- Brendan Reynolds "Ernmander" <Ernmander@discussions.microsoft.com> wrote in message news:4CDA479B-BFA6...

can't delete record
I am trying to use a command button on a form to delete the current record displayed on the form. The record has several related fields in other tables, but I believe I have successfully deleted all related records with delete queries. When I run the code DoCmd.RunCommand acCmdDeleteRecord the confirm delete message comes up, and after clicking through it the form goes blank, appearing like a successful deletion. No error messages. However the record is still in the table. In table view there isn’t any problem deleting the record in question i.e. no related records. I’ve tried using a w...

find affected tables when creating a new record in CRM frontend
How can I find out what tables which new record has been added on when I enter data through a form in CRM front end? I try to find way to map form in the CRM frontend to the tables at the CRM database at backend? It is CRM 3.0 Thank in advances for help! On Nov 9, 12:12=A0pm, VistaUser123 <sunmapleleaf...@yahoo.com> wrote: > How can I find out what tables which new record has been added on when I > enter data through a form in CRM front end? I try to find way to map form= in > the CRM frontend to the tables at the CRM database at backend? It is CRM = 3.0 > > Thank in...

Query Builder (SQL View)
I am trying to join 2 tables. I want to join if 'Field B' is in 'Field A'. ie. Field A = 'ABCDE' Field B= 'BC' ....the join would be successful cause 'BC' is in 'ABCDE'. Is this possible? I tried... SELECT FROM table1 INNER JOIN table2 ON table1.name like (% table2. partial_name %); Keep getting errors. Any help greatly appreciated! Warren Depending on the "flavor" of SQL you are using SELECT * FROM table1 INNER JOIN table2 ON table1.name like '%' & table2.partial_name & '%' Or SELECT * FROM table1 IN...

Unique records in Excel
Hi does anyone know how to ensure Unique data when making entries in an Excel spreadsheet ? Say Part Numbers or account numbers etc Thanks :confused: -- gilsondg ------------------------------------------------------------------------ gilsondg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15878 View this thread: http://www.excelforum.com/showthread.php?threadid=273641 There's a method using Data Validation and COUNTIF. Debra Dalgleish's website has an example of how to set this up: http://www.contextures.com/xlDataVal07.html HTH Jason Atla...

Ole objects in records/tables
I have a question every time I tried to go to word and powerpoint and says type a prodect key I typed from the back of my labtop but it keeps saying it wrong.So what do I do? WHAT??????? "Kevin Ramos" <kevin's email> wrote in message news:%23X$8BdeQIHA.2268@TK2MSFTNGP02.phx.gbl... >I have a question every time I tried to go to word and powerpoint and says >type a prodect key I typed from the back of my labtop but it keeps saying >it wrong.So what do I do? ...

Access Deleting a Query SQL
Hi all, I am working with a multiple parameter search query. The form I am using to conduct the search displays the results in a subform. I have gone through and really combed through the SQL to make sure there are no blatant spelling errors or incorrect references within the code. Anyways when I open the form that it is attached to, I keep getting the boxes described in this website http://allenbrowne.com/bug-13.html .. The form is open, and I even changed names of fields in the table in case SQL was choosing to be picky about what I named my fields. Is there anything else that would cau...

Help required in Update query
Hi, pls help me here table t1 has 3 fields, and data is as below name , grade , marks n1 a 10 n2 b 20 n3 c 30 n4 a 30 n5 c 10 n6 n7 40 now i want an update query where 2 names should be swapped. like n1 is updated as n2, and n2 updated as n1. i tried to put update query, but problem is if n1= n2 then we have 2 n2's in the table then again n2=n1 tried then we have 2 n1's , i want both names should be interchanged, any help STEP 1: BACKUP you...

If statement to export query if record count is not null
Greetings, thank you very much to all who are reading this. Basically I have an automated email that goes out every Monday. Very simply it uses sendobject to email a query. I would like to add some logic that basically looks at the record count and if it isn't null...sends teh email as is...if the record count is empty I would like to have it send an email that basically says "The record set this week is empty." Below is the module converted from the original macro. I was having trouble modifying the module so any help would be greatly appreciated. Thanks in ...

Find Record in Subform
I currently have a find record command button on the main form. When I use the main form find button it only searches fields in the main form. I would like the ability to search ALL the records in a subform using a find record command button. Currently when I put a find record button on the subform it only searches the record currenly displayed. Is there any way to seach (for example) ALL the Titles in that subform table? On Nov 5, 2:43 pm, Reb <RebKayGom...@gmail.com> wrote: > I currently have a find record command button on the main form. When I > use the main form find button...

Prefill Records in Form
I have a table with the 3 columns attached to a form 1 - Experience 2 - Yes/No 3 - Role I want to prefill the experience column with 15 entries for the user to complete when they open the form. Is there a way to do this in Access? Joe, Are you saying you want to provide the user with 15 choices from which to opick one to go in Experience? If so, create a table with the 15 choices and then place a combobox based on that table in the form with Experience as the record source. Stalin wrote: >I have a table with the 3 columns attached to a form > >1 - Experience > >2 - Yes/N...