Top 100

[Jez]

Hi, Please Help...

I am wanting to build a query that will show the Top 100 parts ordered.

My query has a list of all the products, qty, EngineerID and Contract he is
on, with Product Group

How can I show by contract & engineer the Top 100 parts ordered?

Thanks,
Jez

 

[geebee]

hi,

The basic syntax is ...

SELCT TOP 100
partname, contract, engineer
FROM tbl_name
GROUP BY
contract, engineer

hope this helps,
geebee

 

[Phil Smith]

Two queries. The first one is a top 100 query.

Build a query sorted so that it goes top to bottom. Then, right click
in the area with the tables, and choose properties. Where you see Top
values, enter 100. This query will now give you the top 100 results.

Then create a second query, based on the first query, to resort and
group it by Engineer and contract.

 

[Jerry Whittle]

See if this will work. You'll need to put in the proper table name twice.

SELECT T1.EngineerID,
T1.Contract,
T1.products,
T1.qty
FROM YourTableName AS T1
WHERE T1.[qty] In
(SELECT TOP 100 T2.[qty]
FROM YourTableName AS T2
WHERE T2.[qty] = T1.[qty]
AND T2.[products] = T1.[products]
ORDER BY T2.[qty] DESC);

 

[Jez]

Jerry, Thanks for that. It worked just like I wanted...

My next problem I have found is when I delete the Qty as found thats not
what I was after. I am wanting to count the number of products instead.

I tried to replace the Qty with just the Products and this doesnt give me a
count of all the same product, just gives me a count of 1 each time.

How can I measure the Top100 products

Jez

See if this will work. You'll need to put in the proper table name twice.

SELECT T1.EngineerID,
T1.Contract,
T1.products,
T1.qty
FROM YourTableName AS T1
WHERE T1.[qty] In
(SELECT TOP 100 T2.[qty]
FROM YourTableName AS T2
WHERE T2.[qty] = T1.[qty]
AND T2.[products] = T1.[products]
ORDER BY T2.[qty] DESC);
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi, Please Help...

I am wanting to build a query that will show the Top 100 parts ordered.

My query has a list of all the products, qty, EngineerID and Contract he is
on, with Product Group

How can I show by contract & engineer the Top 100 parts ordered?

Thanks,
Jez