top values

  • Thread starter Thread starter newgroup
  • Start date Start date
N

newgroup

This is on eof the most frusting problems.

I have tried to recreate the microsoft solution however it does not seem to
work. I have two tables. One with brand, year and value of sales. I would
like top 10 values per brand per year. Can anyone advise the query setup and
in(select statement I need as Im going around and around in circles....) :-(

Thanks, Kerri
 
something like

Select top 10 brand, year, sum([value of sale]) from . . . . groupby brand,
year
 
Can you be a little more specific with your table and field structure/names?
Also, what is the primary key field in your tables?
 
Sorry....

Table one

Brand - Text
CHID - Text (primary)
Name - Text
RRP
etc

Table Two

CHID - Primary
Year - Number
Value - Currency

does this help?

Thanks again, Kerri
 
Have you tried to group the fields. Next, select the sort and change it to
either ascending or descending. After this click on the down arrow next to
the All on the toolbar. Select 10. Let me know if that works.

Emily
 
Hi there,

I have tried grouping etc with no success......

I have the following 'in' statement in value if this helps:-

In (Select Top 10 [sumofvalue] From [total values] Where [chid]=[selected
products]![chid] Order By [brand], [year], [sumofvalue] Desc)

thanks
 
Are these really your table and field names? What's up with "Table one" and
"Table Two"? Also, "Name" and "Value" are property names while "Year" is a
function. Do yourself a favor and kick it up a notch and use a better naming
convention/standard.

Try:
SELECT [Table Two].*
FROM [Table Two]
WHERE [Value] IN (SELECT TOP 10 [Value] FROM [Table Two] tt WHERE tt.CHID =
[Table Two].CHID ORDER BY [Value] DESC);
 
Thanks for your comments.

I used field names that were easy to understand, however I get your point.

Can you confirm where this state should appear? I am using a query view and
wont allow the statement to appear in the [VALUE] criteria box. Although if
just have the statement from IN (select... etc the query runs, albeit
incorrectly.

Thanks again, Kerri


Duane Hookom said:
Are these really your table and field names? What's up with "Table one"
and "Table Two"? Also, "Name" and "Value" are property names while "Year"
is a function. Do yourself a favor and kick it up a notch and use a better
naming convention/standard.

Try:
SELECT [Table Two].*
FROM [Table Two]
WHERE [Value] IN (SELECT TOP 10 [Value] FROM [Table Two] tt WHERE tt.CHID
= [Table Two].CHID ORDER BY [Value] DESC);


--
Duane Hookom
MS Access MVP
--

newgroup said:
Sorry....

Table one

Brand - Text
CHID - Text (primary)
Name - Text
RRP
etc

Table Two

CHID - Primary
Year - Number
Value - Currency

does this help?

Thanks again, Kerri
Click to expand...
Click to expand...
 
The criteria under the [Value] column/field should be
IN (SELECT TOP 10 [Value] FROM [Table Two] tt WHERE tt.CHID = [Table
Two].CHID ORDER BY [Value] DESC)

When you state "albeit incorrectly", you should provide details about why
you think it didn't work.

--
Duane Hookom
MS Access MVP


newgroup said:
Thanks for your comments.

I used field names that were easy to understand, however I get your point.

Can you confirm where this state should appear? I am using a query view
and wont allow the statement to appear in the [VALUE] criteria box.
Although if just have the statement from IN (select... etc the query runs,
albeit incorrectly.

Thanks again, Kerri


Duane Hookom said:
Are these really your table and field names? What's up with "Table one"
and "Table Two"? Also, "Name" and "Value" are property names while "Year"
is a function. Do yourself a favor and kick it up a notch and use a
better naming convention/standard.

Try:
SELECT [Table Two].*
FROM [Table Two]
WHERE [Value] IN (SELECT TOP 10 [Value] FROM [Table Two] tt WHERE tt.CHID
= [Table Two].CHID ORDER BY [Value] DESC);


--
Duane Hookom
MS Access MVP
--

newgroup said:
Sorry....

Table one

Brand - Text
CHID - Text (primary)
Name - Text
RRP
etc

Table Two

CHID - Primary
Year - Number
Value - Currency

does this help?

Thanks again, Kerri



Can you be a little more specific with your table and field
structure/names? Also, what is the primary key field in your tables?

--
Duane Hookom
MS Access MVP
--

This is on eof the most frusting problems.

I have tried to recreate the microsoft solution however it does not
seem to work. I have two tables. One with brand, year and value of
sales. I would like top 10 values per brand per year. Can anyone
advise the query setup and in(select statement I need as Im going
around and around in circles....) :-(

Thanks, Kerri
Click to expand...
Click to expand...
Click to expand...
 
Back
Top