grouping and autonumber

[zturaiki]

Need help in this ..

I have One table , contain these Data :

Section	Sub-Section	Drug Class
Anaesthetics, Muscle Relaxants and Premeds	Premedication	Benzodiazepines
Anaesthetics, Muscle Relaxants and Premeds	Miscellaneous	Opioid analgesics
Central Nervous System	Epilepsy	Barbiturates
Central Nervous System	Epilepsy	Barbiturates
Central Nervous System	Epilepsy	Barbiturates
Anaesthetics, Muscle Relaxants and Premeds	Premedication	Benzodiazepines
Anaesthetics, Muscle Relaxants and Premeds	Miscellaneous	Opioid analgesics
Central Nervous System	Anxiety	Benzodiazepines
Anaesthetics, Muscle Relaxants and Premeds	Miscellaneous	Opioid analgesics

we have Section and under the section is sub section , and under sub sectio is Drug Class

i want to change the view to :

section code	Section	sub section code	Sub-Section	drug class	Drug Class	Item code
1	Anaesthetics, Muscle Relaxants and Premeds	1	Premedication	1	Benzodiazepines	1.1.1
1	Anaesthetics, Muscle Relaxants and Premeds	2	Miscellaneous	1	Opioid analgesics	1.2.1
2	Central Nervous System	1	Epilepsy	1	Barbiturates	2.1.1
2	Central Nervous System	1	Epilepsy	1	Barbiturates	2.1.1
2	Central Nervous System	1	Epilepsy	1	Barbiturates	2.1.1
1	Anaesthetics, Muscle Relaxants and Premeds	1	Premedication	2	Benzodiazepines	1.1.2
1	Anaesthetics, Muscle Relaxants and Premeds	2	Miscellaneous	2	Opioid analgesics	1.2.2
2	Central Nervous System	2	Anxiety	1	Benzodiazepines	2.2.1
1	Anaesthetics, Muscle Relaxants and Premeds	2	Miscellaneous	3	Opioid analgesics	1.2.3

[hic]

Load *,

          [section code] &'.'& [sub section code]  &'.'& [Drug Class code] as Item code

          ;

Load

          Section,

          Autonumber(Section, 'Section') as [section code],

          [Sub-Section],

          Autonumber([Sub-Section],'Sub-Section') as [sub section code],

          [Drug Class],

          Autonumber([Drug Class],'Drug Class') as [Drug Class code]

          From ...

HIC

[swuehl]

Zain,

it seems that the same combination of values is assigned a different code, e.g.

1	Anaesthetics, Muscle Relaxants and Premeds	1	Premedication	1	Benzodiazepines	1.1.1
1	Anaesthetics, Muscle Relaxants and Premeds	2	Miscellaneous	1	Opioid analgesics	1.2.1
1	Anaesthetics, Muscle Relaxants and Premeds	1	Premedication	2	Benzodiazepines	1.1.2
1	Anaesthetics, Muscle Relaxants and Premeds	2	Miscellaneous	2	Opioid analgesics	1.2.2

I believe the suggested autonumber will not consider these.

While other combinations are assigned same code (Central Nervous System | Epilepsy | Barbiturates).

What's the logic behind, when do you assign same and when do you assign different codes?

[zturaiki]

  I work with your solution ,, but it not give me the same result as I want ,,,

[zturaiki]

  I will explain it to you ,,,

In the section we have ,,, Anaesthetics, Muscle Relaxants and Premeds ,,,, I want to auto number it

Under this section is two sub sections ,,,, I want to auto number start from 1

1     Premedication     

2 Miscellaneous

Under each sub sections is drug class ,,, I want to auto number it

Sub section ,,,Premedication

Under it is drug class ,,,

1     Benzodiazepines     

2.      Opioid analgesics     

other sub section is ,,, Miscellaneous

Under it ,, the following drag class

1 Benzodiazepines     

2  Opioid analgesics     

The goal is to auto number and start the auto number from 1 for each group and sub group

[hic · Accepted solution]

If you want the autonumbering to restart from 1 within each group, then try

Load *,

          [section code] &'.'& [sub section code]  &'.'& [Drug Class code] as [Item code]

          ;

Load

          Section,

          Autonumber(Section, 'Section') as [section code],

          [Sub-Section],

          Autonumber([Sub-Section], 'Sub-Section' & Section) as [sub section code],

          [Drug Class],

          Autonumber([Drug Class],'Drug Class' & [Sub-Section]) as [Drug Class code]

          From ...

HIC

[zturaiki]

Thanks ,,, it works fine