assigning numerical value to subset data

[tractorvix]

Hi All,

first time poster, so please be gentle!

I have a huge data set containing parent and child items such as:

parent Child
1 a
1 b
1 c
2 ab
2 c
3 a
3 b
3 ab
3 c
3 e


etc

What I would like to do is to assign a number to each of the child items for each parent item so:
parent child child number
1 a 1
1 b 2
1 c 3
2 ab 1
2 c 2
3 a 1
3 b 2
3 ab 3
3 c 4
3 e 5

This will enable me to perform a cross tab query that will identify the number of child items and the actual items so that I can go on to perform more queries.

There will be duplicates in the child item field (as I've tried to show above)

I'm happy to put together a make table query that could do this, but I need the information to be built from a query as the data is dynamic.

Must just add, am relatively new to all this, so please talk loud and slow!

TractorVix

 

[PHV]

Do a keyword search in this forum for rank.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244