February 13, 2012 at 5:20 am
Hi,
I need the top and prior to top records from a table for each country. below is my test data.
can you pleaset tell me how we can get the desired result. In oracle we can get this using lead function, but in MS Access how to get it
table a
col1 col2 col3
us 01/01/2012 10
us 02/02/2012 11
us 03/03/2012 33
gpb 01/01/2012 20
gbp 02/02/2012 21
chf 01/01/2012 30
output should be
us o3/03/2012 33 02/02/2012 11
gbp 02/01/2012 21 01/01/2012 20
chf 01/01/2012 30
Thanks & regards
Naveen
February 13, 2012 at 10:09 am
Unfortunately there is no such function in Access. Two possible approaches you might consider:
1) open a recordset on your data and perform a rank calculation on each record using a VBA procedure and then select only records ranked on or two.
2) try using a self-join (Access allows that) with a combination of Max functions on the appropriate parameter.
Both of those may require that you have a unique key to identify a specific record.
Also, what do you want to do if you have several records of a given type that are all equal?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 13, 2012 at 10:16 am
as per your previous post......in Access design view...switch to SQL view...cut and paste following...assuming you have provided correct table/column names...if not modify as necessary
SELECT Q.col1, Last(Q.col2) AS Top1DATE, Last(Q.col3) AS Top1QTY, First(Q.col2) AS Top2DATE, First(Q.col3) AS Top2QTY
FROM (SELECT col1, col2, col3
FROM tableA
WHERE col3 IN
(select top 2 col3 from tableA as S
where s.col1 = tableA.col1
order by col3 desc
)
ORDER BY col1, col3 DESC) AS Q
GROUP BY Q.col1;
doesnt this work for you ?....didnt see any reply to my post on your first thread
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 14, 2012 at 12:57 am
Thanks very much its working fine, but where there is only 1 record for a group (in this case for chf), it should not display the same record for both
February 14, 2012 at 1:17 am
the input and output should be similar
table a
col1 col2 col3
us 01/01/2012 10
us 02/02/2012 11
us 03/03/2012 33
gpb 01/01/2012 20
gbp 02/02/2012 21
chf 01/01/2012 30
output should be
us o3/03/2012 33 02/02/2012 11
gbp 02/01/2012 21 01/01/2012 20
chf 01/01/2012 30
in this chf is having only 1 record so, in output the the other cols are blank
February 29, 2012 at 12:37 pm
Four steps
Query "step1":
SELECT a.col1, Max(a.col2) AS MaxOfcol2
FROM a
GROUP BY a.col1;
Query "step2":
SELECT a.col1, Max(a.col2) AS MaxOfcol21
FROM a LEFT JOIN step1 ON (a.col1 = step1.col1) AND (a.col2 = step1.MaxOfcol2)
WHERE (((step1.MaxOfcol2) Is Null))
GROUP BY a.col1;
Query "step3":
select * from step1
UNION ALL select * from step2;
Query "step4":
SELECT a.col1, a.col2, a.col3
FROM step3 INNER JOIN a ON (step3.col1 = a.col1) AND (step3.MaxOfcol2 = a.col2);
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply