Query to adjust table

[celestedeeva]

Hello all,

I have the following table

Type QTY
1 50
2 1000
3 600
5 800
9 1000
12 2500
50 1000
51 1000
55 20

What type of query could I run so that I would end up with the following?

Type QTY
1 Distribution 50
2 Distribution 50
3 Distribution 50
5 Distribution 50
12 Distribution 50
Balance 2020

So I need to make a table listing the first 5 records as its associated number concatenated with the word "distribution" and then any beyond record 5 I need to assign as "balance" and the associated quantity is the sum of the QTY field for all records above record 5.

Any help is greatly appreciated.

Celeste.

 

[lespaul]

Do you mean the first five sorted by Type?

if so, what happened to Type #9? Why did you go from 5 to 12?

Why do all 5 of the records have a QTY of 50? Is that because the "first" record (sorted by Type) has a QTY of 50?

Leslie

 

[celestedeeva]

sorry I was caught up with cut and paste - you are correct the final table should read

Type QTY
1 Distribution 50
2 Distribution 1000
3 Distribution 600
5 Distribution 800
9 Distribution 1000
Balance 4520

Yes the able would be sorted by type ascending and I would need the first 5.

Thanks so much for the response.

C.

 

[PHV]

Something like this (SQL code) ?
SELECT TOP 5 Type & ' Distribution' As myType, QTY
FROM yourTable
ORDER BY Type
UNION ALL
SELECT 'Balance', Sum(QTY)
FROM yourTable
WHERE Type NOT In (SELECT TOP 5 Type FROM yourTable ORDER BY Type)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886