If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Display last 5 records Query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Location: Oakland, CA
Posts: 126
Display last 5 records Query

I have a query with a date and want to display last 5 records, how do you do it. Think about 1 table with one field - date. How do you display last 5...? THANK YOU
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Mar 2004
Location: Adelaide, Australia
Posts: 32
Try this:

SELECT TOP 5 Dates FROM tblDates ORDER BY Dates DESC;

Seems to work.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Location: Oakland, CA
Posts: 126
Thanks, that definitely works, let me take this further. I would like to select SELECT TOP n number. I'm bring the n from another form and it doesn't work. Is there any reason why after top must be a hard coded integer and not a variable. Anybody knows a way around it?
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
use the generic "top n" syntax which involves a correlated subquery, and then you can prompt for the number

Code:
select foo
     , bar
  from yourtable zz
 where [enter "top" number] 
     > ( select count(*)
           from yourtable
          where bar > zz.bar )
in english: select a row only if the "top" number is greater than the number of rows that have a higher value than this row

stated differently: select a row if the number of rows that have a higher value than this row is less than the top number
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Location: Oakland, CA
Posts: 126
I do appreciate the input, it doesn't work though since the selection is in hunderds insted of last n record for the date. This is the code I put in, hopefully someone can help

SELECT [ITEM KEY], DATE
FROM [TBL:BATCHES] zz
WHERE [enter "top" number] > (select count(*) from [TBL:BATCHES] where DATE > zz.DATE)


Just to clarify. the code bellow worked great but the problem is that I want to have the integer 5 replaced with variable that asks the user how many records to display instead of solid 5 every time

SELECT TOP 5 [TBL:BATCHES].[ITEM KEY], [TBL:BATCHES].DATE
FROM [TBL:BATCHES]
WHERE ((([TBL:BATCHES].[ITEM KEY])="1240-121"))
ORDER BY [TBL:BATCHES].DATE DESC;
Reply With Quote
  #6 (permalink)  
Old
Cavalier King Charles
 
Join Date: Dec 2002
Location: Préverenges, Switzerland
Posts: 3,739
make a text box "topHowMany" and a button "butGo" on a form.

Code:
Private sub butGo_Click()
dim strSQL as string
strSQL = "SELECT TOP " & topHowMany & " [TBL:BATCHES].[ITEM KEY], [TBL:BATCHES].DATE FROM [TBL:BATCHES] "
strSQL = strSQL & "WHERE ((([TBL:BATCHES].[ITEM KEY])= '1240-121')) ORDER BY [TBL:BATCHES].DATE DESC;"
strSQL now does what you want.
you could then...
save it into a querydef
or set the .recordsource of a form to it
or set the .rowsource of a combo or list to it

whilst you are at it, you can also get 1240-121 from a text box. it is slightly different because this second one is text:
Code:
strSQL = strSQL & "WHERE ((([TBL:BATCHES].[ITEM KEY])= '" & anotherBox & "')) ORDER BY [TBL:BATCHES].DATE DESC;"
izy
__________________
currently using SS 2008R2
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Location: Oakland, CA
Posts: 126
I bet this is going to work, I have never saved to querydef, neither I know what it means. The way it was set up, I had the SQL written behind a report. Now I just can't figure it how to use it that way. Thanks all of you
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On