Query design to return only a determined number of records

[aleameglio]

Hi everybody,
I need to design a query that returns me a determined number of records (i.e. the last 3 records, according to a date). To be more specific, this is the table I have to design the query for:

WELL	DATE	OIL	WTR	GAS
BG-1070	10-07-13	3.5	10	2300
BG-1070	12-06-13	3.4	9.8	2100
BG-1070	03-06-13	2.8	10.2	3000
BG-1062	14-07-13	5.4	1	950
BG-1062	01-07-13	5.1	0.8	800
BG-1048	25-06-13	10.1	3.2	350
BG-1048	22-06-13	10.5	3.1	350
BG-1048	11-06-13	9.9	4	420
BG-1048	02-06-13	10.5	3	330

What I need is to design a query that returns me the last 2 records for each well (i.e. last 2 records, I am showing here a part of the table, since there is at least 20 records per well).
As an example the query needs to select the last 2 records for each well and should results like this:

WELL	DATE	OIL	WTR	GAS
BG-1070	10-07-13	3.5	10	2300
BG-1070	12-06-13	3.4	9.8	2100
BG-1062	14-07-13	5.4	1	950
BG-1062	01-07-13	5.1	0.8	800
BG-1048	25-06-13	10.1	3.2	350
BG-1048	22-06-13	10.5	3.1	350

Any help will be great.
Thanks in advance,

[ItsMe]

SOunds like you will have to use DAO and .MoveNext or something. YOu would have to grab each record and build a string, then build out a string array of your three strings/records.

Another option may be to find the latest record by PK. As long as you don't allow for deletions from the table you could just add the largest PK-4 to a WHERE clause in a SELECT query.

[Dal Jeanis]

Select T1.MyWell, T1.MyDate, T1.MyOil, T1.MyWtr, T1.MyGas
FROM MyTable AS T1
WHERE T1.MyDate IN
   (SELECT TOP 3 T2.MyDate
    FROM MyTable AS T2
    WHERE T1.MyWell = T2.MyWell
    ORDER BY T2.MyDate DESC)
ORDER By T1.MyWell, T1.MyDate DESC;

[ItsMe]

Or you will have to use SELECT TOP like Dal demonstrated

I stand corrected.....

[dr4ke1990]

Would it be feasible for you to have a form with date 'drop downs' and you could tell the query to only retrieve data inbetween those dates?