Select Top 5 query across 3 tables

[afbutlerandassoc]

Hi guys & gals, sorry for the seemingly stupid question. Looks like I'm lost here. I have a set of tables that feed info to each other:

vendors-table, country-code, rates-by-vendor

I need to run a query against the Rates-by-vendor table which lists the same country up to 15 times with various rates by different vendors. I tried a select top 5 query as follows:

SELECT Top 5 [Rate table by Vendor].[Dial reference #], [Rate table by Vendor].Rate, [Rate table by Vendor].[Vendor #]
FROM [Rate table by Vendor]
GROUP BY [Rate table by Vendor].[Dial reference #];

My error message is: "You tried to execute a query that does not include the specified expression 'Rate' as part of an aggregate function.

I'm lost - I'm new to SQL so I have no ideas - any help would be appreciated x-)

 

[tlbroadbent]

If you include and aggregate predicate (sum, count, min, max, group by, etc.) in you query, then all items in the select list must be part of an aggregate function. In your query you did Group By on [Rate table by Vendor].[Dial reference #] but did not include [Rate table by Vendor].Rate And [Rate table by Vendor].[Vendor #] in agregate functions.

Perhaps you meant to use Order By rather than Group By. Or you need to add additional aggregate functions.

SELECT Top 5 [Rate table by Vendor].[Dial reference #], Max([Rate table by Vendor].Rate) As MaxRate, [Rate table by Vendor].[Vendor #]
FROM [Rate table by Vendor]
GROUP BY [Rate table by Vendor].[Dial reference #], [Rate table by Vendor].[Vendor #]; Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[MichaelRed]

My guess is te same as Terry's - just later.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[afbutlerandassoc]

Hi again, thanks for your input, it works, but I guess I explained things incorrectly. The table has a list of up to 15 countries, with a corresponding rate and vendor for each rate. I need to get the top 5 rates for each country, showing me the rate and vendor for each set of 5 similar countries. I can do a standard query that sorts them as I noted, listing all of the countries, no matter how many there are:

SELECT [Rate table by Vendor].[Dial reference #], First([Rate table by Vendor].Rate) AS FirstOfRate, [Rate table by Vendor].[Date last changed], First([Rate table by Vendor].[Vendor #]) AS [FirstOfVendor #], [Vendor table].[Vendor Name]
FROM [Vendor table] INNER JOIN ([DialCodes reference table] INNER JOIN [Rate table by Vendor] ON [DialCodes reference table].[Dial reference #] = [Rate table by Vendor].[Dial reference #]) ON [Vendor table].[Vendor #] = [Rate table by Vendor].[Vendor #]
GROUP BY [Rate table by Vendor].[Dial reference #], [Rate table by Vendor].[Date last changed], [Vendor table].[Vendor Name], [Rate table by Vendor].[Primary key]
ORDER BY [Rate table by Vendor].[Dial reference #], First([Rate table by Vendor].Rate);

This is sort of ok, but I need to isolate the top 5 for each country. Next I'll be looking to output them so that each country is listed along the left most column, and the top 5 are reflected in a row from left to right beside each country name (Similar to what can be done in Excel) I'm lost, and greatly appreciate your assistance (-:

 

[MichaelRed]

Do seperate queries for the top 5 in each country. Do a union query of the the seperate countries. Transpose this to get your layout.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[afbutlerandassoc]

Thanks MichaelRed & Terry - now, how do I add the per country query? I don't understand how to get a simple query and then transpose them. Any suggestions? Thank you, thank you, thank you......

 

[MichaelRed]

tthe per country query would just take the list of (unique) countries in your table ("[Rate table by Vendor].[&quot. From this list, do a query for the fields you need

("[Rate table by Vendor].[Dial reference #], First([Rate table by Vendor].Rate) AS FirstOfRate, [Rate table by Vendor].[Date last changed], First([Rate table by Vendor].[Vendor #]) AS [FirstOfVendor #], [Vendor table].[Vendor Name]&quot

For each country in the "list". Order by the [Rate] field and select the TOP 5.

Next, do a uniou query of all of the "country" queries.

The transpose will depend on the ver of Ms. Access you are using. ver 2K has a "Piviot Table" which works nicely for this. In '97 and previous you will need to some 'creative' usage of the crosstab query, basically, doing a series of them and then a 'master select' to join the columns back together.

Although there are quite a few steps involved, if the process is set up in a module it is really more of an intellectual issue than a pregramatic one.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over