I want to group duplicates (text) across several columns

[shanice]

Hello Everyone!

I have an excel spreadsheet that I have imported in access. The spreadsheet has a column that lists project title and then 6 staff titles (project manager, admin asst, etc). I'm trying to create a query (or maybe it should be a report) that shows the projects and titles that each employee has held.

For example:

Stan Smith
---project manger for project A
---senior analyst for project D

Tracy Sue
---admin asst for project C
---asst analyst for project B

Please help...

 

[plog]

Is the example you showed the output of what you want or the input? I'm guessing output.

Could you list the fields of your table and some sample data? If you could then show how that sample data should look in the report that would be helpful to.

 

[shanice]

Yes that was the desired output.

Right now my I have 7 columns but here's 3 just for example:

Column 1: Project Name (unique)
Row 1: Project Name 1
Row 2: Project Name 2
Row 3: Project Name 3

Project Manger
Row 1: Sue Davis
Row 2: Bill Smith
Row 3:John Johnson

Senior Analyst
Row 1: Bill Smith; Bob Rice
Row 2: John Johnson
Row 3: Richard Lewis; Sue Davis

I want to find a way to group these by name. So based on the data above I want it to show:

Staff Name: Sue Davis --- Project Title: Project Title 1 --- Title: Project Manger
Staff Name: Sue Davis --- Project Title: Project Title 3 --- Title: Senior Analyst

Is there a way to simply have a table with a plus sign next to each name that shows all project titles and titles that they've worked on when you click on the plus sign?

Also, can I find name duplicates when there is more than one name in a cell (i.e. Bill Smith; Bob Rice)?

THANKS!!!

 

[shanice]

Is there a way to have each name show the number of times that they are listed in parathesis:

+Bill Smith (3)
+John Johnson (8)
+Richard Lewis (2)

 

[plog]

Your table needs to be restructured into at least 3 tables: Projects, Staff, Roles.

Projects will include ProjectId (autonumber) and ProjectName (text)
Staff will include StaffId (autonumber), StaffFname (text) and StaffLname(text)
Roles will essentially link Projects and Staff tables and have 3 fields: RoleId (autonumber), ProjectId (number--link to Projects table), StaffId (number--link to Staff table) and Role (text).

This is a sample of how your data will fit into this new structure for Project 1:

Projects:
ProjectId, ProjectName
1, "Project 1 Name"

Staff:
StaffId, StaffLname, StaffFname
1, "Bill", "Smith"
2, "Bob", "Rice"
3, "Sue", "Davis"

Roles:
RoleId, ProjectId, StaffId, Role
1, 1, 3, "Manager"
2, 1, 1, "Senior Analyst"
3, 1, 2, "Senior Analyst"

Once you have your table structured properly it will be easy to create a query to generate the data you initially posted about.

 

[shanice]

This is great!!! Thanks!