last three records in a query

[clientuser]

how would i do a query to pull the last three records from a given criteria..

I have a support database for calls, and i need to pull the last three records for each call that i input the criteria for ...

any suggestions?

 

[BNPMike]

You could turn the query into a maketable query. Order it in descending date order. Thus the first three records are the most recent entries. Add an autonumber column say recid and then do a second query with where recid < 4

 

[GIA]

You might back door it as follows: use

SELECT TOP 3 ...

after reversing the sort sequence. Of course, this presumes that you can sort the 3 in question to the top (or bottom ), which should be achieveable - if not by a time-logging field, then by a sequence counter.

In fact, by golly, you may have come up with the first _truly_ legitimate use for autonumber I've ever seen. Every, and I do mean every, case I've seen someone use or suggest it ... involved kluges and compromises. Yours may be the exception. &quot;Outside of a dog, a book is probably man's best friend; and inside a dog, it's too dark to read&quot; - G. Marx

 

[mac318]

Gates, I routinely use the autonumber field for security purposes. A user can lie about the time s\he accomplished a task; but, I can put the entries in the sequence in which they were entered; even if the user reset the system clock to cheat!!!

mac

 

[GIA]

Thanks for weighing in - yes, that's a good one. :-9 &quot;What is your favourite colour?&quot; &quot;Blue! No! Yell&quot;

 

[clientuser]

I changed the top value in the queries properties to &quot;3&quot; but when i run the query it of course only brings back 3 records max when I know there are more..

I found this code example in access but i do not understand the second line of code in it?

strGetSQL = &quot;SELECT TOP 3 Event.[Event.ID] &quot; _

<~~~~~~~~~~~This part I do not understand~~~~~~~~~~~~~~~>
& &quot;AS TenMostExpensiveProducts, Products.UnitPrice FROM Products &quot; _

<~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~>
& &quot;ORDER BY Event.[AddDate] DESC;&quot;

Care to elaborate on this one?

 

[GIA]

Others should pipe in as to the best method for you to learn SQL (vs. the piecemeal living-on-the-edge education-by-error I experienced). That learning which really should be a prerequisite to messing around with &quot;TOP n&quot;.

But short of learning it the &quot;right&quot; way, one thing you can do is flip back and forth between the SQL view and regular Access query design view (under &quot;View&quot; on the menu when you're in query design mode). You'll see that as you change (in &quot;design view&quot the query output field name (or input field), or the source table, the SQL is changed accordingly.

As such, you'll be able to answer your own question above.

All the same, learn SQL formally. See if your firm will pay -------
&quot;What is your favourite colour?&quot; &quot;Blue! No! Yell&quot;

 

[clientuser]

yeah that post was helpful (can you hear the saracasim in it).. as i would love to learn sql &quot;formally&quot; with job layoffs and cutbacks, its difficult to convince an already dwindling company to send anyone to school. now i could go on my own (which is in the works) to school and learn, but at the moment all I am asking is for a little bit of help to finish a project of mine.

Which brought me to this forum. Which the last time I checked was an area to post questions that we could not fiqure out on our own. after going through and looking at design/view in queries, looking at helpfiles etc, I (being the uneducated one) looked to the educated ones in this forum to shed some light on my issue. Telling me &quot;go learn it yourself&quot; is a cop out and not informative at all.

I have always been one to help out in anyway I can and cannot stand those that stand around saying &quot;ummm.. yeah I can tell you, but then you wouldnt learn&quot; Well if I dont understand it from the beginning and no one is willing to help me, how I am suppose to learn... Almost as bad as companies requiring 10 years of experience from individuals but will never give anyone a chance to start from ground level... Some good potential people could be passed by...

Anyway, thanks for the informative post, it has allowed me to solve my problem, which is look to someone else for advice.

 

[GIA]

No problem. I only spent about 20 minutes of my time giving you the original answer, which WAS the answer, right? ... (hmmm, what's my hourly rate... oh, nm).

I was pretty proud of that answer, too - I'm not 95th percentile material on everything I do, but on that question I believe I was.

I assumed you knew SQL when I answered. I didn't expect I'd need get into those basics. Basically, I just don't have time to answer the bonus question, sorry.

Sorry to hear that it's rough out there. But I stand by my advice: if you're going to mess with SQL specifics, uhhh, well, kind of learn SQL. I can't teach you how to do SQL in the time that I have to answer this post. But you'll be grateful when you do learn it, if you want to be an exotic Access problem-solving guru.

Good luck - really. -------
&quot;What is your favourite colour?&quot; &quot;Blue! No! Yell&quot;

 

[clientuser]

If you do a select TOP, in this case in the queries properties and setting TOP VALUES to: 3 the query only pulls back 3 records, which in this case I know have 87..

Is there something else in the query that needs to be set to show the other records as well..???

 

[clientuser]

no one else has any suggestions on this???