Top 10?

[John]

Hi,

I need help setting up the following report:

I have a table listing the items we buy, and each has a unique ID (part
number).

Then I have a table listing bids for each part, one bid per record, and with
fields of part number (Many-To-One), the name/ID of the supplier, and the
bid price.

I need to make a report that lists each part number, and the suppliers with
the top 10 bids (in descending bid order). I'm not sure how to do this; if I
just needed one, I'd just use the Min(bid) feature, but now I need the
minimum and the next 9 as well. So a total of 11 columns (ID and top ten
suppliers). Any ideas how to do this?

Thanks in advance.

[Br@dley]

Re: Top 10?

John <noway@me.com > wrote:[color=blue]
> Hi,
>
> I need help setting up the following report:
>
> I have a table listing the items we buy, and each has a unique ID
> (part number).
>
> Then I have a table listing bids for each part, one bid per record,
> and with fields of part number (Many-To-One), the name/ID of the
> supplier, and the bid price.
>
> I need to make a report that lists each part number, and the
> suppliers with the top 10 bids (in descending bid order). I'm not
> sure how to do this; if I just needed one, I'd just use the Min(bid)
> feature, but now I need the minimum and the next 9 as well. So a
> total of 11 columns (ID and top ten suppliers). Any ideas how to do
> this?
>
> Thanks in advance.[/color]

SELECT TOP 10 Min([Bid]) FROM tblSuppliers;

OR

Open the query in design view. Go to View/Properties in the menu. Then
put a value of 10 in the Top Values property.

--
regards,

Bradley

A Christian Response

http://www.pastornet.net.au/response

[BillCo]

Re: Top 10?

if there are equal bids in the top ten, it will return more than ten
records - so you may want to limit it further somehow.

I once tied myself in knots creating a query that made a composit value
by taking the exact text opposite (a > z) of the client name and
appending it to the numeric value as a string and sorting that as top
ten alphabetically to make sure only 10 records were returned - anyone
know an easier way?

[polite person]

Re: Top 10?

On 27 Jul 2005 02:21:45 -0700, "BillCo" <coleman.bill@g mail.com> wrote:
[color=blue]
>if there are equal bids in the top ten, it will return more than ten
>records - so you may want to limit it further somehow.
>
>I once tied myself in knots creating a query that made a composit value
>by taking the exact text opposite (a > z) of the client name and
>appending it to the numeric value as a string and sorting that as top
>ten alphabetically to make sure only 10 records were returned - anyone
>know an easier way?
>[/color]
Include the id or some other unique item as a second ascending field in the select. But probably not very fair!

[Justin Hoffman]

Re: Top 10?

"polite person" <sitting@tv> wrote in message
news:42e76b75.1 05803016@localh ost...[color=blue]
> On 27 Jul 2005 02:21:45 -0700, "BillCo" <coleman.bill@g mail.com> wrote:
>[color=green]
>>if there are equal bids in the top ten, it will return more than ten
>>records - so you may want to limit it further somehow.
>>
>>I once tied myself in knots creating a query that made a composit value
>>by taking the exact text opposite (a > z) of the client name and
>>appending it to the numeric value as a string and sorting that as top
>>ten alphabetically to make sure only 10 records were returned - anyone
>>know an easier way?
>>[/color]
> Include the id or some other unique item as a second ascending field in
> the select. But probably not very fair![/color]

Use the Rnd function within the query to generate a random number so that
each time the query is run, any "joint top winners" stand an equal chance of
being selected.

[John]

Re: Top 10?

Thanks for all of these responses. I have 2 problems:
- The Top Values field allows me to set how many records are returned for
the *query.* But I need to choose how many values are returned *for each
part number* within the query. I.e. if there are 100 items, and I want the
10 lowest bids, I need the top 10 bids for each item, for a total of 1000
bids.

- I'll need to organize these bids so in the end I have one record per part
number, with 10 columns per p/n for the 10 lowest bids, so I can have a
report listing each part number and it's 10 lowest bids on one line. Any
ideas?

Thanks.

"Br@dley" <n0mail@4u.co m> wrote in message
news:RWDFe.6394 2$oJ.45193@news-server.bigpond. net.au...[color=blue]
> John <noway@me.com > wrote:[color=green]
> > Hi,
> >
> > I need help setting up the following report:
> >
> > I have a table listing the items we buy, and each has a unique ID
> > (part number).
> >
> > Then I have a table listing bids for each part, one bid per record,
> > and with fields of part number (Many-To-One), the name/ID of the
> > supplier, and the bid price.
> >
> > I need to make a report that lists each part number, and the
> > suppliers with the top 10 bids (in descending bid order). I'm not
> > sure how to do this; if I just needed one, I'd just use the Min(bid)
> > feature, but now I need the minimum and the next 9 as well. So a
> > total of 11 columns (ID and top ten suppliers). Any ideas how to do
> > this?
> >
> > Thanks in advance.[/color]
>
> SELECT TOP 10 Min([Bid]) FROM tblSuppliers;
>
> OR
>
> Open the query in design view. Go to View/Properties in the menu. Then
> put a value of 10 in the Top Values property.
>
> --
> regards,
>
> Bradley
>
> A Christian Response
> http://www.pastornet.net.au/response
>
>[/color]

[Br@dley]

Re: Top 10?

John <noway@me.com > wrote:[color=blue]
> Thanks for all of these responses. I have 2 problems:
> - The Top Values field allows me to set how many records are returned
> for the *query.* But I need to choose how many values are returned
> *for each part number* within the query. I.e. if there are 100 items,
> and I want the 10 lowest bids, I need the top 10 bids for each item,
> for a total of 1000 bids.
>
> - I'll need to organize these bids so in the end I have one record
> per part number, with 10 columns per p/n for the 10 lowest bids, so I
> can have a report listing each part number and it's 10 lowest bids on
> one line. Any ideas?
>
> Thanks.[/color]

Try something like this.....


select cat.id
, cat.descr
, subcat.id
, subcat.descr
, subcat.salesamt
from categories cat
left outer
join categories subcat
on cat.id = subcat.paretid
where cat.parentid is null
and subcat.id in
( select top 10
id
from categories
where parentid = cat.id
order by salesamt desc )
order
by cat.id
, subcat.salesamt desc

[color=blue]
> "Br@dley" <n0mail@4u.co m> wrote in message
> news:RWDFe.6394 2$oJ.45193@news-server.bigpond. net.au...[color=green]
>> John <noway@me.com > wrote:[color=darkred]
>>> Hi,
>>>
>>> I need help setting up the following report:
>>>
>>> I have a table listing the items we buy, and each has a unique ID
>>> (part number).
>>>
>>> Then I have a table listing bids for each part, one bid per record,
>>> and with fields of part number (Many-To-One), the name/ID of the
>>> supplier, and the bid price.
>>>
>>> I need to make a report that lists each part number, and the
>>> suppliers with the top 10 bids (in descending bid order). I'm not
>>> sure how to do this; if I just needed one, I'd just use the Min(bid)
>>> feature, but now I need the minimum and the next 9 as well. So a
>>> total of 11 columns (ID and top ten suppliers). Any ideas how to do
>>> this?
>>>
>>> Thanks in advance.[/color]
>>
>> SELECT TOP 10 Min([Bid]) FROM tblSuppliers;
>>
>> OR
>>
>> Open the query in design view. Go to View/Properties in the menu.
>> Then put a value of 10 in the Top Values property.
>>
>> --
>> regards,
>>
>> Bradley
>>
>> A Christian Response
>> http://www.pastornet.net.au/response[/color][/color]

--
regards,

Bradley

A Christian Response

http://www.pastornet.net.au/response