Combining multiple coloums into one.

[KeithB14]

Hello all,

I have an issue I can't quite get my head around. I have a table with four coloums that list Emplyees by Number. I need to pull thase number and combine them into one colomn through a querry (I think). I don't need to join the coloums in the same record. Example...

Now... leader Pilot Admin Facilitator
11800 11801 11802 11803

Needed... Participants
11800
11801
11802
11803

Thanks for the help,

Keith

 

[GaryPanic]

Why ???

you could do this - but if its for a report, you don't need to -if its for a table
you could do it but again why .....

the way I have read this is you want a table field with a list in it
it would involve some coding and using the coding for carrage return chr13 ?? (from memory and without access around to help)

G

 

[KeithB14]

What I ultimately need is a calculation on participation. In one table I have emplyees listed by number, name, salary type, ect... In the other table I have the catagories listed above. I need to have an easily accessble way users can track the particpation percentage based on salary compensation.

Thanks for your resoponse,

Keith

 

[neileg]

Unfortunately you have a badly designed table. Instead of having four columns, you should have four records in a separate table. This is part of the concept called normalisation which you should read up on.

If a redesign is not practicable, or indeed to reorganise your data to support a redesign, you can do this:
Create 4 queries that extract the values for each column in turn, ie one for leader, one for Pilot, one for Admin, one for Facilitator.
Then create a union query that joins these queries vertically (normal queries join horizontally).

 

[GaryPanic]

go with Neil on this - as this is a slight dodge design --

 

[KeithB14]

Redesign is an option and I am familiar with normalization and I thought that’s what I did. In my Main_table, My fields are Project_Title, Project_Cost, Leader_Number, Pilot_Number, Admin_Number, Facilitator_Number. In the Employee_Table I have Employee Number, Name, Salary Status and so on. I think I need to break down my main table into 4 tables, Facilitator_Table....and so on. Does this sound like I am on the right track?

Thanks for all the support.

Keith

 

[neileg]

No, not the right track, really.

Redesign is an option and I am familiar with normalization and I thought that’s what I did. In my Main_table, My fields are Project_Title, Project_Cost, Leader_Number, Pilot_Number, Admin_Number, Facilitator_Number. In the Employee_Table I have Employee Number, Name, Salary Status and so on. I think I need to break down my main table into 4 tables, Facilitator_Table....and so on. Does this sound like I am on the right track?

Thanks for all the support.

Keith

You've got the tables the wrong way round. You need a projectID in your main table but not Leader_Number, Pilot_Number, Admin_Number, Facilitator_Number. Then you need an employee table. If each employee can only have one role and only one project, then you need to hold the projectID against the employee and have another field that that identifies the role.

I suspect, however, that each employee can have multiple projects and/or multiple roles. In this case you have a many to many relationship between projects and employees. So you need a junction table. This should hold the projectID, the employeeID and the roleID.