TOP 10 Parts Prefix

  • Thread starter Thread starter juan
  • Start date Start date
J

juan

Hello,
I have a table that contains 1000's of part numbers. I
want to get top 10 parts by 4 digits prefix . If I use the
Top value, it just gives me top 10 records. Here's the SQL
view
SELECT TOP 10 Left([MATERIAL],4) AS MATERIAL1,
NEW_ZMATX.MATERIAL
FROM NEW_ZMATX
GROUP BY Left([MATERIAL],4), NEW_ZMATX.MATERIAL;
I want to get top 10 parts for all those parts in the
table.
Hope this makes sense. I appreciate all the help.

Thanks,
Juan
 
HI,


It is not clear what are the fields name. So, I will transpose into finding
the top most salesmen, by country:

--------------
SELECT salesman, sale, country

FROM myTable As a

WHERE salesman IN( SELECT TOP 10 b.salesman
FROM myTable as b
WHERE a.country=b.country
ORDER BY b.sale DESC )

ORDER BY country, sale DESC
 
Hello Michael,
Sorry if I wasn't clear here are my fields:
PART1:Left([PART],4) DISTI_PART MAT_ENTERED

The TOP 10 might have been misleading. Since there is no
field that provides a number to get TOP. Basically, I want
to get the first 10 parts for each part prefix.
Example,
prefix PART
XXZZ XXZZ2547
XXZZ25TY
etc
ZZRR ZZRR658TY
ZZRR985XTY
etc
RRXX RRXX69852
RRXX9985Z
etc
Hope this is clearer. I appreciate any info. Trying to
modify what you provided but can't seem to get it to work.
I think my problem is getting the right syntax in the sub
query.

Thanks,
Juan
-----Original Message-----
HI,


It is not clear what are the fields name. So, I will transpose into finding
the top most salesmen, by country:

--------------
SELECT salesman, sale, country

FROM myTable As a

WHERE salesman IN( SELECT TOP 10 b.salesman
FROM myTable as b
WHERE a.country=b.country
ORDER BY b.sale DESC )

ORDER BY country, sale DESC
--------------


Hoping it may help,
Vanderghast, Access MVP



juan said:
Hello,
I have a table that contains 1000's of part numbers. I
want to get top 10 parts by 4 digits prefix . If I use the
Top value, it just gives me top 10 records. Here's the SQL
view
SELECT TOP 10 Left([MATERIAL],4) AS MATERIAL1,
NEW_ZMATX.MATERIAL
FROM NEW_ZMATX
GROUP BY Left([MATERIAL],4), NEW_ZMATX.MATERIAL;
I want to get top 10 parts for all those parts in the
table.
Hope this makes sense. I appreciate all the help.

Thanks,
Juan
Click to expand...


.
Click to expand...
 
Hi,


SELECT Left(part, 4), part
FROM myTable As a
WHERE part IN(SELECT TOP 10 b.part
FROM myTable As b
WHERE Left(a.part, 4) = Left(b.part, 4))
ORDER BY Left(part, 4)


Since there is no "order", defining the items kept in the sub-lists, I just
removed the clause, in the sub-query.

Hoping it may help,
Vanderghast, Access MVP


juan said:
Hello Michael,
Sorry if I wasn't clear here are my fields:
PART1:Left([PART],4) DISTI_PART MAT_ENTERED

The TOP 10 might have been misleading. Since there is no
field that provides a number to get TOP. Basically, I want
to get the first 10 parts for each part prefix.
Example,
prefix PART
XXZZ XXZZ2547
XXZZ25TY
etc
ZZRR ZZRR658TY
ZZRR985XTY
etc
RRXX RRXX69852
RRXX9985Z
etc
Hope this is clearer. I appreciate any info. Trying to
modify what you provided but can't seem to get it to work.
I think my problem is getting the right syntax in the sub
query.

Thanks,
Juan
-----Original Message-----
HI,


It is not clear what are the fields name. So, I will transpose into finding
the top most salesmen, by country:

--------------
SELECT salesman, sale, country

FROM myTable As a

WHERE salesman IN( SELECT TOP 10 b.salesman
FROM myTable as b
WHERE a.country=b.country
ORDER BY b.sale DESC )

ORDER BY country, sale DESC
--------------


Hoping it may help,
Vanderghast, Access MVP



juan said:
Hello,
I have a table that contains 1000's of part numbers. I
want to get top 10 parts by 4 digits prefix . If I use the
Top value, it just gives me top 10 records. Here's the SQL
view
SELECT TOP 10 Left([MATERIAL],4) AS MATERIAL1,
NEW_ZMATX.MATERIAL
FROM NEW_ZMATX
GROUP BY Left([MATERIAL],4), NEW_ZMATX.MATERIAL;
I want to get top 10 parts for all those parts in the
table.
Hope this makes sense. I appreciate all the help.

Thanks,
Juan
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top