Grouping or Concatenation?

[oXmike]

Hi Folks,
I'm rebuilding slowly a inherited 2007 DB that has some very strange features. One area is the 'staff' table. The Pk of this table has been built using the initials of the individual members of staff.

I must add, this is not an issue with the Pk, I will be adding a new Auto-Number one too the table before too long. The problem is with the two columns 'Staff Initials' and 'staff Name'. And the use they have been put to.

ok: I'm currently rebuilding this data in Excel:

Staff Initials.......Staff Name
AL......................Dr Arthur London
PM.....................Dr Peter Manchester
JNY....................Mr John New York

This table had no Title, forename or surname fields, so I have extracted that data no problem. And I can concatenate the names back together in a query no probs too.

The problems stem from when I come across a record in the table with the initials:

Staff Initials..........Staff name
AL/PM/JNY..............Dr Arthur London/Dr Peter Manchester/Mr John New York

(Yes inc... the forward slashes)

Apparently (I have been told, by the person who built the original DB), this has been done for a small (for small, read 107 records from 619), group of people who always teach together on certain subjects year after year.

Now, because of the number of clinics, lectures, seminars etc...that a person can do throughout the year, multiple staff participating in a session, needs to be grouped or concatenated into one line on reports. So I can see why they have done this in the first place.

But not really liking these multiple values in one field, I really want to get rid of them, but I'm at a loss as to how to get 3 separate records such as those above in blue to appear the same way as the single record above in green.

Anyone, any ideas?

Thanks in advance

Mike.

 

[pbaldy]

I'm a little confused. Is the question how to break up the merged data, or how to get normalized data to appear merged? The first would involve the Split() function, the second by a function such as this:

http://www.mvps.org/access/modules/mdl0004.htm

 

[gemma-the-husky]

first - how can 3 teachers teach the same subject. what do you do when one of them leaves/retires or you get a new teacher.

you need a subject table, a teacher table, and a teaches table to act as a junction table between the two.

you then need a function in your report to rebuild the concatenated list - but note that this is non-normalised, and while not ever-so tricky, needs a bit of thought.

 

[oXmike]

I'm a little confused. Is the question how to break up the merged data, or how to get normalized data to appear merged? The first would involve the Split() function, the second by a function such as this:

http://www.mvps.org/access/modules/mdl0004.htm

Hi Paul,
I'm excellent at confusing people lol...

The question was really about getting normalised data to appear merged. Thanks for the link, I have utilised it and it works thanks.

first - how can 3 teachers teach the same subject. what do you do when one of them leaves/retires or you get a new teacher.

you need a subject table, a teacher table, and a teaches table to act as a junction table between the two.

you then need a function in your report to rebuild the concatenated list - but note that this is non-normalised, and while not ever-so tricky, needs a bit of thought.

Gemma,
They run medical clinics also where they may have 20+ students. Apparently there are rules in regards to staff/student ratios. So quite often they have more than one member of staff in attendance. As for when someone leaves, (Holds head in hands) they just add another row with the new 'grouped' staffs initials.....

Just to add to the fun, there are 2 DBs. A staff table in each. One uses staff initials as a PK, the other an auto-number. Staff initials differ between the two tables for the same member of staff. There is no department table. Though the school has 7 departments, there appear to be 106 variations on the 7 etc. etc.....

Fortunately (or not, as the case may be) I have the long term task of sorting out this mess. Unfortunately they like their reports, for the time being, in the format they are used to. Therefore any structural changes I make, I need (again for the time being), to be able to make it appear to the end user that nothing has changed. Hence the concatenation of normalised data.

Thanks again both of you

Mike.

 

[pbaldy]

Glad it worked for you Mike.