Top 3 problem.


G

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
 
Ad

Advertisements

D

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
 
G

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
 
J

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
 
Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Top 3 Records 6
Selecting the top 3 records for a given value 1
Top Records 1
SELECT TOP 1
top Bar on ACCESS 2
Just the Top 20 2
Top Issues Database 3
Top N value 5

Top