Searching at the last two records

[Mehvan]

Hello Everyone



I want to make a query searching at the last two records only for each worker. on condition the last record is less than before. In other words, if the worker received 200 weeks ago and received 300 today, it will not be included in the result of the query. But if received a week ago 300 and today received 200 is displayed. (Search only in last two records)

for example :

Ali 200$ 01/02/2018
Ali 250$ 10/02/2018
Ali 300$ 19/02/2018
Ali 200$ 28/02/2018

Now Ali sent 300$ in 19/02/2018 , it must not show me 200$ in 28/02/2018 , I want to show me only 300$ in 19/02/2018 , if be more than 19/02/2018 it must show me on last record

[CJ_London]

you'll need to use a subquery to find the last two, and your table needs to have a primary key.

SELECT TOP 1 *
FROM myTable
WHERE PK=(SELECT TOP 2 PK FROM myTable T WHERE myName=myTable.myName ORDER by MyDate Desc)
AND myName='Ali' 
ORDER BY myDate Asc

[ranman256]

...except when it was raining on a tuesday on a full moon.

Q1, get the last record by asking for the MIN(date), select [clientID],Min(date) from tData
Q2, get the next record by asking for the Min(date) but tData.date > Q1.date
Q3, put Q1 & Q2 , and tData table in a query , join on clientID, and set Q2.date > Q1.date...

(this NOT exact sql)
like select tData.* from tData, Q1,Q2 where tData.ClientID =Q1.clientID = Q2.clientID where Q2.date > Q1.date

[June7]

Consider:

Query to retrieve TOP 2 records per group from table: Top2

SELECT Table1.ID, Table1.EmpName, Table1.Amount, Table1.DateRcd
FROM Table1 WHERE ID IN (SELECT TOP 2 ID FROM Table1 AS Dupe
WHERE Dupe.EmpName=Table1.EmpName ORDER BY Dupe.DateRcd DESC);

Query to retrieve the TOP 1 record per group from Top2:

SELECT Top2.ID, Top2.EmpName, Top2.Amount, Top2.DateRcd
FROM Top2 WHERE ID IN (SELECT TOP 1 ID FROM Top2 AS Dupe
WHERE Dupe.EmpName = Top2.EmpName ORDER BY Dupe.Amount DESC);

[Mehvan]

I don't want to give me a MIN Date before last record
maybe I will have so much workers and they will take 4 times salary at the Month , I mean every week they will take part of Salary
When we use LAST in Query , it will gives us last date he/she takes a part of salary
but MIN it will gives us which day He/She took less salary in this Year
it will gives me a mistake result

I have two Tables :
1 - Workers ( ID , WorkersName , Salary )
2 - WorkersDetails : (ID , Amount , Date) , I mean tblDetails will be subQuery for the first Table .

[Mehvan]

Excuse me June7 , but it doesn't give me too my result

[orange]

In your example

Ali  200$   01/02/2018
Ali  250$   10/02/2018
Ali  300$   19/02/2018
Ali  200$   28/02/2018

the logic , as per ajax's response, is
Select the top 2 records with the greatest date desc.
And in that pair, select the top 1 with greatest dollar value.

Date and Amount are reserved word in Access.
Good luck

[June7]

And that is the logic applied in my approach according to description in original post.

If that is not what you want then provide a sample of what the desired output should look like. Perhaps also a larger input sample using several names.

[Mehvan]

I'm sorry for late replied , because I was so busy at work today

you're bring the second one , and I want one before the last record of that be larger than last

Ali 200$ 01/02/2018
Ali 250$ 10/02/2018
Ali 300$ 19/02/2018
Ali 200$ 28/02/2018

in this example , 300$ 19/02/2018 is larger than 200$ 28/02/2018 , it must gives me 300$ 19/02/2018 , not gives me 10/02/2018
if I give Him 150$ in 05/03/2018 , it must gives me 28/02/2018 and so on .

[CJ_London]

so looks like the code provided in post #2 does what you require, probably the others as well. Try them and let us know how you get on

[orange]

??? I am not understanding your comment.

As I posted in post 7
========
The logic is:
a)Select the top 2 records with the greatest date desc.
b)And in that pair, select the top 1 with greatest dollar value.

So using your data

Ali 200$ 01/02/2018
Ali 250$ 10/02/2018
Ali 300$ 19/02/2018
Ali 200$ 28/02/2018

The latest top 2 dates (a) are

Ali 300$ 19/02/2018
Ali 200$ 28/02/2018

The greatest top 1 Dollar Value of this pair (b) is

Ali 300$ 19/02/2018

[Mehvan]

Thanks for everybody it's working now