2 Records of unique field name

[tonydepo]

All, I know there is a very basic SQL, but I can't figure it out. I have one table named "dataset", which includes "Account_NO", "Device". "Date" and "Time". I would like to see only the first 2 records or less of each unique account number/Device.

Table looks like this:



Account_no Device Date Time
00007 123 3/1/2012 11:01:01
00007 123 3/2/2012 12:02:22
00007 123 3/3/2012 09:02:44
00010 335 02/02/2012 01:22:22
00010 655 02/01/2012 13:33:33

Result I am looking for:

00007 123 3/1/2012 11:01:01
00007 123 3/2/2012 12:02:22
00010 335 02/02/2012 01:22:22
00010 655 02/01/2012 13:33:33

etc...

Thanks for the help

[pbaldy]

Does this help?

http://support.microsoft.com/kb/153747/en-us

[tonydepo]

Tried it earlier today, but was getting error on

Set rs = db.OpenRecordset(SQL). I saw where there was a small SQL to perform based on 2 records per each similar account.

[tonydepo]

Trying to do a sub query, but it is not working.

SELECT a.ACCOUNT_NO, a.DEVICE_NO
FROM tblRemovalDataSet AS a
WHERE (((a.ACCOUNT_NO) In (Select Top 3 ACCOUNT_NO from [tblRemovalDataSet] where [ACCOUNT_NO]=a.[ACCOUNT_NO] and [DEVICE_NO]=a.[DEVICE_NO] Order By [ACCOUNT_NO] desc)));

Any help? It is not displaying only 3 items per same account_no and same device.... any ideas?

[orange]

SELECT A.Account_no, A.Device, A.DateTime
FROM AccountDev as A
WHERE A.DateTime IN
( Select top 2 datetime from accountdev as B
WHERE a.account_no = b.account_no)



Result:

Account_no	Device	DateTime
00007	123	03/01/2012 11:01:01 AM
00007	123	03/02/2012 12:02:22 PM
00010	335	02/02/2012 1:22:22 AM
00010	655	02/01/2012 1:33:33 PM

[orange]

In post #1 you want 2 items, in post# 4 it's 3. In your sample data you show the result you're looking for.

My solution above was to give you what you said you were looking for.