Separating records all in one table to separate fields

[Pickymiss]

I have all personnel records in one table coded by job function. I need to
break the managers from the list of people and make the list of people new
fields in the same table... or something like that! Some managers have one
report, others have 30+.

So I would want the table to be
Manager, manager's info, direct report (DR) 1, DR 1's info, DR2, DR2's info,
etc.

I need to use it to pull data for an automated e-mail message in a mail
merge. I tried to build a query, but failed miserably. Unless there is a
way to pull from a sub set of records in a second table for a mail merge, I
am stuck. I could build the two tables with select queries.

If it were just an on-screen form, I could do it, but I actually need to
pull the fields from one table and send all the information on one department
to the manager (including all the direct reports).

Any suggestions?

 

[John W. Vinson]

I have all personnel records in one table coded by job function. I need to
break the managers from the list of people and make the list of people new
fields in the same table... or something like that! Some managers have one
report, others have 30+.

So I would want the table to be
Manager, manager's info, direct report (DR) 1, DR 1's info, DR2, DR2's info,
etc.

No. You certainly do NOT want to create such a dreadfully non-normalized
table.

I need to use it to pull data for an automated e-mail message in a mail
merge. I tried to build a query, but failed miserably. Unless there is a
way to pull from a sub set of records in a second table for a mail merge, I
am stuck. I could build the two tables with select queries.

There is of course. Most or all mail merges will be based on queries.

If it were just an on-screen form, I could do it, but I actually need to
pull the fields from one table and send all the information on one department
to the manager (including all the direct reports).

If you want to construct a string containing comma-separated email addresses
for the To: or Cc: lines in an email, see
http://www.mvps.org/access/modules/mdl0004.htm