Max query

[dmkelgin]

Hi Guys,
Hopefully a nice easy question here.

I have a single table database. It contains the fields "Band", "Single" and "Sales".
Band contains the name of a band, single contains the name of a single released by that band, and sales is the number of copies of that single which was sold.

Each band has multiple singles.

I want to create a query which will display the name of the band, their highest selling single and the sales for this single - so only one record for each band.

I've tried various combinations using the max function without success. I want to be able to create this query in query design view.

I'm using access 2010 but would also like to use it in earlier versions.

All help much appreciated

 

[pr2-eugin]

Try something along the lines of,

SELECT musicTable.Band, musicTable.[Single], Max(musicTable.Sales) As MaxSales
FROM musicTable
GROUP BY musicTable.Band, musicTable.[Single];

 

[dmkelgin]

Thanks Paul. I tried that, but it doesn't work. It still returns all the records from the original table. I only want the highest selling single from each band.

I have been able to create a query with two fields - band and sales, and using "group by" for band and "max" for sales it does return the highest sales for each band, but I cant get it to display the single title along with these two fields.

 

[pr2-eugin]

Can you show your table actual structure? Does it not have an AutoNumber or unique ID field that can distinguish the records as a unique element?

 

[dmkelgin]

Table Name - Music

Field Name - "ID", Type - "Autonumber" - Primary key
Field Name - "Band", Type - "Text"
Field Name - "Single", Type - "Text"
Field Name - "Sales", Type - "Integer"

 

[pr2-eugin]

I am sure there is a better way of doing this, this is just my opinion.

SELECT musicTable.[Band], musicTable.Single, tmpTbl.MaxSales
FROM musicTable INNER JOIN (SELECT musicTable.[Band], Max(musicTable.Sales) AS MaxSales
FROM musicTable
GROUP BY musicTable.[Band])  AS tmpTbl ON (musicTable.Sales = tmpTbl.MaxSales) AND (musicTable.[Band] = tmpTbl.[Band])
GROUP BY musicTable.[Band], musicTable.Single, tmpTbl.MaxSales;

 

[dmkelgin]

That works, thanks Paul.

My next problem is I need to be able to explain this to a group of kids

If anyone knows a way to get this same effect without going into sql I'd really appreciate it.