One to many help

[Kentman]

I've searched the forum and cannot seem to find the answer to this but I'm sure I've seen it before somewhere!

I have a column of names along with a crime number. Some names are associated with more than one crime but the victim will be different so a name might appear a number of times in the name column but the corresponding other name for a crime number will be different - it's a bit difficult to put into words so an example might look like this:

CrimeNumber Name
1 JimJones190600101
1 Jack
2 JimJones190600101
2 John
3 HarryArnold19760304
3 Bill
4 HarryArnold19760304
4 Gordon

I would like to be able to get:

Col1 Col2 Col3
JimJones190600101 Jack John
HarryArnold19760304 Bill Gordon

The names are unique as they are a concatenation of the forename, sirname & DOB so if there are a number of Jims each could be picked out as a different person (I haven't written every name in full but they are this format).

I can't put real data as it is sensitive (the data above is ficticious and does not identify anyone with similar details).

Any help would be appreciated.

 

[Sektor]

IMHO, it's many-to-many.

 

[Kentman]

Yes. OK. If you want to be pedantic, you're correct but the result would be one to many is what I really meant!

 

[Sektor]

As it's many-to-many, then the structure of your table won't let make SQL. You need intermediate table.

 

[RoryA]

Is your data sorted by crime number as in your example, and is the key name always first?

 

[Richard Schollar]

Hi Kent

How much data in total is there? If you want to normalise the data then the output you want is actually:

JimJones190600101 Jack
JimJones190600101 John
HarryArnold19760304 Bill
HarryArnold19760304 Gordon

But that may not be your purpose.

 

[Kentman]

Sektor, this would be easily done in a database but I've only a spreadsheet where the data will be appended on a month by month basis. - It can be done as I've seen it on this forum but cannot, for the life of me, find it again!

Rory: Data is sorted by crime number and is always the first column.

Richard: It runs at about 6000 records per year. The output you show would be acceptable if the row format can't be achieved.

Thanks guys for your input.