Top 3 problem.

[George Avery]

Hello,

I'd appreciate it if someone can point me in the right direction.

I have an Access front end with tables linked to two SQL Server2005
databases both on the same server. I want to report against two of the
tables and get the most recent 3 records for each establishment.

There are four fields for each record in my query:-
1. Database1.Estab
2. Database1.Name
3. Database2.Date
4. Database2.Outcome
each table has a field [Estab] which I use for joins.

Any ideas please?

GA

 

[Douglas J Steele]

Take a look at what Allen Browne has at
http://www.allenbrowne.com/subquery-01.html#TopN

"George Avery" wrote in message

Hello,

I'd appreciate it if someone can point me in the right direction.

I have an Access front end with tables linked to two SQL Server2005
databases both on the same server. I want to report against two of the
tables and get the most recent 3 records for each establishment.

There are four fields for each record in my query:-
1. Database1.Estab
2. Database1.Name
3. Database2.Date
4. Database2.Outcome
each table has a field [Estab] which I use for joins.

Any ideas please?

GA

 

[George Avery]

Thanks for that. I saw this before posting and spent several hours
trying to get the sub query to work. The query ran but returned all
records so the syntax was OK but I assume my logic was wrong.

I also tried creating a view on the SQL server but couldn't get my
reference from database1 to database2 to work.

I think I just need to leave it alone for a day or two and come back
to it with a fresh mind when I go in next week :^)

It was just a chance that there might be a suggestion to try something
completely different.

Thanks - GA

 

[John W. Vinson]

Thanks for that. I saw this before posting and spent several hours
trying to get the sub query to work. The query ran but returned all
records so the syntax was OK but I assume my logic was wrong.

Post the SQL of the query you tried. Someone may see the issue.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[George Avery]

Post the SQL of the query you tried. Someone may see the issue.

Thanks for that.

I've got it working now by simplifying things and doing the top three
in an intermediate query that only 'looks' at one table and then
getting the establishment detail in a 'final' query.

GA