Simple / Complicated Query?

[z.ghulam]

Hi,

I am trying to create a query from a table which has an ID, Diary and
Date fields.
A particular ID may have several diary entries which all have their
specific date.

What I'm after is a query that will show me only the previous 3 diary
entries sorted by date.

Any thoughts would be greatly appreciated

Thanks
Zieshan

[banem2]

On May 30, 2:21*am, [e-mail] wrote:

Hi,

I am trying to create a query from a table which has an ID, Diary and
Date fields.
A particular ID may have several diary entries which all have their
specific date.

What I'm after is a query that will show me only the previous 3 diary
entries sorted by date.

Any thoughts would be greatly appreciated

Thanks
Zieshan

It goes something like this (change field names to match your table
field names):

SELECT tblDiary.ID, tblDiary.Diary, tblDiary.Date
FROM tblDiary
WHERE tblDiary.Date In (SELECT TOP 3 tblDiary.Date
FROM tblDiary
GROUP BY tblDiary.Date);

Regards,
Branislav Mihaljev
Microsoft Access MVP

[z.ghulam]

Thanks for that.

I've tried it out, but this brings up only 3 results. What I'm after
is 3 diary entries for each Issue Ref?
I've tried playing about with it, but to be honest my SQL skills are
pretty poor.
Can you help?

Cheers
Zieshan

[banem2]

On May 31, 12:04*am, [e-mail] wrote:

Thanks for that.

I've tried it out, but this brings up only 3 results. What I'm after
is 3 diary entries for each Issue Ref?
I've tried playing about with it, but to be honest my SQL skills are
pretty poor.
Can you help?

Cheers
Zieshan

Probably you have done something wrong. Using these data:

Diary, Date
a, 01/01/2008
b, 01/01/2008
c, 02/01/2008
d, 03/01/2008
e, 03/01/2008
f, 04/01/2008
g, 04/01/2008
h, 04/01/2008
i, 04/01/2008

above query returns:

Diary, Date
a, 01/01/2008
b, 01/01/2008
c, 02/01/2008
d, 03/01/2008
e, 03/01/2008

Five records in total, but only for top 3 dates.

Regards,
Branislav Mihaljev
Microsoft Access MVP