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]

John <no***@me.com> wrote:

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.

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]

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]

On 27 Jul 2005 02:21:45 -0700, "BillCo" <co**********@gmail.com> wrote:

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?

Include the id or some other unique item as a second ascending field in the select. But probably not very fair!

[Justin Hoffman]

"polite person" <sitting@tv> wrote in message
news:42e76b75.105803016@localhost...

On 27 Jul 2005 02:21:45 -0700, "BillCo" <co**********@gmail.com> wrote:

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?

Include the id or some other unique item as a second ascending field in
the select. But probably not very fair!

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]

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" <n0****@4u.com> wrote in message
news:RW******************@news-server.bigpond.net.au...

John <no***@me.com> wrote:

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.

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

[Br]

John <no***@me.com> wrote:

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.
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

"Br@dley" <n0****@4u.com> wrote in message
news:RW******************@news-server.bigpond.net.au...

John <no***@me.com> wrote:

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.

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

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response